Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.1(EOL), 11.2, 11.4, 10.6.18, 11.0(EOL), 11.5(EOL)
Description
The test case below is non-deterministic, run with --repeat=N. It usually fails for me in a few attempts.
--source include/have_innodb.inc
|
|
|
CREATE TABLE A (f INT, KEY (f)) ENGINE=InnoDB; |
CREATE TABLE B (f INT, FOREIGN KEY fk1(f) REFERENCES A(f)) ENGINE=InnoDB; |
|
|
ALTER TABLE A ADD FOREIGN KEY fk2(f) REFERENCES B(f); |
ALTER TABLE B ADD FOREIGN KEY fk(f) REFERENCES B(f); |
|
|
SET FOREIGN_KEY_CHECKS = OFF, LOCK_WAIT_TIMEOUT= 2, INNODB_LOCK_WAIT_TIMEOUT= 1; |
|
|
--connect (con1,localhost,root,,)
|
SET FOREIGN_KEY_CHECKS = OFF, LOCK_WAIT_TIMEOUT= 2, INNODB_LOCK_WAIT_TIMEOUT= 1; |
|
|
--connection con1
|
--send
|
ALTER TABLE B ADD FOREIGN KEY fk3 (f) REFERENCES A (f); |
--connection default
|
--send
|
ALTER TABLE A ADD FOREIGN KEY fk (f) REFERENCES A (f); |
--connection con1
|
--reap
|
--connection default
|
--error ER_FK_FAIL_ADD_SYSTEM,ER_LOCK_WAIT_TIMEOUT
|
--reap
|
|
|
DROP TABLE A, B; |
Two last concurrent ALTERs sometimes mutually lock up. It happened in earlier releases, too. However, in 10.6.17 release the threads were getting stalled and the deadlock was temporary, resulting in LOCK_WAIT_TIMEOUT error after innodb_lock_wait_timeout was exhausted. In 10.6.18+ instead the threads keep looping at high CPU usage, switching back and forth from "Committing alter table to storage engine" and "Waiting for table metadata lock", they no longer obey the timeout and stay in this stuck state seemingly forever. Killing the queries doesn't help either, they just switch to "Killed" in "Committing alter table to storage engine" state, but keep looping.
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
| Id | User | Host | db | Command | Time | State | Info | Progress | |
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
| 9 | root | localhost | test | Query | 3 | Waiting for table metadata lock | ALTER TABLE A ADD FOREIGN KEY fk (f) REFERENCES A (f) | 0.000 | |
| 10 | root | localhost | test | Query | 3 | Committing alter table to storage engine | ALTER TABLE B ADD FOREIGN KEY fk3 (f) REFERENCES A (f) | 0.000 | |
| 11 | root | localhost:44368 | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
3 rows in set (0.001 sec) |
MariaDB [test]> show processlist;
|
+----+------+-----------------+------+---------+------+---------------------------------+--------------------------------------------------------+----------+ |
| Id | User | Host | db | Command | Time | State | Info | Progress | |
+----+------+-----------------+------+---------+------+---------------------------------+--------------------------------------------------------+----------+ |
| 9 | root | localhost | test | Query | 4 | Waiting for table metadata lock | ALTER TABLE A ADD FOREIGN KEY fk (f) REFERENCES A (f) | 0.000 | |
| 10 | root | localhost | test | Query | 4 | Waiting for table metadata lock | ALTER TABLE B ADD FOREIGN KEY fk3 (f) REFERENCES A (f) | 0.000 | |
| 11 | root | localhost:44368 | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+------+-----------------+------+---------+------+---------------------------------+--------------------------------------------------------+----------+ |
3 rows in set (0.000 sec) |
|
|
MariaDB [test]> show processlist;
|
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
| Id | User | Host | db | Command | Time | State | Info | Progress | |
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
| 9 | root | localhost | test | Query | 8 | Committing alter table to storage engine | ALTER TABLE A ADD FOREIGN KEY fk (f) REFERENCES A (f) | 0.000 | |
| 10 | root | localhost | test | Query | 8 | Waiting for table metadata lock | ALTER TABLE B ADD FOREIGN KEY fk3 (f) REFERENCES A (f) | 0.000 | |
| 11 | root | localhost:44368 | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
|
|
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> kill query 9;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> show processlist;
|
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
| Id | User | Host | db | Command | Time | State | Info | Progress | |
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
| 9 | root | localhost | test | Killed | 57 | Committing alter table to storage engine | ALTER TABLE A ADD FOREIGN KEY fk (f) REFERENCES A (f) | 0.000 | |
| 10 | root | localhost | test | Query | 57 | Committing alter table to storage engine | ALTER TABLE B ADD FOREIGN KEY fk3 (f) REFERENCES A (f) | 0.000 | |
| 11 | root | localhost:44368 | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
3 rows in set (0.000 sec) |
|
|
MariaDB [test]> kill query 10;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> show processlist;
|
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
| Id | User | Host | db | Command | Time | State | Info | Progress | |
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
| 9 | root | localhost | test | Killed | 86 | Committing alter table to storage engine | ALTER TABLE A ADD FOREIGN KEY fk (f) REFERENCES A (f) | 0.000 | |
| 10 | root | localhost | test | Killed | 86 | Committing alter table to storage engine | ALTER TABLE B ADD FOREIGN KEY fk3 (f) REFERENCES A (f) | 0.000 | |
| 11 | root | localhost:44368 | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+------+-----------------+------+---------+------+------------------------------------------+--------------------------------------------------------+----------+ |
3 rows in set (0.000 sec) |
The change was apparently introduced by this commit:
commit b2654ba82651630dba0dd2012f45b77299a43548
|
Author: Marko Mäkelä
|
Date: Thu Feb 1 15:48:46 2024 +0200
|
|
|
MDEV-32899 InnoDB is holding shared dict_sys.latch while waiting for FOREIGN KEY child table lock on DDL
|
Attachments
Issue Links
- is caused by
-
-
- Closed
-
- relates to
-
-
- Closed
-