revision-id: 43296f4a90cc9281b4ddad498bfab4a4e43131b9 (mariadb-10.2.21-45-g43296f4) parent(s): 97930df13c0e403940969ebb47398760b59f753c committer: Alexey Botchkov timestamp: 2019-01-31 16:23:12 +0400 message: MDEV-17599 ALTER TABLE DROP CONSTRAINT does not work for foreign keys. The list of table constraints doesn't include foreign keys. So we replace DROP CONSTRAINT with DROP FOREIGN KEY in this case. --- mysql-test/r/alter_table.result | 24 ++++++++++++++++++++++++ mysql-test/t/alter_table.test | 11 +++++++++++ sql/sql_table.cc | 38 ++++++++++++++++++++++++++++++++++++++ 3 files changed, 73 insertions(+) diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index dcee72e..8ca62d9 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -2456,5 +2456,29 @@ ERROR 23000: Duplicate entry '1' for key 'i' UNLOCK TABLES; DROP TABLE t1; # +# MDEV-17599 ALTER TABLE DROP CONSTRAINT does not work for foreign keys. +# +CREATE TABLE t1(id INT PRIMARY KEY, c1 INT) ENGINE= INNODB; +CREATE TABLE t2(id INT PRIMARY KEY, c1 INT, CONSTRAINT sid FOREIGN KEY (`c1`) REFERENCES t1 (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE= INNODB; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `c1` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `sid` (`c1`), + CONSTRAINT `sid` FOREIGN KEY (`c1`) REFERENCES `t1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t2 DROP CONSTRAINT sid; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `c1` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `sid` (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t2, t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index df077c8..659cae1 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -2013,5 +2013,16 @@ DROP TABLE t1; --echo # +--echo # MDEV-17599 ALTER TABLE DROP CONSTRAINT does not work for foreign keys. +--echo # + +CREATE TABLE t1(id INT PRIMARY KEY, c1 INT) ENGINE= INNODB; +CREATE TABLE t2(id INT PRIMARY KEY, c1 INT, CONSTRAINT sid FOREIGN KEY (`c1`) REFERENCES t1 (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE= INNODB; +SHOW CREATE TABLE t2; +ALTER TABLE t2 DROP CONSTRAINT sid; +SHOW CREATE TABLE t2; +DROP TABLE t2, t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 1b426f8..c080e5a 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -8993,6 +8993,44 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name, THD_STAGE_INFO(thd, stage_setup); + + if (alter_info->flags & Alter_info::ALTER_DROP_CHECK_CONSTRAINT) + { + /* + ALTER TABLE DROP CONSTRAINT + should be replaced with ... DROP FOREIGN KEY + if the constraint is the FOREIGN KEY one. + */ + + List_iterator<Alter_drop> drop_it(alter_info->drop_list); + Alter_drop *drop; + alter_info->flags&= ~Alter_info::ALTER_DROP_CHECK_CONSTRAINT; + + while ((drop= drop_it++)) + { + if (drop->type == Alter_drop::CHECK_CONSTRAINT) + { + 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) + { + drop->type= Alter_drop::FOREIGN_KEY; + alter_info->flags|= Alter_info::DROP_FOREIGN_KEY; + goto do_continue; + } + } + } + alter_info->flags|= Alter_info::ALTER_DROP_CHECK_CONSTRAINT; +do_continue:; + } + } + handle_if_exists_options(thd, table, alter_info); /*