revision-id: ce91482df1af6935f81a2142d0ee9391feab2454 (mariadb-10.2.21-45-gce91482) parent(s): 97930df13c0e403940969ebb47398760b59f753c committer: Alexey Botchkov timestamp: 2019-02-04 12:57:57 +0400 message: MDEV-17599 ALTER TABLE DROP CONSTRAINT does not work for foreign keys. The list of table constraints doesn't include foreign keys and uniques. So we replace DROP CONSTRAINT with DROP [FOREIGN] KEY in this case. --- mysql-test/r/alter_table.result | 49 ++++++++++++++++++++++++++++++++++ mysql-test/t/alter_table.test | 17 ++++++++++++ sql/sql_table.cc | 58 +++++++++++++++++++++++++++++++++++++++++ sql/sql_yacc.yy | 9 +++++-- 4 files changed, 131 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index dcee72e..f243d24 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -2456,5 +2456,54 @@ 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, c2 INT NOT NULL, +CONSTRAINT sid FOREIGN KEY (`c1`) REFERENCES t1 (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, +CONSTRAINT UNIQUE `ui`(c2)) ENGINE= INNODB; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `c1` int(11) DEFAULT NULL, + `c2` int(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `ui` (`c2`), + 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, + `c2` int(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `ui` (`c2`), + KEY `sid` (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t2 DROP CONSTRAINT ui; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `c1` int(11) DEFAULT NULL, + `c2` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `sid` (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t2 DROP CONSTRAINT PRIMARY KEY; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `c1` int(11) DEFAULT NULL, + `c2` int(11) NOT NULL, + 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..e6caadc 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -2013,5 +2013,22 @@ 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, c2 INT NOT NULL, + CONSTRAINT sid FOREIGN KEY (`c1`) REFERENCES t1 (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT UNIQUE `ui`(c2)) ENGINE= INNODB; +SHOW CREATE TABLE t2; +ALTER TABLE t2 DROP CONSTRAINT sid; +SHOW CREATE TABLE t2; +ALTER TABLE t2 DROP CONSTRAINT ui; +SHOW CREATE TABLE t2; +ALTER TABLE t2 DROP CONSTRAINT PRIMARY KEY; +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..bd6ab9e 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -8993,6 +8993,64 @@ 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) + { + { + /* Test if there is a FOREIGN KEY with this name. */ + 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; + } + } + } + + { + /* Test if there is an UNIQUE with this name. */ + uint n_key; + + for (n_key=0; n_key < table->s->keys; n_key++) + { + if ((table->key_info[n_key].flags & HA_NOSAME) && + my_strcasecmp(system_charset_info, + drop->name, table->key_info[n_key].name) == 0) + { + drop->type= Alter_drop::KEY; + alter_info->flags|= Alter_info::ALTER_DROP_INDEX; + goto do_continue; + } + } + } + } + alter_info->flags|= Alter_info::ALTER_DROP_CHECK_CONSTRAINT; +do_continue:; + } + } + handle_if_exists_options(thd, table, alter_info); /* diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index b2b9cb4..cc6670b 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1741,7 +1741,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); IDENT_sys TEXT_STRING_sys TEXT_STRING_literal NCHAR_STRING opt_component key_cache_name sp_opt_label BIN_NUM label_ident TEXT_STRING_filesystem ident_or_empty - opt_constraint constraint opt_ident ident_table_alias + opt_constraint opt_constraint_no_id constraint opt_ident ident_table_alias %type <lex_str_ptr> opt_table_alias @@ -6123,6 +6123,11 @@ check_constraint: } ; +opt_constraint_no_id: + /* Empty */ {} + | CONSTRAINT {} + ; + opt_constraint: /* empty */ { $$= null_lex_str; } | constraint { $$= $1; } @@ -7653,7 +7658,7 @@ alter_list_item: lex->alter_info.drop_list.push_back(ad, thd->mem_root); lex->alter_info.flags|= Alter_info::DROP_FOREIGN_KEY; } - | DROP PRIMARY_SYM KEY_SYM + | DROP opt_constraint_no_id PRIMARY_SYM KEY_SYM { LEX *lex=Lex; Alter_drop *ad= (new (thd->mem_root)