Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
780
questions
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 ...
