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, innodb_lock_wait_timeout=0; TRUNCATE TABLE parent; ERROR HY000: Lock wait timeout exceeded; try restarting transaction SET DEBUG_SYNC='now SIGNAL go'; connection dml; SELECT * FROM parent; a 3 5 SELECT * FROM child; a 3 5 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; # # MDEV-26052 Assertion prebuilt->trx_id < table->def_trx_id failed # call mtr.add_suppression("InnoDB: In ALTER TABLE `test`\\.`t1` has or is"); CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk), KEY (a)) ENGINE=InnoDB; SET FOREIGN_KEY_CHECKS=0; ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a), ALGORITHM=COPY; INSERT INTO t1 VALUES (1,1); LOCK TABLES t1 WRITE; TRUNCATE t1; ERROR HY000: Cannot add foreign key constraint for `t1` INSERT INTO t1 VALUES (2,2); SELECT * FROM t1; pk a 1 1 2 2 DROP TABLE t1; # End of 10.6 tests