MDEV-22934 Table disappear after two alter table command

Problem:
=======
InnoDB drops the column which has foreign key relations on it. So it
tries to load the foreign key during rename process of copy algorithm
even though the foreign_key_check is disabled.

Solution:
========
During alter copy algorithm, InnoDB ignores the error while loading
the foreign key constraint if foreign key check is disabled. It
should throw the warning about failure of the foreign key constraint
when foreign key check is disabled.

1 files changed, 23 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 27fa63f144e..40bc3a32a4f 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -657,6 +657,8 @@ 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,
@@ -698,6 +700,27 @@ drop table t1,t2;
 --error ER_BAD_TABLE_ERROR
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-22934 Table disappear after two alter table command
+--echo #
+CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT,
+		f2 INT NOT NULL,
+		PRIMARY KEY (f1), INDEX (f2))ENGINE=InnoDB;
+CREATE TABLE t2(f1 INT NOT NULL,
+		f2 INT NOT NULL, f3 INT NOT NULL,
+		PRIMARY KEY(f1, f2), UNIQUE KEY(f2),
+CONSTRAINT `t2_ibfk_1` FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE CASCADE,
+CONSTRAINT `t2_ibfk_2` FOREIGN KEY (f1) REFERENCES t1(f1) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+SET FOREIGN_KEY_CHECKS=0;
+ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(f3), ALGORITHM=INPLACE;
+ALTER TABLE t2 DROP INDEX `f2`, ALGORITHM=COPY;
+SHOW CREATE TABLE t2;
+--error ER_TABLE_EXISTS_ERROR
+CREATE TABLE t2 (f1 INT NOT NULL)ENGINE=InnoDB;
+DROP TABLE t2, t1;
+
 --echo # End of 10.2 tests
 
 --source include/wait_until_count_sessions.inc