MDEV-29701 Inconsistencies and eventual failure upon CREATE OR REPLACE with foreign keys

finalize_atomic_replace(): when creating new table it must be checked
that it is valid reference for any existing foreign keys even with
FOREIGN_KEY_CHECKS=OFF. That is the original behavior.

RENAME_CONSTRAINT_IDS: the algorithm was broken when we CREATE OR
REPLACE referenced table. REF_NAME was renamed to backup name and the
new table did't see any referenced keys. We must not change REF_NAME
to backup name in that case. The owner of that foreign key is some
different table and its constraints must be intact. But that doesn't
apply to self-references. The owner of self-reference is the table
being replaced, so we must get them away before the new table is
checked.

4 files changed, 118 insertions, 9 deletions

diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index 0b85048b650..5489fdd94c9 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -1,3 +1,4 @@
+call mtr.add_suppression("foreign key constraints");
 #
 # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
 # DICT_CREATE_FOREIGN_CONSTR
@@ -732,7 +733,6 @@ SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
 # MDEV-17187 table doesn't exist in engine after ALTER other tables
 # with CONSTRAINTs
 #
-call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`\\.`t2` has or is referenced in foreign key constraints which are not compatible with the new table definition.");
 set foreign_key_checks=on;
 create table t1 (id int not null primary key) engine=innodb;
 create table t2 (id int not null primary key, fid int not null,
@@ -1028,3 +1028,64 @@ disconnect con1;
 connection default;
 DROP TABLE IF EXISTS t2, t1;
 # End of 10.6 tests
+#
+# MDEV-29701 Inconsistencies and eventual failure upon CREATE OR REPLACE with foreign keys
+#
+create table t2 (x int primary key) engine=innodb;
+create table t1 (a int references t2 (x), b int references t1 (a)) engine=innodb;
+set session foreign_key_checks = off;
+create or replace table t2 (x int, y int) engine=innodb;
+ERROR HY000: Can't create table `test`.`./test/t2` (errno: 150 "Foreign key constraint is incorrectly formed")
+select * from information_schema.innodb_sys_foreign;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/t1_ibfk_1	test/t1	test/t2	1	0
+test/t1_ibfk_2	test/t1	test/t1	1	0
+create or replace table t2 (x int primary key, new int) engine=innodb;
+select * from information_schema.innodb_sys_foreign;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/t1_ibfk_1	test/t1	test/t2	1	0
+test/t1_ibfk_2	test/t1	test/t1	1	0
+create or replace table t2 (x int, z int) engine=innodb;
+ERROR HY000: Can't create table `test`.`./test/t2` (errno: 150 "Foreign key constraint is incorrectly formed")
+select * from information_schema.innodb_sys_foreign;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/t1_ibfk_1	test/t1	test/t2	1	0
+test/t1_ibfk_2	test/t1	test/t1	1	0
+set session foreign_key_checks= on;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `x` int(11) NOT NULL,
+  `new` int(11) DEFAULT NULL,
+  PRIMARY KEY (`x`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  KEY `a` (`a`),
+  KEY `b` (`b`),
+  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`x`),
+  CONSTRAINT `t1_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+create or replace table t1 (a int references t1 (a)) engine=innodb;
+ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
+select * from information_schema.innodb_sys_foreign;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/t1_ibfk_1	test/t1	test/t2	1	0
+test/t1_ibfk_2	test/t1	test/t1	1	0
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  KEY `a` (`a`),
+  KEY `b` (`b`),
+  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`x`),
+  CONSTRAINT `t1_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+create or replace table t1 (a int) engine=innodb;
+select * from information_schema.innodb_sys_foreign;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+drop tables if exists t1, t2;
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 918d8e8df20..4d8eaf88c13 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -2,6 +2,8 @@
 --source include/count_sessions.inc
 --source include/default_charset.inc
 
+call mtr.add_suppression("foreign key constraints");
+
 --echo #
 --echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
 --echo # DICT_CREATE_FOREIGN_CONSTR
@@ -183,7 +185,6 @@ SET FOREIGN_KEY_CHECKS=1;
 
 --disable_query_log
 call mtr.add_suppression("InnoDB: Table rename might cause two FOREIGN KEY");
-call mtr.add_suppression("InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15\\.");
 --enable_query_log
 
 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
@@ -739,8 +740,6 @@ SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
 --echo # with CONSTRAINTs
 --echo #
 
-call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`\\.`t2` has or is referenced in foreign key constraints which are not compatible with the new table definition.");
-
 set foreign_key_checks=on;
 create table t1 (id int not null primary key) engine=innodb;
 create table t2 (id int not null primary key, fid int not null,
@@ -1072,4 +1071,35 @@ DROP TABLE IF EXISTS t2, t1;
 
 --echo # End of 10.6 tests
 
+--echo #
+--echo # MDEV-29701 Inconsistencies and eventual failure upon CREATE OR REPLACE with foreign keys
+--echo #
+create table t2 (x int primary key) engine=innodb;
+create table t1 (a int references t2 (x), b int references t1 (a)) engine=innodb;
+
+set session foreign_key_checks = off;
+--error ER_CANT_CREATE_TABLE
+create or replace table t2 (x int, y int) engine=innodb;
+select * from information_schema.innodb_sys_foreign;
+create or replace table t2 (x int primary key, new int) engine=innodb;
+select * from information_schema.innodb_sys_foreign;
+--error ER_CANT_CREATE_TABLE
+create or replace table t2 (x int, z int) engine=innodb;
+select * from information_schema.innodb_sys_foreign;
+set session foreign_key_checks= on;
+
+show create table t2;
+show create table t1;
+
+--error ER_CANT_CREATE_TABLE
+create or replace table t1 (a int references t1 (a)) engine=innodb;
+select * from information_schema.innodb_sys_foreign;
+show create table t1;
+
+create or replace table t1 (a int) engine=innodb;
+select * from information_schema.innodb_sys_foreign;
+
+drop tables if exists t1, t2;
+
+
 --source include/wait_until_count_sessions.inc
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 1c20354b86c..03deaad5259 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4483,15 +4483,19 @@ bool HA_CREATE_INFO::finalize_atomic_replace(THD *thd, TABLE_LIST *orig_table)
   param.old_version= tabledef_version;
   param.old_alias= tmp_name.table_name;
   param.new_alias= table_name;
+  ulonglong option_bits_save= thd->variables.option_bits;
+  thd->variables.option_bits&= ~OPTION_NO_FOREIGN_KEY_CHECKS;
   if (ddl_log_create_table(ddl_log_state_create, param.from_table_hton,
                            &cpath, &db, &table_name, false) ||
       rename_table_and_triggers(thd, &param, NULL, &tmp_name, &db, false,
                                 &dummy))
   {
+    thd->variables.option_bits= option_bits_save;
     if (locked_tables_decremented)
       thd->locked_tables_list.add_back_last_deleted_lock(pos_in_locked_tables);
     return true;
   }
+  thd->variables.option_bits= option_bits_save;
   debug_crash_here("ddl_log_create_after_install_new");
   return false;
 }
