cleanup: foreign-keys.test vs foreign_key.testmariadb-10.1.45

6 files changed, 371 insertions, 377 deletions

diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result
deleted file mode 100644
index d76f01f7095..00000000000
--- a/mysql-test/suite/innodb/r/foreign-keys.result
+++ /dev/null
@@ -1,175 +0,0 @@
-#
-# Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE
-# ADD FOREIGN KEY
-#
-CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT,
-PRIMARY KEY (`department_id`)) engine=innodb;
-CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT,
-`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb;
-CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb;
-ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES
-`people` (`people_id`);
-ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people`
-(`people_id`);
-ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people`
-(`people_id`);
-drop table title, department, people;
-create table t1 (a int primary key, b int) engine=innodb;
-create table t2 (c int primary key, d int,
-foreign key (d) references t1 (a) on update cascade) engine=innodb;
-insert t1 values (1,1),(2,2),(3,3);
-insert t2 values (4,1),(5,2),(6,3);
-flush table t2 with read lock;
-connect  con1,localhost,root;
-delete from t1 where a=2;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
-update t1 set a=10 where a=1;
-connection default;
-unlock tables;
-connection con1;
-connection default;
-lock table t2 write;
-connection con1;
-delete from t1 where a=2;
-connection default;
-unlock tables;
-connection con1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
-connection default;
-unlock tables;
-disconnect con1;
-create user foo;
-grant select,update on test.t1 to foo;
-connect foo,localhost,foo;
-update t1 set a=30 where a=3;
-disconnect foo;
-connection default;
-select * from t2;
-c	d
-5	2
-4	10
-6	30
-drop table t2, t1;
-drop user foo;
-create table t1 (f1 int primary key) engine=innodb;
-create table t2 (f2 int primary key) engine=innodb;
-create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb;
-insert into t1 values (1),(2),(3),(4),(5);
-insert into t2 values (1),(2),(3),(4),(5);
-insert into t3 values (1),(2),(3),(4),(5);
-connect con1,localhost,root;
-set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2';
-alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade;
-connection default;
-set debug_sync='before_execute_sql_command wait_for g1';
-update t1 set f1 = f1 + 100000 limit 2;
-connect con2,localhost,root;
-kill query UPDATE;
-disconnect con2;
-connection default;
-ERROR 70100: Query execution was interrupted
-set debug_sync='now signal g2';
-connection con1;
-show create table t2;
-Table	Create Table
-t2	CREATE TABLE `t2` (
-  `f2` int(11) NOT NULL,
-  PRIMARY KEY (`f2`),
-  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-disconnect con1;
-connection default;
-select * from t2 where f2 not in (select f1 from t1);
-f2
-select * from t3 where f3 not in (select f2 from t2);
-f3
-drop table t3;
-drop table t2;
-drop table t1;
-set debug_sync='reset';
-#
-# MDEV-17595 - Server crashes in copy_data_between_tables or
-#              Assertion `thd->transaction.stmt.is_empty() ||
-#              (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)'
-#              fails in close_tables_for_reopen upon concurrent
-#              ALTER TABLE and FLUSH
-#
-CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;
-INSERT INTO t1 VALUES(1),(2);
-CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB;
-INSERT INTO t2 VALUES(2);
-ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE;
-DROP TABLE t2, t1;
-#
-# MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name)
-#
-CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB;
-CREATE TABLE t2 LIKE t1;
-FLUSH TABLES;
-SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go';
-ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE;
-connect con1, localhost, root;
-SET debug_sync='now WAIT_FOR ready';
-SET lock_wait_timeout=1;
-UPDATE t2 SET pk=10 WHERE pk=1;
-ERROR HY000: Lock wait timeout exceeded; try restarting transaction
-PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1';
-DEALLOCATE PREPARE stmt;
-FLUSH TABLE t2;
-SET debug_sync='now SIGNAL go';
-connection default;
-disconnect con1;
-connection default;
-SET debug_sync='reset';
-SHOW OPEN TABLES FROM test;
-Database	Table	In_use	Name_locked
-DROP TABLE t1, t2;
-create table t1 (pk int primary key, data int) engine=innodb;
-insert t1 values (1,1),(2,2),(3,3);
-create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb;
-insert t2 values (1),(2);
-insert t2 values (10);
-ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_pk`) REFERENCES `t1` (`pk`))
-flush tables;
-flush status;
-update t1 set data=10 where pk+1>10;
-show status like '%opened_tab%';
-Variable_name	Value
-Opened_table_definitions	1
-Opened_tables	1
-flush tables;
-flush status;
-update t2 set t1_pk=11 where t1_pk+1>10;
-show status like '%opened_tab%';
-Variable_name	Value
-Opened_table_definitions	1
-Opened_tables	1
-flush tables;
-flush status;
-lock tables t1 write;
-show status like '%opened_tab%';
-Variable_name	Value
-Opened_table_definitions	2
-Opened_tables	2
-insert t1 values (4,4);
-show status like '%opened_tab%';
-Variable_name	Value
-Opened_table_definitions	2
-Opened_tables	2
-unlock tables;
-create function foo() returns int
-begin
-insert t1 values (5,5);
-return 5;
-end|
-flush tables;
-flush status;
-select foo();
-foo()
-5
-show status like '%opened_tab%';
-Variable_name	Value
-Opened_table_definitions	2
-Opened_tables	2
-drop function foo;
-drop table t2, t1;
diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index 7098faca0c9..378fe74e14b 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -130,3 +130,106 @@ ALTER TABLE t1 DROP f3;
 ALTER TABLE t1 CHANGE f f3 INT;
 DROP TABLE t1;
 SET FOREIGN_KEY_CHECKS=1;
+#
+# Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE
+# ADD FOREIGN KEY
+#
+CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT,
+PRIMARY KEY (`department_id`)) engine=innodb;
+CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT,
+`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb;
+CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb;
+ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES
+`people` (`people_id`);
+ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people`
+(`people_id`);
+ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people`
+(`people_id`);
+drop table title, department, people;
+create table t1 (a int primary key, b int) engine=innodb;
+create table t2 (c int primary key, d int,
+foreign key (d) references t1 (a) on update cascade) engine=innodb;
+insert t1 values (1,1),(2,2),(3,3);
+insert t2 values (4,1),(5,2),(6,3);
+flush table t2 with read lock;
+delete from t1 where a=2;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
+update t1 set a=10 where a=1;
+unlock tables;
+lock table t2 write;
+delete from t1 where a=2;
+unlock tables;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE)
+unlock tables;
+create user foo;
+grant select,update on test.t1 to foo;
+update t1 set a=30 where a=3;
+select * from t2;
+c	d
+5	2
+4	10
+6	30
+drop table t2, t1;
+drop user foo;
+#
+# MDEV-17595 - Server crashes in copy_data_between_tables or
+#              Assertion `thd->transaction.stmt.is_empty() ||
+#              (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)'
+#              fails in close_tables_for_reopen upon concurrent
+#              ALTER TABLE and FLUSH
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1),(2);
+CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES(2);
+ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE;
+DROP TABLE t2, t1;
+create table t1 (pk int primary key, data int) engine=innodb;
+insert t1 values (1,1),(2,2),(3,3);
+create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb;
+insert t2 values (1),(2);
+insert t2 values (10);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_pk`) REFERENCES `t1` (`pk`))
+flush tables;
+flush status;
+update t1 set data=10 where pk+1>10;
+show status like '%opened_tab%';
+Variable_name	Value
+Opened_table_definitions	1
+Opened_tables	1
+flush tables;
+flush status;
+update t2 set t1_pk=11 where t1_pk+1>10;
+show status like '%opened_tab%';
+Variable_name	Value
+Opened_table_definitions	1
+Opened_tables	1
+flush tables;
+flush status;
+lock tables t1 write;
+show status like '%opened_tab%';
+Variable_name	Value
+Opened_table_definitions	2
+Opened_tables	2
+insert t1 values (4,4);
+show status like '%opened_tab%';
+Variable_name	Value
+Opened_table_definitions	2
+Opened_tables	2
+unlock tables;
+create function foo() returns int
+begin
+insert t1 values (5,5);
+return 5;
+end|
+flush tables;
+flush status;
+select foo();
+foo()
+5
+show status like '%opened_tab%';
+Variable_name	Value
+Opened_table_definitions	2
+Opened_tables	2
+drop function foo;
+drop table t2, t1;
diff --git a/mysql-test/suite/innodb/r/foreign_key_debug.result b/mysql-test/suite/innodb/r/foreign_key_debug.result
new file mode 100644
index 00000000000..741200b62b2
--- /dev/null
+++ b/mysql-test/suite/innodb/r/foreign_key_debug.result
@@ -0,0 +1,60 @@
+create table t1 (f1 int primary key) engine=innodb;
+create table t2 (f2 int primary key) engine=innodb;
+create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb;
+insert into t1 values (1),(2),(3),(4),(5);
+insert into t2 values (1),(2),(3),(4),(5);
+insert into t3 values (1),(2),(3),(4),(5);
+connect con1,localhost,root;
+set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2';
+alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade;
+connection default;
+set debug_sync='before_execute_sql_command wait_for g1';
+update t1 set f1 = f1 + 100000 limit 2;
+connect con2,localhost,root;
+kill query UPDATE;
+disconnect con2;
+connection default;
+ERROR 70100: Query execution was interrupted
+set debug_sync='now signal g2';
+connection con1;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `f2` int(11) NOT NULL,
+  PRIMARY KEY (`f2`),
+  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+disconnect con1;
+connection default;
+select * from t2 where f2 not in (select f1 from t1);
+f2
+select * from t3 where f3 not in (select f2 from t2);
+f3
+drop table t3;
+drop table t2;
+drop table t1;
+set debug_sync='reset';
+#
+# MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name)
+#
+CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 LIKE t1;
+FLUSH TABLES;
+SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go';
+ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE;
+connect con1, localhost, root;
+SET debug_sync='now WAIT_FOR ready';
+SET lock_wait_timeout=1;
+UPDATE t2 SET pk=10 WHERE pk=1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1';
+DEALLOCATE PREPARE stmt;
+FLUSH TABLE t2;
+SET debug_sync='now SIGNAL go';
+connection default;
+disconnect con1;
+connection default;
+SET debug_sync='reset';
+SHOW OPEN TABLES FROM test;
+Database	Table	In_use	Name_locked
+DROP TABLE t1, t2;
diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test
deleted file mode 100644
index 9f1622d74cd..00000000000
--- a/mysql-test/suite/innodb/t/foreign-keys.test
+++ /dev/null
@@ -1,202 +0,0 @@
---source include/have_innodb.inc
---source include/have_debug.inc
---source include/have_debug_sync.inc
-
---enable_connect_log
-
---echo #
---echo # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE
---echo # ADD FOREIGN KEY
---echo #
-
-CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT,
-PRIMARY KEY (`department_id`)) engine=innodb;
-
-CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT,
-`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb;
-
-CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb;
-
-ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES
-`people` (`people_id`);
-
-ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people`
-(`people_id`);
-
-ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people`
-(`people_id`);
-
-drop table title, department, people;
-
-#
-# FK and prelocking:
-# child table accesses (reads and writes) wait for locks.
-#
-create table t1 (a int primary key, b int) engine=innodb;
-create table t2 (c int primary key, d int,
-  foreign key (d) references t1 (a) on update cascade) engine=innodb;
-insert t1 values (1,1),(2,2),(3,3);
-insert t2 values (4,1),(5,2),(6,3);
-flush table t2 with read lock; # this takes MDL_SHARED_NO_WRITE
-connect (con1,localhost,root);
---error ER_ROW_IS_REFERENCED_2
-delete from t1 where a=2;
-send update t1 set a=10 where a=1;
-connection default;
-let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock';
-source include/wait_condition.inc;
-unlock tables;
-connection con1;
-reap;
-connection default;
-lock table t2 write; # this takes MDL_SHARED_NO_READ_WRITE
-connection con1;
-send delete from t1 where a=2;
-connection default;
-let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock';
-source include/wait_condition.inc;
-unlock tables;
-connection con1;
---error ER_ROW_IS_REFERENCED_2
-reap;
-connection default;
-unlock tables;
-disconnect con1;
-
-# but privileges should not be checked
-create user foo;
-grant select,update on test.t1 to foo;
-connect(foo,localhost,foo);
-update t1 set a=30 where a=3;
-disconnect foo;
-connection default;
-select * from t2;
-drop table t2, t1;
-drop user foo;
-
-#
-# MDEV-16465 Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys
-#
-create table t1 (f1 int primary key) engine=innodb;
-create table t2 (f2 int primary key) engine=innodb;
-create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb;
-insert into t1 values (1),(2),(3),(4),(5);
-insert into t2 values (1),(2),(3),(4),(5);
-insert into t3 values (1),(2),(3),(4),(5);
-connect con1,localhost,root;
-set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2';
-send alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade;
-connection default;
-let $conn=`select connection_id()`;
-set debug_sync='before_execute_sql_command wait_for g1';
-send update t1 set f1 = f1 + 100000 limit 2;
-connect con2,localhost,root;
-let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock' and info like 'update t1 %';
-source include/wait_condition.inc;
---replace_result $conn UPDATE
-eval kill query $conn;
-disconnect con2;
-connection default;
-error ER_QUERY_INTERRUPTED;
-reap;
-set debug_sync='now signal g2';
-connection con1;
-reap;
-show create table t2;
-disconnect con1;
-connection default;
-select * from t2 where f2 not in (select f1 from t1);
-select * from t3 where f3 not in (select f2 from t2);
-drop table t3;
-drop table t2;
-drop table t1;
-set debug_sync='reset';
-
-
---echo #
---echo # MDEV-17595 - Server crashes in copy_data_between_tables or
---echo #              Assertion `thd->transaction.stmt.is_empty() ||
---echo #              (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)'
---echo #              fails in close_tables_for_reopen upon concurrent
---echo #              ALTER TABLE and FLUSH
---echo #
-CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;
-INSERT INTO t1 VALUES(1),(2);
-CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB;
-INSERT INTO t2 VALUES(2);
-ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE;
-DROP TABLE t2, t1;
-
-
---echo #
---echo # MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name)
---echo #
-CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB;
-CREATE TABLE t2 LIKE t1;
-FLUSH TABLES;
-
-SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go';
-send ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE;
-
-connect con1, localhost, root;
-SET debug_sync='now WAIT_FOR ready';
-SET lock_wait_timeout=1; # change to 0 in 10.3
---error ER_LOCK_WAIT_TIMEOUT
-UPDATE t2 SET pk=10 WHERE pk=1;
-PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1';
-DEALLOCATE PREPARE stmt;
-FLUSH TABLE t2;
-SET debug_sync='now SIGNAL go';
-
-connection default;
-reap;
-
-# Cleanup
-disconnect con1;
-
-connection default;
-SET debug_sync='reset';
-SHOW OPEN TABLES FROM test;
-DROP TABLE t1, t2;
-
-#
-# MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys
-#
-
-create table t1 (pk int primary key, data int) engine=innodb;
-insert t1 values (1,1),(2,2),(3,3);
-create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb;
-insert t2 values (1),(2);
-error ER_NO_REFERENCED_ROW_2;
-insert t2 values (10);
-flush tables;
-flush status;
-# with ON UPDATE RESTRICT child tables are not opened
-update t1 set data=10 where pk+1>10;
-show status like '%opened_tab%';
-flush tables;
-flush status;
-# neither are parent tables
-update t2 set t1_pk=11 where t1_pk+1>10;
-show status like '%opened_tab%';
-# under LOCK TABLES
-flush tables;
-flush status;
-lock tables t1 write;
-show status like '%opened_tab%';
-insert t1 values (4,4);
-show status like '%opened_tab%';
-unlock tables;
-delimiter |;
-create function foo() returns int
-begin
-  insert t1 values (5,5);
-  return 5;
-end|
-delimiter ;|
-flush tables;
-flush status;
-select foo();
-show status like '%opened_tab%';
-drop function foo;
-drop table t2, t1;
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index afaeaa073a2..b33fbf60675 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -158,3 +158,133 @@ ALTER TABLE t1 CHANGE f f3 INT;
 DROP TABLE t1;
 
 SET FOREIGN_KEY_CHECKS=1;
