Unanswered Questions
345 questions with no upvoted or accepted answers
15
votes
1
answer
2k
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:
------...
CommunityBot
- 1
4
votes
0
answers
4k
views
Are `--events` and `--routines` really needed to get a `mysqldump --all-databases` of an entire mysql instance?
Several fine answers on here have already exquisitely argued that
mysqldump --hex-blob --events --routines --triggers [...] --all-databases
should be all - modulo storage-engine considerations - ...
- 141
4
votes
0
answers
585
views
Does Google CloudSQL Export function lock tables?
In order to create more 'data recovery' points than the standard automatic backup policy allows, I'm going to run a cron which will export sql dumps out of my CloudSQL instances at intervals ...
- 1,834
3
votes
0
answers
368
views
Slower delete performance in MySQL8 compared to MySQL5.7
I'm looking for some insights into why deletes from a fairly simple table seem to be much slower in MySQL8 than in MySQL5.7
Apologies in advance if this is a little fluffy at this stage and I've ...
3
votes
0
answers
91
views
Is there any way to optimize this query (generated by Drupal)?
I have an older system built on drupal, and there is a particular (generated) query that takes a long time - never completing in some cases.
The query:
SELECT
node.nid,
node.created,
node....
- 1,232
3
votes
0
answers
2k
views
How to estimate table row size in MySQL InnoDB?
My particular problem that I'd like to solve is a table with very wide column (sometimes > 1000 columns) which requires me to split the table. In order to do so smartly, I'd like to split right ...
- 131
3
votes
3
answers
495
views
Execution plan changed for existing query - high CPU usage
So few weeks ago we had an alert on our production MySQL database due to a CPU usage spike. We identified the issue as a query that had been there for several months that suddenly was misbehaving. The ...
CommunityBot
- 1
3
votes
0
answers
2k
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 ...
- 83
3
votes
0
answers
18k
views
/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=1/FAILURE)
I had to install MySQL on Ubuntu 18.04.4 LTS (GNU/Linux 4.15.0-1065-aws x86_64)
But it produces errors:
ExecStart=/usr/sbin/mysqld --daemonize
--pid-file=/run/mysqld/mysqld.pid (code=exited, status=1/...
CommunityBot
- 1
3
votes
0
answers
937
views
RESET MASTER; does not reset GTID_EXECUTED
I'm trying to get replication up and running between two kubernetes pods, and am getting the dreaded @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. error.
root@central-vm:~...
- 694
3
votes
0
answers
154
views
What is the order of execution of alter operations in an alter query in MySQL?
Consider the following queries
CREATE TABLE MyTable (f2 INT);
ALTER TABLE MyTable ADD COLUMN f1 INT, DROP COLUMN f1;
In the above query drop column seems to happen before add column and query fails. ...
- 4,128
3
votes
0
answers
366
views
Why do two transactions making a read lock result in a DEADLOCK exception?
Let's assume a scenario that flows as follows:
Transaction #1 makes this query:
SELECT v.id, v.name
FROM versions_history as vh
INNER JOIN versions as v
ON vh.version_id = v.id
WHERE vh.project_id = ?...
- 8,530
3
votes
0
answers
188
views
"FOR UPDATE" in an "EXISTS SELECT" statement
Is there any potential problem in using (in a transaction)
SELECT EXISTS( SELECT * FROM mytable WHERE id=36 FOR UPDATE )
i.e. having SELECT ... FOR UPDATE within an EXISTS?
MySQL does certainly some ...
- 555
3
votes
1
answer
3k
views
wait_timeout kills session in SLEEP MODE or all connection ?`
Do wait_timeout session clear all running session or only sessions which are in SLEEP mode for specified seconds ?
CommunityBot
- 1
3
votes
1
answer
3k
views
InnoDB page_cleaner error message: The settings might not be optimal
My server is in idle state but I often get this error:
Note: InnoDB: page_cleaner: 1000ms intended loop took 35165ms.
The settings might not be optimal.
flushed=0 and evicted=0, during the time
How ...
- 31.8k