@@ -5582,7 +5586,13 @@ mysql_rename_table(handlerton *base, const LEX_CSTRING *old_db,
   else if (error ==  ENOTDIR)
     my_error(ER_BAD_DB_ERROR, MYF(0), new_db->str);
   else if (error)
-    my_error(ER_ERROR_ON_RENAME, MYF(0), from, to, error);
+  {
+    if (thd->lex->sql_command == SQLCOM_CREATE_TABLE &&
+        flags & FN_FROM_IS_TMP)
+      my_error(ER_CANT_CREATE_TABLE, MYF(0), new_db->str, to, error);
+    else
+      my_error(ER_ERROR_ON_RENAME, MYF(0), from, to, error);
+  }
   else if (!(flags & FN_IS_TMP))
     mysql_audit_rename_table(thd, old_db, old_name, new_db, new_name);
 
diff --git a/storage/innobase/include/sql_funcs.h b/storage/innobase/include/sql_funcs.h
index 1506a2a5a48..2c3eda1abf3 100644
--- a/storage/innobase/include/sql_funcs.h
+++ b/storage/innobase/include/sql_funcs.h
@@ -104,8 +104,16 @@ R"===(PROCEDURE RENAME_CONSTRAINT_IDS () IS
       END IF;
     END IF;
   END LOOP;
-  UPDATE SYS_FOREIGN SET REF_NAME = :new_table_name
-  WHERE REF_NAME = :old_table_name
-    AND TO_BINARY(REF_NAME)
-      = TO_BINARY(:old_table_name);
+  IF (:new_is_tmp = 0)
+  THEN
+    UPDATE SYS_FOREIGN SET REF_NAME = :new_table_name
+    WHERE REF_NAME = :old_table_name
+      AND TO_BINARY(REF_NAME) = TO_BINARY(:old_table_name);
+  ELSE
+    UPDATE SYS_FOREIGN SET REF_NAME = :new_table_name
+    WHERE REF_NAME = :old_table_name
+      AND TO_BINARY(REF_NAME) = TO_BINARY(:old_table_name)
+      AND FOR_NAME = :new_table_name
+      AND TO_BINARY(FOR_NAME) = TO_BINARY(:new_table_name);
+  END IF;
 END;)===";