+
+--echo #
+--echo # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE
+--echo # ADD FOREIGN KEY
+--echo #
+
+CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT,
+PRIMARY KEY (`department_id`)) engine=innodb;
+
+CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT,
+`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb;
+
+CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb;
+
+ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES
+`people` (`people_id`);
+
+ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people`
+(`people_id`);
+
+ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people`
+(`people_id`);
+
+drop table title, department, people;
+
+#
+# FK and prelocking:
+# child table accesses (reads and writes) wait for locks.
+#
+create table t1 (a int primary key, b int) engine=innodb;
+create table t2 (c int primary key, d int,
+  foreign key (d) references t1 (a) on update cascade) engine=innodb;
+insert t1 values (1,1),(2,2),(3,3);
+insert t2 values (4,1),(5,2),(6,3);
+flush table t2 with read lock; # this takes MDL_SHARED_NO_WRITE
+connect (con1,localhost,root);
+--error ER_ROW_IS_REFERENCED_2
+delete from t1 where a=2;
+send update t1 set a=10 where a=1;
+connection default;
+let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock';
+source include/wait_condition.inc;
+unlock tables;
+connection con1;
+reap;
+connection default;
+lock table t2 write; # this takes MDL_SHARED_NO_READ_WRITE
+connection con1;
+send delete from t1 where a=2;
+connection default;
+let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock';
+source include/wait_condition.inc;
+unlock tables;
+connection con1;
+--error ER_ROW_IS_REFERENCED_2
+reap;
+connection default;
+unlock tables;
+disconnect con1;
+
+# but privileges should not be checked
+create user foo;
+grant select,update on test.t1 to foo;
+connect(foo,localhost,foo);
+update t1 set a=30 where a=3;
+disconnect foo;
+connection default;
+select * from t2;
+drop table t2, t1;
+drop user foo;
+
+--echo #
+--echo # MDEV-17595 - Server crashes in copy_data_between_tables or
+--echo #              Assertion `thd->transaction.stmt.is_empty() ||
+--echo #              (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)'
+--echo #              fails in close_tables_for_reopen upon concurrent
+--echo #              ALTER TABLE and FLUSH
+--echo #
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1),(2);
+CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES(2);
+ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE;
+DROP TABLE t2, t1;
+
+#
+# MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys
+#
+
+create table t1 (pk int primary key, data int) engine=innodb;
+insert t1 values (1,1),(2,2),(3,3);
+create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb;
+insert t2 values (1),(2);
+error ER_NO_REFERENCED_ROW_2;
+insert t2 values (10);
+flush tables;
+flush status;
+# with ON UPDATE RESTRICT child tables are not opened
+update t1 set data=10 where pk+1>10;
+show status like '%opened_tab%';
+flush tables;
+flush status;
+# neither are parent tables
+update t2 set t1_pk=11 where t1_pk+1>10;
+show status like '%opened_tab%';
+# under LOCK TABLES
+flush tables;
+flush status;
+lock tables t1 write;
+show status like '%opened_tab%';
+insert t1 values (4,4);
+show status like '%opened_tab%';
+unlock tables;
+delimiter |;
+create function foo() returns int
+begin
+  insert t1 values (5,5);
+  return 5;
+end|
+delimiter ;|
+flush tables;
+flush status;
+select foo();
+show status like '%opened_tab%';
+drop function foo;
+drop table t2, t1;
+
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/suite/innodb/t/foreign_key_debug.test b/mysql-test/suite/innodb/t/foreign_key_debug.test
new file mode 100644
index 00000000000..75d05801b5f
--- /dev/null
+++ b/mysql-test/suite/innodb/t/foreign_key_debug.test
@@ -0,0 +1,78 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+
+--enable_connect_log
+
+#
+# MDEV-16465 Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys
+#
+create table t1 (f1 int primary key) engine=innodb;
+create table t2 (f2 int primary key) engine=innodb;
+create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb;
+insert into t1 values (1),(2),(3),(4),(5);
+insert into t2 values (1),(2),(3),(4),(5);
+insert into t3 values (1),(2),(3),(4),(5);
+connect con1,localhost,root;
+set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2';
+send alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade;
+connection default;
+let $conn=`select connection_id()`;
+set debug_sync='before_execute_sql_command wait_for g1';
+send update t1 set f1 = f1 + 100000 limit 2;
+connect con2,localhost,root;
+let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock' and info like 'update t1 %';
+source include/wait_condition.inc;
+--replace_result $conn UPDATE
+eval kill query $conn;
+disconnect con2;
+connection default;
+error ER_QUERY_INTERRUPTED;
+reap;
+set debug_sync='now signal g2';
+connection con1;
+reap;
+show create table t2;
+disconnect con1;
+connection default;
+select * from t2 where f2 not in (select f1 from t1);
+select * from t3 where f3 not in (select f2 from t2);
+drop table t3;
+drop table t2;
+drop table t1;
+set debug_sync='reset';
+
+--echo #
+--echo # MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name)
+--echo #
+CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 LIKE t1;
+FLUSH TABLES;
+
+SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go';
+send ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE;
+
+connect con1, localhost, root;
+SET debug_sync='now WAIT_FOR ready';
+SET lock_wait_timeout=1; # change to 0 in 10.3
+--error ER_LOCK_WAIT_TIMEOUT
+UPDATE t2 SET pk=10 WHERE pk=1;
+PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1';
+DEALLOCATE PREPARE stmt;
+FLUSH TABLE t2;
+SET debug_sync='now SIGNAL go';
+
+connection default;
+reap;
+
+# Cleanup
+disconnect con1;
+
+connection default;
+SET debug_sync='reset';
+SHOW OPEN TABLES FROM test;
+DROP TABLE t1, t2;
+
+#
+# End of 10.1 tests
+#