Questions tagged [mysql-5.7]

MySQL version 5.7 - please also tag with mysql for search purposes.

Filter by
Sorted by
Tagged with
1
vote
1answer
15 views

Replicate_Do_DB and Replicate_Wild_Do_Table - Replication does not work

I have been tasked to set replication up on a slave host. The master database is a "data store" where tables are dropped, recreated, and reloaded on a daily basis. My initial setup of the ...
0
votes
0answers
11 views

Clear performance schema on read-only MySQL instance

I am using AWS Aurora MySQL with Writer and reader instances in replication. Both instance are used by two separate applications. I want to clear the performance schema on the read replica server but ...
0
votes
1answer
24 views

Strange max(date) return in reader vs writer aurora mysql

Something very strange happening here which I can't point out. Using AWS Aurora, select max(date) in the writer gives me one date, in the reader it returns strange date and warnings.. Column is ...
0
votes
0answers
37 views

how to create a MySQL View to select the smallest and largest Records?

i have a table contains items units details (st_items_units) like :- ------------------------------------------------------- id ------ stitems_ID ------unit------ stitemsu_UnitNum --- unit price -----...
1
vote
0answers
27 views

2020-07-29T07:44:29.817503Z 108716 [Note] Aborted connection 108716 to db:

We are getting continuously logs in mysql error logs 2020-07-29T07:46:56.534666Z 111536 [Note] Aborted connection 111536 to db: '' user: '' host: '' (Got an error reading communication packets) we ...
0
votes
0answers
40 views

Query performance become slower after upgrade to MySQL 8.0.20

I have found bug/issue after update the MySQL Server from 5.7 to 8.0.20. When I run a query with the user-define function in 5.7(same session), the duration for each query is constant and stable. But ...
0
votes
1answer
24 views

How to determine actual memory occupied by Query Cache in MySQL?

How can we check if the MySQL server is utilizing high memory/RAM ? How much memory is occupied in total by query cache ? How much memory is fragmented ? Basically what is occupying xGB off memory by ...
0
votes
1answer
38 views

Is the following index is good?

