MDEV-22230 : Unexpected ER_ERROR_ON_RENAME upon DROP non-existing FOREIGN KEY with ALGORITHM=COPYbb-10.4-MDEV-22230

If we are dropping a foreign key and expect it to exists,
we should check does it exists and if not report a error in early
stage i.e. in mysql_prepare_alter_table.

3 files changed, 119 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/innodb_alter_copy.result b/mysql-test/suite/innodb/r/innodb_alter_copy.result
new file mode 100644
index 00000000000..1b836e2e430
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb_alter_copy.result
@@ -0,0 +1,61 @@
+CREATE TABLE t1 (a INT primary key, b int) ENGINE=InnoDB;
+ALTER TABLE t1 DROP FOREIGN KEY x, ALGORITHM=COPY;
+ERROR 42000: Can't DROP FOREIGN KEY `x`; check that it exists
+# Only Warning expected
+ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS x, ALGORITHM=COPY;
+Warnings:
+Note	1091	Can't DROP FOREIGN KEY `x`; check that it exists
+ALTER TABLE t1 DROP KEY x, ALGORITHM=COPY;
+ERROR 42000: Can't DROP INDEX `x`; check that it exists
+ALTER TABLE t1 DROP KEY IF EXISTS x, ALGORITHM=COPY;
+Warnings:
+Note	1091	Can't DROP INDEX `x`; check that it exists
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+ALTER TABLE t1 DROP CONSTRAINT x, ALGORITHM=COPY;
+ERROR 42000: Can't DROP CONSTRAINT `x`; check that it exists
+ALTER TABLE t1 DROP CONSTRAINT IF EXISTS x, ALGORITHM=COPY;
+Warnings:
+Note	1091	Can't DROP CONSTRAINT `x`; check that it exists
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+ALTER TABLE t1 DROP FOREIGN KEY b;
+ERROR 42000: Can't DROP FOREIGN KEY `b`; check that it exists
+CREATE TABLE t2 (a int primary key, b int, foreign key (b) references t1(a)) engine=innodb;
+ALTER TABLE t2 DROP FOREIGN KEY b, ALGORITHM=COPY;
+ERROR 42000: Can't DROP FOREIGN KEY `b`; check that it exists
+ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS b, ALGORITHM=COPY;
+Warnings:
+Note	1091	Can't DROP FOREIGN KEY `b`; check that it exists
+DROP TABLE t2;
+CREATE TABLE t2 (a int primary key, b int, foreign key b(b) references t1(a)) engine=innodb;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `b` (`b`),
+  CONSTRAINT `b` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+ALTER TABLE t2 DROP FOREIGN KEY b, ALGORITHM=COPY;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `b` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/innodb_alter_copy.test b/mysql-test/suite/innodb/t/innodb_alter_copy.test
new file mode 100644
index 00000000000..8f006f0d94c
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb_alter_copy.test
@@ -0,0 +1,36 @@
+--source include/have_innodb.inc
+
+CREATE TABLE t1 (a INT primary key, b int) ENGINE=InnoDB;
+--error ER_CANT_DROP_FIELD_OR_KEY
+ALTER TABLE t1 DROP FOREIGN KEY x, ALGORITHM=COPY;
+--echo # Only Warning expected
+ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS x, ALGORITHM=COPY;
+--error ER_CANT_DROP_FIELD_OR_KEY
+ALTER TABLE t1 DROP KEY x, ALGORITHM=COPY;
+ALTER TABLE t1 DROP KEY IF EXISTS x, ALGORITHM=COPY;
+SHOW CREATE TABLE t1;
+--error ER_CANT_DROP_FIELD_OR_KEY
+ALTER TABLE t1 DROP CONSTRAINT x, ALGORITHM=COPY;
+ALTER TABLE t1 DROP CONSTRAINT IF EXISTS x, ALGORITHM=COPY;
+SHOW CREATE TABLE t1;
+--error ER_CANT_DROP_FIELD_OR_KEY
+ALTER TABLE t1 DROP FOREIGN KEY b;
+#
+# Column name is not same as foreign key constraint name
+#
+CREATE TABLE t2 (a int primary key, b int, foreign key (b) references t1(a)) engine=innodb;
+--error ER_CANT_DROP_FIELD_OR_KEY
+ALTER TABLE t2 DROP FOREIGN KEY b, ALGORITHM=COPY;
+ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS b, ALGORITHM=COPY;
+DROP TABLE t2;
+#
+# These are fine
+#
+CREATE TABLE t2 (a int primary key, b int, foreign key b(b) references t1(a)) engine=innodb;
+SHOW CREATE TABLE t2;
+ALTER TABLE t2 DROP FOREIGN KEY b, ALGORITHM=COPY;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+# Cleanup
+DROP TABLE t1;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 2e581134507..9eb0ffc9c6c 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -8891,10 +8891,32 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
                  alter_info->drop_list.head()->name);
         goto err;
       case Alter_drop::FOREIGN_KEY:
+        /* If we are dropping a foreign key and expect it to exists,
+        we can now check does it exists and if not report a error. */
+        if (!drop->drop_if_exists)
+        {
+          List <FOREIGN_KEY_INFO> fk_child_key_list;
+          FOREIGN_KEY_INFO *f_key;
+          table->file->get_foreign_key_list(thd, &fk_child_key_list);
+          List_iterator<FOREIGN_KEY_INFO> fk_key_it(fk_child_key_list);
+          while ((f_key= fk_key_it++))
+          {
+            if (my_strcasecmp(system_charset_info, f_key->foreign_id->str,
+                              drop->name) == 0)
+              break;
+          }
+          if (!f_key)
+          {
+              my_error(ER_CANT_DROP_FIELD_OR_KEY, MYF(0), drop->type_name(),
+                       drop->name);
+              goto err;
+          }
+        }
         // Leave the DROP FOREIGN KEY names in the alter_info->drop_list.
         break;
       }
     }
+    drop_it.rewind();
   }
 
   if (!create_info->comment.str)