4 files changed, 41 insertions, 5 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-fkcheck.result b/mysql-test/suite/innodb/r/innodb-fkcheck.result
index 2c2be83a3ff..f86ba50597f 100644
--- a/mysql-test/suite/innodb/r/innodb-fkcheck.result
+++ b/mysql-test/suite/innodb/r/innodb-fkcheck.result
@@ -33,11 +33,19 @@ b bigint unsigned NOT NULL,
 d1 date NOT NULL,
 PRIMARY KEY (b,d1)
 ) ENGINE=InnoDB;
+DROP TABLE b;
+set foreign_key_checks = 1;
+CREATE TABLE b (
+b bigint unsigned NOT NULL,
+d1 date NOT NULL,
+PRIMARY KEY (b,d1)
+) ENGINE=InnoDB;
 ERROR HY000: Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
 show warnings;
 Level	Code	Message
 Error	1005	Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
 Warning	1215	Cannot add foreign key constraint for `b`
+set foreign_key_checks = 0;
 DROP TABLE IF EXISTS d;
 Warnings:
 Note	1051	Unknown table 'bug_fk.d'
diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result
index edbdcd2b28e..47e5f0f5c94 100644
--- a/mysql-test/suite/innodb/r/innodb.result
+++ b/mysql-test/suite/innodb/r/innodb.result
@@ -2531,9 +2531,19 @@ disconnect b;
 set foreign_key_checks=0;
 create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
-ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
 set foreign_key_checks=1;
+insert into t2 values (1,1);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
+set foreign_key_checks=0;
+drop table t1;
+set foreign_key_checks=1;
+insert into t2 values (1,1);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
+create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
+ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
 drop table t2;
+create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
+drop table t1;
 set foreign_key_checks=0;
 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
diff --git a/mysql-test/suite/innodb/r/instant_alter_crash.result b/mysql-test/suite/innodb/r/instant_alter_crash.result
index 843f33fddd1..f0fedcc7673 100644
--- a/mysql-test/suite/innodb/r/instant_alter_crash.result
+++ b/mysql-test/suite/innodb/r/instant_alter_crash.result
@@ -34,13 +34,15 @@ ROLLBACK;
 InnoDB		0 transactions not purged
 INSERT INTO t2 VALUES
 (16,1551,'Omnium enim rerum'),(128,1571,' principia parva sunt');
+BEGIN;
+UPDATE t1 SET c2=c2+1;
 connect ddl, localhost, root;
 SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever';
 ALTER TABLE t2 DROP COLUMN c3, ADD COLUMN c5 TEXT DEFAULT 'naturam abhorrere';
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR ddl';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
-UPDATE t1 SET c2=c2+1;
+COMMIT;
 # Kill the server
 disconnect ddl;
 # restart
@@ -61,6 +63,8 @@ DELETE FROM t2;
 ROLLBACK;
 InnoDB		0 transactions not purged
 INSERT INTO t2 VALUES (64,42,'De finibus bonorum'), (347,33101,' et malorum');
+BEGIN;
+DELETE FROM t1;
 connect ddl, localhost, root;
 ALTER TABLE t2 DROP COLUMN c3;
 SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever';
@@ -68,7 +72,7 @@ ALTER TABLE t2 ADD COLUMN (c4 TEXT NOT NULL DEFAULT ' et malorum');
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR ddl';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
-DELETE FROM t1;
+COMMIT;
 # Kill the server
 disconnect ddl;
 # restart
@@ -138,6 +142,8 @@ InnoDB		0 transactions not purged
 #
 # MDEV-24323 Crash on recovery after kill during instant ADD COLUMN
 #
+BEGIN;
+INSERT INTO t1 VALUES(0,0);
 connect ddl, localhost, root;
 CREATE TABLE t3(id INT PRIMARY KEY, c2 INT, v2 INT AS(c2) VIRTUAL, UNIQUE(v2))
 ENGINE=InnoDB;
@@ -147,7 +153,7 @@ ALTER TABLE t3 ADD COLUMN c3 TEXT NOT NULL DEFAULT 'sic transit gloria mundi';
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR ddl';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
-INSERT INTO t1 VALUES(0,0);
+COMMIT;
 # Kill the server
 disconnect ddl;
 # restart
@@ -183,13 +189,15 @@ DROP TABLE t2,t3;
 #
 CREATE TABLE t2(a INT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;
 INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6);
+BEGIN;
+DELETE FROM t1;
 connect ddl, localhost, root;
 SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever';
 ALTER TABLE t2 ADD COLUMN b TINYINT UNSIGNED NOT NULL DEFAULT 42 FIRST;
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR ddl';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
-DELETE FROM t1;
+COMMIT;
 # Kill the server
 disconnect ddl;
 # restart
diff --git a/mysql-test/suite/innodb/r/truncate_foreign.result b/mysql-test/suite/innodb/r/truncate_foreign.result
index 3154674aabf..e587baa5288 100644
--- a/mysql-test/suite/innodb/r/truncate_foreign.result
+++ b/mysql-test/suite/innodb/r/truncate_foreign.result
@@ -80,9 +80,19 @@ 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;
+SET FOREIGN_KEY_CHECKS=1;
 TRUNCATE t1;
 ERROR HY000: Cannot add foreign key constraint for `t1`
 INSERT INTO t1 VALUES (2,2);
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+SELECT * FROM t1;
+pk	a
+1	1
+UNLOCK TABLES;
+INSERT INTO t1 VALUES (2,2);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
+SET FOREIGN_KEY_CHECKS=0;
+INSERT INTO t1 VALUES (2,2);
 SELECT * FROM t1;
 pk	a
 1	1