Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
677
questions
0
votes
0answers
7 views
Cannot find record in table after upgrading mysql from 5.6 to 5.7
We recently upgraded our database from mysql 5.6 to 5.7 and started seeing a new error that happens to occur intermittently. The error happens while trying to access results of a query or while ...
0
votes
0answers
16 views
INSERT INTO …ON DUPLICATE KEY UPDATE doesn't work [closed]
I try to using the ON DUPLICATE KEY UPDATE for changer the table's value, but not success, It return CLIENT->OKkkkk and TYPE_CLIENT->null.
thank for your help
INSERT INTO `table`(`CLIENT`)
...
0
votes
1answer
16 views
calling mysql procedure return empty result
when i call procedure it gives me empty result
call hussian('prstate','table_1','column1,column2,column3',1)
it run but gives empty result, please help me...
DELIMITER //
CREATE PROCEDURE ...
0
votes
1answer
11 views
MySql Replication Failure on primary secondary
We have experienced replication failure between primary - secondary databases.
I have tried various scenarios to get replication to work again, which included deleting secondary database and ...
-2
votes
0answers
15 views
I ran MySQLTuner and wondered if anyone would go over the results with me [closed]
The mysqltuner report:
>> MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help'...
0
votes
2answers
26 views
MySql replication Failure
I have a mysql (Ver 14.14 Distrib 5.7.29, for Linux (x86_64)) primary -> secondary replication setup. Initially the replication was working successfully until we had to restart the primary database. ...
0
votes
1answer
12 views
Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4
I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER ...
3
votes
1answer
36 views
Why mysql throws “invalid default value for field” only when two or more timestamp fields exist in the create statement?
In mysql 5.7.28 I create a table like this:
create table t1 (
id int not null,
d1 timestamp)
engine=innodb;
which works fine and creates d1 as non-null with default value current_timetamp,on update ...
0
votes
0answers
18 views
Alter Table operations blocks replication in MySQL
We are using Amazon Web Service's MySQL 5.7.26 with 4 read-only replicas. After some Alter Table operations we have found that the replication stops working. The symtoms are great replica lag that is ...
0
votes
1answer
36 views
Why doesn't MySQL use indexes for subqueries?
This query takes forever to run (30+m - infinity).
select date,
sc,
( select count(fingerprint_id)
from stats
where hit_date >= t.date
and hit_date ...
0
votes
1answer
62 views
MySQL - [Note] Got an error reading communication packets
I have deployed MySQL on OpenShift (kubernetes) with GlusterFS as persistent storage and i am getting following error after every 2 seconds in MySQL Container logs
2020-01-17T18:00:04.787586Z 1541 [...
0
votes
1answer
47 views
MySQL: I need to return rows according to certain conditions
I have a 3 tables: work, recording, release
1 work can have multiple recordings and 1 recording appears in only 1 release and 1 release can be in m recordings.
TABLE: work
+---------+-----------+
| ...
1
vote
2answers
37 views
Filtering down specific criteria for all rows and return only one row for each column with the same value
I have house_leases and house_lease_terms (see table schemas below). A house_lease can have multiple house_lease_terms however there can only be one "active" term at a time.
Table Definitions:
...
0
votes
1answer
40 views
Best strategy to correctly index complex join query
This is the query running on MySQL 5.7 with INNODB:
SELECT SQL_CALC_FOUND_ROWS
calendar.id_calendar AS calendar_id,
calendar.event_datetime AS calendar_event_datetime
FROM
users
INNER ...
-2
votes
1answer
19 views
Can't manage to work with comparing days
In a table with persons and their birthdays, the ones whose birthday is in less than 7 days need to be displayed.
I found some answers on the Internet but they all are at least 5 years ago with ...
0
votes
1answer
21 views
MySQL DB Causing Spike-up on Windows Server CPU
This is a Magento application connected to MySQL Db. When I ran show engine innodb status\GI get a list of TRANSACTIONSand it looks like some of them are pending and others are in a DEADLOCKand it ...
0
votes
0answers
121 views
I can't start mysqld.service -> status : Data Dictionary upgrade from MySQL 5.7 in progress in centos 7
I've installed mysql 8 on centos 7
and when i want to start the mysqld service , it says :
Job for mysqld.service failed because the control process exited with error code. See "systemctl status ...
0
votes
1answer
35 views
Unable to Join phpMyAdmin Using MySQL Defined User
I can login to mysql via the command line just fine, but when I try to log on to phpMyAdmin with the same password that worked in the cli, it won't allow me to connect and gives me: ...
0
votes
1answer
24 views
Everything is ok and x is not disappearing
Since some time ago, I've started with MySQL so I don't know Workbench very well.
That little red x next to the number of the line that indicates an error does not disappear, even though everything ...
2
votes
1answer
34 views
Primary Key At the End of Foreign Key index in MySQL is in order for sure?
I am aware that any Index in MySQL Innodb contains primary key value in the end of it. If there was a table as follows.
create table BaseTbl (
base_id int not null primary key auto_increment,
...
0
votes
0answers
16 views
Very strange Mysql query optimization
This is the initial query:
SELECT
DISTINCT clienti.id_cliente AS cliente_id , clienti.cognome AS cognome,clienti.nome AS nome
FROM
clienti
WHERE
clienti.id_agenzia = 5782 AND ...
0
votes
0answers
19 views
create backup on mysql master-slave
I have MySQL version 5.7 with a master-slave configuration. but I'm confused to make a backup cron. Is there a way to do a backup while remaining safe to master-slave? I heard that using mysqldump is ...
2
votes
3answers
164 views
Something in MySQL is causing progressively high load which consumes almost all memory
I have this server that has only one site with one database on it. And yet, I cannot tweak it to make the load go back down to normal.
If I restart mysql service, memory and load go down to normal, ...
0
votes
1answer
28 views
Gereralise the query for all user_id
I have a mysql query
select sum(duration),app_name,app_id,user_id
from timesheet
where user_id=164
group by app_id
order by sum(duration) desc
limit 5;
The result is
sum(duration), app_name, ...
0
votes
2answers
21 views
If I need to restrict access to certain databases?
So I've just created a MySQL server. I also created some users only having specific access to certain databases, but do I need to restrict access to default databases to the users I've created?
If so, ...
0
votes
1answer
12 views
Make a list of stations by checking next_station column value
How to list all station between selected two stations based on next_station_id
For instance, if the user selects origin A station and destination to J than I want to list all stations in ORDER that ...
0
votes
0answers
22 views
Is there a way to create tablespace in MySQL RDS
I'm using AWS RDS MySQL instance and using it. Now I want to create table space and assign table space to tables. Is it possible in MySQL RDS
1
vote
1answer
12 views
DB locks being created on temp tables. Can percona ignore temp tables for replication
I have a stored procedure that I am using that creates a temp table. Recently I've noticed the logs are filling up with locks. I think the temp table is trying to be replicated but I am unsure.
*** ...
0
votes
1answer
31 views
Find employees latest activity is slow when adding ORDER BY
I am working on a legacy system in Laravel and I am trying to pull the latest action of some specific types of actions an employee has done.
Performance is good when I don't add ORDER BY. When adding ...
0
votes
0answers
11 views
Upgrading MySQL takes forever
I am upgrading a MySQL database from 5.6 to 5.7; it contains a few tables that are obscenely big, >500m rows in one case. I started the mysql_upgrade yesterday, and it has been chewing its way through ...
2
votes
0answers
66 views
MySQL performance degradation with heavy inserts
I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
0
votes
2answers
120 views
High RAM memory usage
I'm currently testing a new MySQL server, but I'm new in this so I'm not sure about my parameters, because the RAM is always above 90% of usage.
The server will be setup for a CRM environment of 100 ...
0
votes
0answers
15 views
Re-Generate cumalative totals for double entry accounting records stored in a table
I have a customer_transaction table as below.
CREATE TABLE `customer_transactions` (
`id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`transaction_type` varchar(25) NOT NULL,
`...
-1
votes
2answers
39 views
Compare grouped totals using two tables
I have two tables:
charges
id
booking_no
room_id
guest_id
product_id
unit_price
quantity
net_total
tax_total
gross_total
payments
id
booking_no
room_id
guest_id
payment_method
payment_amount
I ...
-1
votes
1answer
31 views
Alternative for creating Database and inside Database having schemes like Oracle in MySQL
In oracle we have database inside database we have schemes and inside schemas we have tables.
For Example consider the following scenario
I have 2 databases like Database1 and Database2.
In ...
0
votes
2answers
76 views
MySQL DELETE statement doesn't use index although the same SELECT query does
I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following:
id,
cookie_id VARCHAR(45),
country ...
0
votes
1answer
26 views
Procedure MySQL cursor taking a long time to execute
I have a MySQL procedure, with a cursor in it. The sample cursor query is given below
DECLARE cs CURSOR FOR
(SELECT a.mag_id FROM A a WHERE
a.creation_date BETWEEN (...
1
vote
0answers
61 views
Slow MySql with left join and Group by
Below is the Mysql 5.7 Table query which has a left join with the group by. When I execute it takes more execution time.
line_items have around 35 Lakhs (3.5M) data and users have 1000 rows.
Below is ...
0
votes
1answer
27 views
Optimizing the repeated use of the same if condition inside a VIEW?
CREATE OR REPLACE VIEW VIEW_NAME AS (
SELECT
table0.fieldBlah "FieldBlah",
--more fields
IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField) "uniqueFieldName")
...
-1
votes
1answer
58 views
Can't get this query to work properly
I've been trying to get the following query working for days but I can't seem to solve my issue. I'm using the MySQL version 5.7.9
I have the following database:
CREATE TABLE user (
id INT NOT ...
1
vote
2answers
48 views
how to get the marks in ‘Math’ of ‘English’ topper for each class in mysql for student result data
I need to find the marks in ‘Math’, of ‘English’ topper for each class in MySQL for student result data.
Output schema: class, roll_no, math_marks
(Here expected result image not exact answer)
...
0
votes
1answer
16 views
Incomplete MysqlDump Files - Mysqldump Inside For Loop
I am running a Windows 10 Machine with WAMP installed. I have also a Git Bash Installed. I was trying to backup all my databases in each separate file using the code below.
for DB in $(mysql -uroot ...
1
vote
1answer
31 views
migrate users from mariadb10.1 to mysql5.7
My company is moving our databases from self-hosted mariadb 10.1 to Google CloudSQL (which is essentially MySQL 5.7).
Dumping / restoring our application schemas looks straightforward. However I'm ...
-1
votes
1answer
54 views
What are the right ways to analyse and tune up innodb_thread_concurrency in mysql 5.7?
I'm in a plan to increase thread concurrency in my production MySQL server 5.7 but not sure, before what are the params/configurations that need to be measured and configured to increase the thread ...
-1
votes
1answer
26 views
MYSQL Trigger: Update before insert
I want to increment the order of every video by one every time a video gets inserted so that the new video takes its place
UPDATE services SET orders = orders + 1
This doesn't work, I get this error,...
0
votes
0answers
34 views
Trimming copy/delete mysql table rows using Trigger
I am using Freeradius AAA server on Ubuntu 18 server which uses mySQL ver 5.7.27 as DB to store entries. INNODB is being used. It have a table called radacct which stores users accounting /session ...
0
votes
1answer
34 views
Intentionally slow down importing of (Percona) MySQL 5.7 dump
I need to move a very large MySQL table (250M rows, 120 columns. 120GB of data, 310GB of index) from one database instance to another (on AWS). For reasons beyond the scope of this question, this is ...
0
votes
0answers
14 views
MySQL 5.7 on W2K12 essentials - starts fine and then fails after restart
I have a W2K12 Essentials server that I'm trying to get MySQL 5.7 running on for in house development. It installs just fine - I change install and data directories to two separate directories on the ...
0
votes
0answers
17 views
MySQL 5.7.26 data exists in .ibd file but 0 records exist in table
We found an old mysql database that we're trying to restore. What does it mean when there's clearly data in the .ibd file when opening in notepad++ (the .ibd file is 288 kb) but the table contains no ...
0
votes
1answer
12 views
Update value by column/field in INSERT SQL
I want to update the column value in multi-rows insert SQL.
For example:
INSERT INTO `product_price` (`product_id`, `store_id`,`price`)
VALUES (20001, 1, `price`+10),
(4442, 1, `price`-5),
(2442, 1, ...