What is the proper indexing plan for the below query? Query 1 : select id from room_participants where roomId=1 and userId=1 and status=1 and isMuted=0 Query 2 : select group_concat(b....
1
vote
0answers
20 views

Apply function in UNIQUE constraint in MySQL 5.7

I would like to add a multi column unique constraint to my MySQL 5.7 database table like this: ALTER TABLE report_summary ADD UNIQUE ( tenant_id, app_id, date_format(FROM_UNIXTIME(...
0
votes
0answers
14 views

Is my connection using SSL or not?

I have two MySQL 5.7 databases ([db1] 5.7.27 and [db2] 5.7.29) (n.b. db1 is Linux, db2 is Windows) I have created a basic user on db1: CREATE USER 'ssltest'@'%' IDENTIFIED BY 'ssltest'; GRANT ALL ON *....
0
votes
0answers
9 views

Replication refuses to use SSL

I have two MySQL 5.7 Servers (5.7.29 and 5.7.31) I have created a set of SSL certificates and copied them to both the source and replica DB's so they both have the same certificates in place. On my ...
0
votes
0answers
22 views

MySQL TPS Speed down with process hang on status 'Update' or 'Updating'

I have mysql running with a high concurrency press test. MySQL can running with around 1000TPS for 15 minutes with 200-400 running thread. Then performance speed down, with process waiting for status '...
1
vote
2answers
55 views

15 seconds to insert a single row in MYSQL 5.7 (happens occasionally)

I'm seeing very sporadic slow queries in MYSQL, at least, that's where Datadog is suggesting the issues are. I have, for example, 5 queries out of 726K traces (both selects & updates) in the last ...
0
votes
0answers
40 views

How to get the list of foreign key together with the index name on that foreign key?

I am using this query to list all the foreign key, but I also need to have the index name on that foreign key. select a.* from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as a where a.CONSTRAINT_TYPE = '...
1
vote
0answers
19 views

Optimizing UNION

In MySQL 5.7 is it possible to optimize a query that coalesces values from three different tables? In pseudo-code something like: SELECT id FROM table_1 WHERE name = ? ELSE SELECT id FROM ...
0
votes
2answers
35 views

Filter and Aggregate a column not in the group by clause MySQL

I have the following table. I'm using mysql-5.7. Here is the dbfiddle select * from report; +----+---------------------+---------+------+------+------------+------+-------------------+ | id | ...
0
votes
2answers
25 views

WHERE statement returning out of bounds returns

I have this query that I'm trying to work with, and ultimately I'm trying to get it to return results as soon as it matches one of my status WHERE requirements....but that's not even my question. My ...
0
votes
0answers
9 views

Getting current number of connections in mysql

I want to know if there is any parameter to show the current number of mysql connections (or peak value in the past 10 minutes or something else). Right now, I only see max and max_used connections. ...
2
votes
1answer
64 views

MySQL 5.7 High Memory usage

I have a setup with MySQL 5.7 and the following configuration: [mysqld] innodb_buffer_pool_size=2GB innodb_buffer_pool_instances=2 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock bind-...
0
votes
1answer
34 views

Optimising MySQL count() with INNER JOIN (550k rows)

I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the ...
2
votes
0answers
34 views

Database Design for Pharmacy store with wholesale and retial

I have designed a DB ( Inventory Management ) for Pharmacy Store which will sell medicine both wholesale and retail. I store each units with its conversion for medicine in a separate fields for ...
1
vote
1answer
15 views

Azure Pipelines: MySQL Exiting and No Longer Available with --initialize or --initialize-insecure (No Clear Errors)

Background (Aka the "Why") I'm attempting to automate a build of a static website based on a Wordpress site utilizing Azure DevOps Pipelines. As part of that process I need to get MySQL ...
0
votes
0answers
55 views

Restart MySQL server while query is in Killed State

I need to reboot the server (not just MySQL instance) to perform some urgent server updates. Currently on the MySQL server when I run: select * from information_schema.processlist where info is not ...
0
votes
0answers
17 views

Selecting a nonaggregated column which is not functionally dependent on other columns

I'm using MySQL 5.7 and have run in to this dreaded error. Changing the sql_mode is not an option. SQL Fiddle: http://sqlfiddle.com/#!9/18f79b/1/0 Expected output is {"This": "row"...
0
votes
1answer
39 views

Do MySQL can do automatic EXPLAIN on every query?

We only have two Junior DBAs, no Senior DBAs, no one to guide us. We need to review each and every query for almost 30 large projects. Each query took between 5 minutes and two hours depending on the ...
0
votes
0answers
19 views

MySQL says “check that your my.cnf matches the ibdata files”. How?

After trying to copy some columns of big tables (500k records) into another one, my MySQL dockers (5.7) images stoped. Trying to start it again I get the following error: [ERROR] InnoDB: Trying to ...
2
votes
1answer
30 views

Indexing a date column in MySQL doesn't work if a date function is used

I have a table with the following schema table_name id int task_id int completed_date datetime (INDEX IX_TBL_NAME_COMPLETED_DATE) I run EXPLAIN on this query EXPLAIN SELECT * FROM ...
0
votes
2answers
30 views

Wrong index been peeked

I have an index that filters 99% of the table i.e. ix_magic_composite (for that query arguments). When I add another or filter it chooses the wrong index, i.e. fTS even if I create an index that ...
1
vote
2answers
45 views

How to optimize big result to sum in MySQL 5.7

I have a user consume history record table. Sometimes, the data is very big (> 1 million / day). Now I have to calculate the table to get report data. Here is the SQL: SELECT SUM(consume_num) ...
1
vote
0answers
38 views

Getting error while starting slave on MySQL slave./ Backups in MySQL 8.0.20

I want to set up below command for daily MySQL database backup.suppose We have more than 50 MySQL database in server. I am using below command to take backup of all databases on server. Note: I am ...
1
vote
2answers
41 views

After changing engine from myisam to innodb tables are larger in size, is this an expected behaviour?

I updated a WordPress database from myisam to innodb on a server using apache and mysql, and I noticed that some tables, such as wp-posts grew in size after running the ALTER TABLE wp_posts ENGINE=...
0
votes
1answer
29 views

MySQL read explain - using where

In Oracle there is an important note in the explain plain where it says filter a.col1=... this one letting you know col1 is first retrieved from the disk and only then filter is been done. This ...
0
votes
0answers
32 views

Specify Host for User in docker compose file

I've started messing with Docker and the MySQL 5.7 image, and have created a docker-compose.yaml file to quickly bring up a DB instance with the root password set, a new user & password created, ...
0
votes
0answers
16 views

updating n number of records mysql database

I want to update n number of records in table using a query like below, but it will return an error since in mysql, it is not allowed to use value after the limit key update producedItem pi inner ...
0
votes
1answer
31 views

MySQL apply LIMIT and OFFSET to a selected table, rather than to the result set

I have 3 tables as below. mysql> describe IDN_CONFIG_RESOURCE; +---------------+--------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default ...
1
vote
0answers
28 views

InnoDB trigger deadlocks itself?

I have a MySQL 5.7.22 database using the InnoDB engine. There's a table respondents that has 6 associated triggers - three of them make CRUD operations on a completed_respondents table, and the other ...
2
votes
0answers
120 views

MySQL Slave SQL Running State - “Waiting for dependent transaction to commit” confusion

I'm trying to debug a replication lag issue. We have a job that we run over the weekend, where we delete a large number of rows (potentially millions) from a table, currently in batches of 100. While ...
1
vote
2answers
34 views

Almost unique constraint in MySQL

Is is possible to have a key in MySQL 5.7, which is unique for all values except one. Let's say -1 is a not assigned wildcard. Is it possible to have a table with the following structure user | car | ...
0
votes
0answers
17 views

MySQL: Use a LIMIT for a JOIN table added

View The view of the app contains a page which shows a bunch of carousels (each carousel = a tag), each of each should contain 5 products: +-------------------------------------+ |Page ...
0
votes
1answer
16 views

Reducing the size of mysql files

I have been working on MySQL database whose size is growing quite huge and I want to save on space on my cloud. I have tables whose engine is Innodb which holds the largest amount of data. I have ...
2
votes
1answer
31 views

construct a sql , assign it to a variable and execute inside shell script

I am trying to execute a mysql query inside my shell script, which is not executing . Here is what i am trying to do query="\"select count(*) from information_schema.tables where TABLE_SCHEMA not in (...
1
vote
4answers
304 views

Slow fetch time for MySQL query with large result set

I'm aware I should expect slower fetch times for large result sets generally, but I don't understand why fetching should be this slow. The workload fetches a large number of rows (1-10M) from a large ...
0
votes
0answers
17 views

Select Type UNION RESULT doesn't appear in the output of Explain statement

EXPLAIN SELECT actor_id, (SELECT 1 FROM sakila.film_actor WHERE film_actor.actor_id = der_1.actor_id LIMIT 1) FROM ( SELECT actor_id FROM sakila.actor LIMIT 5 ) AS der_1 UNION ALL SELECT film_id, (...
0
votes
0answers
21 views

MySQL - How to optimize multiple OR conditions in a query on a large table

I have a table with the following structure: Configuration ( id: binary(16) Primary key, htId: binary(16), raId: binary(16), date, amount ); I have already created these indexes: ...
8
votes
1answer
72 views

MySQL deadlock: upsert query acquiring gap lock twice?

I am trying to debug a deadlock scenario which has been puzzling me for quite some time now. I am wondering if someone can shed any light into how this can happen. Here is the deadlock output: ------...
0
votes
1answer
22 views

How to access the error log [closed]

Is there any other ways to access the error log in MySQL on ubuntu other than using SSH? I really need to access the error log, but I do not have any privileged to connect via SSH. Please help me. ...
1
vote
1answer
16 views

Is there a difference between running the source command from the mysql command line client and '<' from outside it? if so, when should I use each?

I can run the following commands: C:\myfolder>mysql -uuser -ppassword Then from within the client do: mysql>source C:\path\to\file.sql Or I can do: C:\myfolder>mysql -uuser -...
0
votes
0answers
22 views

MySQL innoDB cluster auto rejoin failed

3 node cluster, single primary. heavy read/write was happening on the master node. Restart the Master node. Then node 3 became the master. After the restart, the old master was in recovery state "...
0
votes
0answers
39 views

Group by with Order by DESC not working using PHP MySQL codes

I have three tables, table one name (tbl_brands) with these columns: and second table name (tbl_loader_attachment) with these columns: and the third table name is (tbl_images) with these columns: I ...
0
votes
0answers
16 views

mysql - need explain for group by behavior

Can someone to explain below group by result I have a simple table "TableA" like this ID --- Name (1 --- A_1) (2 --- A_2) (3 --- B_3) +when I run below group by select Name from TableA group by ...

1
2 3 4 5
16