MDEV-29783 ER_NO_SUCH_TABLE_IN_ENGINE after failed CREATE OR REPLACE with FK violation

If atomic C-O-R fails it reverts back to original table. When doing so
InnoDB code checked old table for foreign key consistency and
failed. The patch disables foreign key check when reverting to
original table: they was created in previous statement and current
statement has nothing to do with that.

InnoDB part just disables any warnings in that case. The warnings was
done when the foreign keys was created.

4 files changed, 44 insertions, 1 deletions

diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index 6a151832c06..7a7e9625819 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -1100,3 +1100,24 @@ alter table t1 modify a bigint;
 set foreign_key_checks = on;
 create or replace table t1 (a int) engine=innodb;
 drop tables t1, t2;
+#
+# MDEV-29783 ER_NO_SUCH_TABLE_IN_ENGINE after failed CREATE OR REPLACE with FK violation
+#
+create table t1 (a int, key (a)) engine=innodb;
+create table t2 (b int, foreign key (b) references t1 (a)) engine=innodb;
+set statement foreign_key_checks=off for alter table t1 modify a varchar(8);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(8) DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+create or replace table t1 (c int) engine=innodb;
+ERROR HY000: Can't create table `test`.`./test/t1` (errno: 150 "Foreign key constraint is incorrectly formed")
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(8) DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+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 8ceaf5ffcbc..f5259dda55c 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -1115,4 +1115,17 @@ set foreign_key_checks = on;
 create or replace table t1 (a int) engine=innodb;
 drop tables t1, t2;
 
+--echo #
+--echo # MDEV-29783 ER_NO_SUCH_TABLE_IN_ENGINE after failed CREATE OR REPLACE with FK violation
+--echo #
+create table t1 (a int, key (a)) engine=innodb;
+create table t2 (b int, foreign key (b) references t1 (a)) engine=innodb;
+set statement foreign_key_checks=off for alter table t1 modify a varchar(8);
+show create table t1;
+--error ER_CANT_CREATE_TABLE
+create or replace table t1 (c int) engine=innodb;
+show create table t1;
+# cleanup
+drop table t2, t1;
+
 --source include/wait_until_count_sessions.inc
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 5dddd034eb8..0860394c9f5 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4534,7 +4534,10 @@ void HA_CREATE_INFO::finalize_ddl(THD *thd, bool roll_back)
     debug_crash_here("ddl_log_create_fk_fail");
     ddl_log_complete(ddl_log_state_rm);
     debug_crash_here("ddl_log_create_fk_fail2");
+    ulonglong option_bits_save= thd->variables.option_bits;
+    thd->variables.option_bits|= OPTION_NO_FOREIGN_KEY_CHECKS;
     (void) ddl_log_revert(thd, ddl_log_state_create);
+    thd->variables.option_bits= option_bits_save;
     debug_crash_here("ddl_log_create_fk_fail3");
   }
   else
diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc
index 418b4893720..ebf958b92d5 100644
--- a/storage/innobase/row/row0mysql.cc
+++ b/storage/innobase/row/row0mysql.cc
@@ -2796,7 +2796,13 @@ row_rename_table_for_mysql(
 				fk_tables);
 
 			if (err != DB_SUCCESS) {
-				if (old_is_tmp) {
+				if (old_is_tmp && fk == RENAME_FK &&
+				    !trx->check_foreigns) {
+					/* Reverting atomic C-O-R from backup,
+					don't warn anything here. */
+					err = DB_SUCCESS;
+					break;
+				} else if (old_is_tmp) {
 					/* In case of copy alter, ignore the
 					loading of foreign key constraint
 					when foreign_key_check is disabled */