mirror of
https://github.com/MariaDB/server.git
synced 2025-04-22 07:05:33 +02:00
676987c4a1
When doing a truncate on an Innodb under lock tables, InnoDB would rename the old table to #sql-... and recreate a new 't1' table. The table lock would still be on the #sql-table. When doing ALTER TABLE, Innodb would do the changes on the #sql table (which would disappear on close). When the SQL layer, as part of inline alter table, would close the original t1 table (#sql in InnoDB) and then reopen the t1 table, Innodb would notice that this does not match it's own (old) t1 table and generate an error. Fixed by adding code in truncate table that if we are under lock tables and truncating an InnoDB table, we would close, reopen and lock the table after truncate. This will remove the #sql table and ensure that lock tables is using the new empty table. Reviewer: Marko Mäkelä
70 lines
2.3 KiB
Text
70 lines
2.3 KiB
Text
CREATE TABLE parent (a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO parent SET a=1;
|
|
CREATE TABLE child (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES parent(a)
|
|
ON UPDATE CASCADE)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO child SET a=1;
|
|
TRUNCATE TABLE parent;
|
|
ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`parent` (`a`))
|
|
TRUNCATE TABLE child;
|
|
INSERT INTO child SET a=1;
|
|
UPDATE parent SET a=2;
|
|
SELECT * FROM child;
|
|
a
|
|
2
|
|
connect dml,localhost,root;
|
|
SET DEBUG_SYNC='foreign_constraint_update_cascade SIGNAL fk WAIT_FOR go';
|
|
UPDATE parent SET a=3;
|
|
connection default;
|
|
SET DEBUG_SYNC='now WAIT_FOR fk';
|
|
SET lock_wait_timeout=1;
|
|
TRUNCATE TABLE child;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC='now SIGNAL go';
|
|
connection dml;
|
|
SELECT * FROM child;
|
|
a
|
|
3
|
|
SET DEBUG_SYNC='foreign_constraint_check_for_update SIGNAL fk WAIT_FOR go';
|
|
DELETE FROM parent;
|
|
connection default;
|
|
SET DEBUG_SYNC='now WAIT_FOR fk';
|
|
SET lock_wait_timeout=1;
|
|
TRUNCATE TABLE child;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC='now SIGNAL go';
|
|
connection dml;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON UPDATE CASCADE)
|
|
SELECT * FROM child;
|
|
a
|
|
3
|
|
INSERT INTO parent SET a=5;
|
|
SET DEBUG_SYNC='foreign_constraint_check_for_ins SIGNAL fk WAIT_FOR go';
|
|
INSERT INTO child SET a=5;
|
|
connection default;
|
|
SET DEBUG_SYNC='now WAIT_FOR fk';
|
|
SET foreign_key_checks=0;
|
|
TRUNCATE TABLE parent;
|
|
SET DEBUG_SYNC='now SIGNAL go';
|
|
connection dml;
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON UPDATE CASCADE)
|
|
SELECT * FROM parent;
|
|
a
|
|
SELECT * FROM child;
|
|
a
|
|
3
|
|
disconnect dml;
|
|
connection default;
|
|
SET DEBUG_SYNC = RESET;
|
|
DROP TABLE child, parent;
|
|
#
|
|
# MDEV-24532 Table corruption ER_NO_SUCH_TABLE_IN_ENGINE or
|
|
# ER_CRASHED_ON_USAGE after ALTER on table with foreign key
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES t1 (a) ON UPDATE CASCADE;
|
|
LOCK TABLE t1 WRITE;
|
|
TRUNCATE TABLE t1;
|
|
ALTER TABLE t1 ADD c INT;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|