Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.2
Description
Note: I'm not sure if it's intentional, cannot find anything about it in MySQL changelog, bug base or manual. If it's intentional, please feel free to close or move to documentation if necessary.
In InnoDB 5.7, if a row which needs to be deleted through DELETE CASCADE is locked, the DELETE hangs seemingly forever – neither innodb_lock_wait_timeout nor lock_wait_timeout have any effect.
In InnoDB 5.6 (MariaDB 10.1, MySQL 5.6) it works as expected.
|
Test case |
--source include/have_innodb.inc
|
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; |
|
|
CREATE TABLE t2 ( |
id INT NOT NULL PRIMARY KEY, |
ref_id INT NOT NULL DEFAULT 0, |
f INT NULL, |
FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE |
) ENGINE=InnoDB;
|
|
|
INSERT INTO t1 VALUES (1),(2); |
INSERT INTO t2 VALUES (1,1,10),(2,2,20); |
|
|
--connect (con1,localhost,root,,)
|
BEGIN; |
UPDATE t2 SET f = 11 WHERE id = 1; |
|
|
|
|
--connection default
|
SET innodb_lock_wait_timeout=1; |
SET lock_wait_timeout=2; |
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
DELETE FROM t1 WHERE id = 1; |
|
|
--connection con1
|
COMMIT; |
--disconnect con1
|
|
|
--connection default
|
|
|
DROP TABLE t2, t1; |
|
Result with 10.1 or MySQL 5.6 |
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
CREATE TABLE t2 (
|
id INT NOT NULL PRIMARY KEY,
|
ref_id INT NOT NULL DEFAULT 0,
|
f INT NULL,
|
FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE
|
) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (1),(2);
|
INSERT INTO t2 VALUES (1,1,10),(2,2,20);
|
BEGIN;
|
UPDATE t2 SET f = 11 WHERE id = 1;
|
SET innodb_lock_wait_timeout=1;
|
SET lock_wait_timeout=2;
|
DELETE FROM t1 WHERE id = 1;
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
COMMIT;
|
DROP TABLE t2, t1;
|
bug.t4 [ pass ] 3621
|
|
Result with 10.2 or MySQL 5.7 |
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
CREATE TABLE t2 (
|
id INT NOT NULL PRIMARY KEY,
|
ref_id INT NOT NULL DEFAULT 0,
|
f INT NULL,
|
FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE
|
) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (1),(2);
|
INSERT INTO t2 VALUES (1,1,10),(2,2,20);
|
connect con1,localhost,root,,;
|
BEGIN;
|
UPDATE t2 SET f = 11 WHERE id = 1;
|
connection default;
|
SET innodb_lock_wait_timeout=1;
|
SET lock_wait_timeout=2;
|
DELETE FROM t1 WHERE id = 1;
|
the test case hangs after that. With MySQL the last line, DELETE, is not printed, it's normal and signifies the same result.
Attachments
Issue Links
- causes
-
-
- Closed
-
- relates to
-
-
- Closed
-
- links to