commit 14a385f37dc2aff290cb6571c6ac2630780b8d03 Author: Laurynas Biveinis Date: Mon Jul 24 06:52:52 2017 +0300 Fix bug 79610 / PS-1683 (Failed DROP DATABASE due FK constraint on master breaks slave) If DROP DATABASE fails to delete any of the tables, it is binlogged as DROP TABLE t1, t2, ... for the tables for which drop succeeded. If slave has different schema so that it references any of the dropped tables, replication will break. Fix by running foreign key checks on all the DROP DATABASE tables before any table is actually dropped. If any FK constraint would fail, do not convert DROP DATABASE to DROP TABLE in the binary log and abort immediately. diff --git a/mysql-test/extra/binlog_tests/database.test b/mysql-test/extra/binlog_tests/database.test index 15a30246d40..714ee971f28 100644 --- a/mysql-test/extra/binlog_tests/database.test +++ b/mysql-test/extra/binlog_tests/database.test @@ -54,11 +54,8 @@ CREATE TABLE test.t1 (c1 INT, FOREIGN KEY(c1) REFERENCES testing_1.t2(c1)) ENGIN --echo # 'DROP DATABASE' will fail but will delete table t1. --echo # ---error ER_ROW_IS_REFERENCED +--error ER_ROW_IS_REFERENCED_2 DROP DATABASE testing_1; -let $wait_binlog_event= DROP TABLE IF EXIST; -source include/wait_for_binlog_event.inc; -let $MYSQLD_DATADIR= `SELECT @@datadir`; DROP TABLE test.t1; --echo # Now we can drop the database. @@ -83,7 +80,7 @@ CREATE TABLE t3 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b)) engine=innodb; RESET MASTER; ---error ER_ROW_IS_REFERENCED +--error ER_ROW_IS_REFERENCED_2 DROP DATABASE db1; # Fails because of the fk # Both t1 and t2 remain as the whole statement is rolled back. SHOW TABLES FROM db1; diff --git a/mysql-test/r/drop_debug.result b/mysql-test/r/drop_debug.result index 7f23ffaf68f..4a017382f00 100644 --- a/mysql-test/r/drop_debug.result +++ b/mysql-test/r/drop_debug.result @@ -157,9 +157,8 @@ Warning 1017 Can't find file: 't1' (errno: 2 - No such file or directory) # 4) Error handling by DROP DATABASE. # # -# 4.a) DROP DATABASE which fails due to foreign key error might -# have side effect. This side-effect is limited to non-atomic -# SEs only. +# 4.a) DROP DATABASE which fails foreign key checks will not have +# any side effects. CREATE DATABASE mysqltest; CREATE TABLE mysqltest.t_m (t_m INT) ENGINE=MyISAM; CREATE TABLE mysqltest.t_i_1 (t_i_1 INT) ENGINE= InnoDB; @@ -169,16 +168,14 @@ CREATE TABLE t1 (fk INT, FOREIGN KEY (fk) REFERENCES mysqltest.t_i_2(t_i_2)) ENGINE=InnoDB; DROP DATABASE mysqltest; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails -# Table t_m is gone. +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test) +# DROP DATABASE FK check was failed before dropping a single table SELECT * FROM mysqltest.t_m; -ERROR 42S02: Table 'mysqltest.t_m' doesn't exist -# Database and tables t_i_1, t_i_2 are still there. +t_m SELECT * FROM mysqltest.t_i_1; t_i_1 SELECT * FROM mysqltest.t_i_2; t_i_2 -# Stored function f1() is still there too. SELECT mysqltest.f1(); mysqltest.f1() 0 @@ -188,7 +185,6 @@ mysqltest.f1() # which we have managed to drop before error stay dropped. # Removal of InnoDB tables should be rolled back. DROP TABLE t1; -CREATE TABLE mysqltest.t_m (t_m INT) ENGINE=MyISAM; SET SESSION DEBUG='+d,rm_db_fail_after_dropping_tables'; DROP DATABASE mysqltest; ERROR HY000: Unknown error diff --git a/mysql-test/r/foreign_key_debug.result b/mysql-test/r/foreign_key_debug.result index 6d16ed8f6fc..b4952928ee9 100644 --- a/mysql-test/r/foreign_key_debug.result +++ b/mysql-test/r/foreign_key_debug.result @@ -601,7 +601,7 @@ WHERE referenced_table_schema LIKE 's1'; name unique_constraint_name referenced_table_schema referenced_table_name grandchild_ibfk_1 child_i_key s1 child DROP SCHEMA s1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test) SET @@foreign_key_checks= 0; DROP SCHEMA s1; SET @@foreign_key_checks= 1; @@ -624,7 +624,7 @@ WHERE referenced_table_schema LIKE 's1'; name unique_constraint_name referenced_table_schema referenced_table_name grandchild_ibfk_1 child_i_key s1 child DROP SCHEMA s1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test) SET @@foreign_key_checks= 0; DROP SCHEMA s1; SET @@foreign_key_checks= 1; diff --git a/mysql-test/suite/binlog/r/binlog_database.result b/mysql-test/suite/binlog/r/binlog_database.result index 970e02e8f98..d14aed60b41 100644 --- a/mysql-test/suite/binlog/r/binlog_database.result +++ b/mysql-test/suite/binlog/r/binlog_database.result @@ -58,7 +58,7 @@ CREATE TABLE test.t1 (c1 INT, FOREIGN KEY(c1) REFERENCES testing_1.t2(c1)) ENGIN # 'DROP DATABASE' will fail but will delete table t1. # DROP DATABASE testing_1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test) DROP TABLE test.t1; # Now we can drop the database. DROP DATABASE testing_1; @@ -76,7 +76,7 @@ CREATE TABLE t3 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b)) engine=innodb; RESET MASTER; DROP DATABASE db1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test) SHOW TABLES FROM db1; Tables_in_db1 t1 @@ -144,7 +144,7 @@ CREATE TABLE test.t1 (c1 INT, FOREIGN KEY(c1) REFERENCES testing_1.t2(c1)) ENGIN # 'DROP DATABASE' will fail but will delete table t1. # DROP DATABASE testing_1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test) DROP TABLE test.t1; # Now we can drop the database. DROP DATABASE testing_1; @@ -162,7 +162,7 @@ CREATE TABLE t3 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b)) engine=innodb; RESET MASTER; DROP DATABASE db1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test) SHOW TABLES FROM db1; Tables_in_db1 t1 @@ -232,7 +232,7 @@ CREATE TABLE test.t1 (c1 INT, FOREIGN KEY(c1) REFERENCES testing_1.t2(c1)) ENGIN # 'DROP DATABASE' will fail but will delete table t1. # DROP DATABASE testing_1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test) DROP TABLE test.t1; # Now we can drop the database. DROP DATABASE testing_1; @@ -250,7 +250,7 @@ CREATE TABLE t3 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b)) engine=innodb; RESET MASTER; DROP DATABASE db1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test) SHOW TABLES FROM db1; Tables_in_db1 t1 diff --git a/mysql-test/suite/rpl/r/rpl_drop_db.result b/mysql-test/suite/rpl/r/rpl_drop_db.result index 9a142f2597f..3235bd32b31 100644 --- a/mysql-test/suite/rpl/r/rpl_drop_db.result +++ b/mysql-test/suite/rpl/r/rpl_drop_db.result @@ -21,14 +21,15 @@ include/sync_slave_sql_with_master.inc [connection master] CREATE TABLE test2.t2 (fk INT, FOREIGN KEY (fk) REFERENCES test1.t4(pk)); DROP DATABASE test1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test2) USE test1; include/assert.inc [Tables in test1 database are not dropped] CREATE TABLE test1.t5(n INT) ENGINE=MyISAM; DROP DATABASE test1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails -include/assert.inc [MyISAM table t5 is dropped] -include/assert.inc [InnoDB tables in test1 database are not dropped] +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (test2) +SET foreign_key_checks=0; +DROP DATABASE test1; +SET foreign_key_checks=1; USE test2; CREATE TABLE t1 (n INT); INSERT INTO t1 VALUES (1234); @@ -37,6 +38,5 @@ DROP TABLE t2; Wait until test2.t2 is deleted on slave include/sync_slave_sql_with_master.inc [connection master] -DROP DATABASE test1; DROP DATABASE test2; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_drop_db_fail.result b/mysql-test/suite/rpl/r/rpl_drop_db_fail.result index e46f60adddd..519c9e3a31b 100644 --- a/mysql-test/suite/rpl/r/rpl_drop_db_fail.result +++ b/mysql-test/suite/rpl/r/rpl_drop_db_fail.result @@ -14,7 +14,7 @@ use db2; CREATE TABLE table_child(id INT PRIMARY KEY, info VARCHAR(20), father_id INT) ENGINE=INNODB; ALTER TABLE table_child ADD CONSTRAINT aaa FOREIGN KEY (father_id) REFERENCES db1.table_father(id); DROP DATABASE db1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (db2) DROP DATABASE db2; include/sync_slave_sql_with_master.inc DROP DATABASE db1; diff --git a/mysql-test/suite/rpl/t/rpl_drop_db.test b/mysql-test/suite/rpl/t/rpl_drop_db.test index 2d7c12b8288..b8a984b65b4 100644 --- a/mysql-test/suite/rpl/t/rpl_drop_db.test +++ b/mysql-test/suite/rpl/t/rpl_drop_db.test @@ -39,7 +39,7 @@ CREATE TABLE test1.t4 (pk INT PRIMARY KEY); --source include/rpl_connection_master.inc CREATE TABLE test2.t2 (fk INT, FOREIGN KEY (fk) REFERENCES test1.t4(pk)); ---error ER_ROW_IS_REFERENCED +--error ER_ROW_IS_REFERENCED_2 DROP DATABASE test1; USE test1; # No tables should be dropped after WL#9536. @@ -50,16 +50,16 @@ USE test1; # Finally, case when we fail while dropping table and there are both InnoDB and MyISAM tables. CREATE TABLE test1.t5(n INT) ENGINE=MyISAM; ---error ER_ROW_IS_REFERENCED +--error ER_ROW_IS_REFERENCED_2 DROP DATABASE test1; ---let assert_text= MyISAM table t5 is dropped ---let assert_cond="[SHOW TABLES LIKE "t5"]"="" ---source include/assert.inc +--let assert_text= Tables in test1 database are not dropped +--let assert_cond="[SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="test1"]"=5 ---let assert_text= InnoDB tables in test1 database are not dropped ---let assert_cond="[SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="test1"]"=4 ---source include/assert.inc +# Disabling foreign key checks allows DROP DATABASE to proceed +SET foreign_key_checks=0; +DROP DATABASE test1; +SET foreign_key_checks=1; # Ensuring that replication is successful USE test2; @@ -76,7 +76,6 @@ DROP TABLE t2; #cleanup --source include/rpl_connection_master.inc -DROP DATABASE test1; DROP DATABASE test2; --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_drop_db_fail.test b/mysql-test/suite/rpl/t/rpl_drop_db_fail.test index da7992c0007..08b3db5d233 100644 --- a/mysql-test/suite/rpl/t/rpl_drop_db_fail.test +++ b/mysql-test/suite/rpl/t/rpl_drop_db_fail.test @@ -25,7 +25,7 @@ use db2; CREATE TABLE table_child(id INT PRIMARY KEY, info VARCHAR(20), father_id INT) ENGINE=INNODB; ALTER TABLE table_child ADD CONSTRAINT aaa FOREIGN KEY (father_id) REFERENCES db1.table_father(id); ---error ER_ROW_IS_REFERENCED +--error ER_ROW_IS_REFERENCED_2 DROP DATABASE db1; DROP DATABASE db2; --source include/sync_slave_sql_with_master.inc diff --git a/mysql-test/t/drop_debug.test b/mysql-test/t/drop_debug.test index e6410dd8494..5ad19b53f68 100644 --- a/mysql-test/t/drop_debug.test +++ b/mysql-test/t/drop_debug.test @@ -178,9 +178,8 @@ DROP TABLE IF EXISTS t1; --echo # --echo # ---echo # 4.a) DROP DATABASE which fails due to foreign key error might ---echo # have side effect. This side-effect is limited to non-atomic ---echo # SEs only. +--echo # 4.a) DROP DATABASE which fails foreign key checks will not have +--echo # any side effects. CREATE DATABASE mysqltest; CREATE TABLE mysqltest.t_m (t_m INT) ENGINE=MyISAM; CREATE TABLE mysqltest.t_i_1 (t_i_1 INT) ENGINE= InnoDB; @@ -189,15 +188,12 @@ CREATE FUNCTION mysqltest.f1() RETURNS INT RETURN 0; CREATE TABLE t1 (fk INT, FOREIGN KEY (fk) REFERENCES mysqltest.t_i_2(t_i_2)) ENGINE=InnoDB; ---error ER_ROW_IS_REFERENCED +--error ER_ROW_IS_REFERENCED_2 DROP DATABASE mysqltest; ---echo # Table t_m is gone. ---error ER_NO_SUCH_TABLE +--echo # DROP DATABASE FK check was failed before dropping a single table SELECT * FROM mysqltest.t_m; ---echo # Database and tables t_i_1, t_i_2 are still there. SELECT * FROM mysqltest.t_i_1; SELECT * FROM mysqltest.t_i_2; ---echo # Stored function f1() is still there too. SELECT mysqltest.f1(); --echo # @@ -206,7 +202,6 @@ SELECT mysqltest.f1(); --echo # which we have managed to drop before error stay dropped. --echo # Removal of InnoDB tables should be rolled back. DROP TABLE t1; -CREATE TABLE mysqltest.t_m (t_m INT) ENGINE=MyISAM; SET SESSION DEBUG='+d,rm_db_fail_after_dropping_tables'; --error ER_UNKNOWN_ERROR DROP DATABASE mysqltest; diff --git a/mysql-test/t/foreign_key_debug.test b/mysql-test/t/foreign_key_debug.test index 65a70ab456d..758af16cba9 100644 --- a/mysql-test/t/foreign_key_debug.test +++ b/mysql-test/t/foreign_key_debug.test @@ -589,7 +589,7 @@ SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_n FROM mysql.foreign_keys WHERE referenced_table_schema LIKE 's1'; ---error ER_ROW_IS_REFERENCED +--error ER_ROW_IS_REFERENCED_2 DROP SCHEMA s1; SET @@foreign_key_checks= 0; @@ -613,7 +613,7 @@ SELECT name, unique_constraint_name, referenced_table_schema, referenced_table_n FROM mysql.foreign_keys WHERE referenced_table_schema LIKE 's1'; ---error ER_ROW_IS_REFERENCED +--error ER_ROW_IS_REFERENCED_2 DROP SCHEMA s1; SET @@foreign_key_checks= 0; diff --git a/sql/dd/cache/dictionary_client.h b/sql/dd/cache/dictionary_client.h index 0e4e0e74d19..de0da1f87c8 100644 --- a/sql/dd/cache/dictionary_client.h +++ b/sql/dd/cache/dictionary_client.h @@ -940,7 +940,9 @@ class Dictionary_client { @param parent_schema Schema name of parent table. @param parent_name Table name of parent table. - @param parent_engine Storage engine of parent table. + @param parent_engine Storage engine of parent table or an empty + string if all child tables should be + returned regardless of their SE @param uncommitted Use READ_UNCOMMITTED isolation. @param[out] children_schemas Schema names of child tables. @param[out] children_names Table names of child tables. diff --git a/sql/dd/impl/cache/dictionary_client.cc b/sql/dd/impl/cache/dictionary_client.cc index c83db5d9a74..6bca6ac1495 100644 --- a/sql/dd/impl/cache/dictionary_client.cc +++ b/sql/dd/impl/cache/dictionary_client.cc @@ -2194,6 +2194,7 @@ bool Dictionary_client::fetch_fk_children_uncached( return true; } + const auto allow_any_se = (parent_engine == ""); Raw_record *r = rs->current_record(); while (r) { /* READ TABLE ID */ @@ -2217,8 +2218,10 @@ bool Dictionary_client::fetch_fk_children_uncached( } if (table) { - // Filter out children in different SEs. This is not supported. - if (my_strcasecmp(system_charset_info, table->engine().c_str(), + // Filter out children in different SEs, unless parent_engine is an empty + // string + if (allow_any_se || + my_strcasecmp(system_charset_info, table->engine().c_str(), parent_engine.c_str()) == 0) { if (uncommitted) { if (acquire_uncached_uncommitted(table->schema_id(), &schema)) { diff --git a/sql/sql_db.cc b/sql/sql_db.cc index 81d4cec2d22..396b14fdf2e 100644 --- a/sql/sql_db.cc +++ b/sql/sql_db.cc @@ -91,6 +91,7 @@ #include "sql/sql_error.h" #include "sql/sql_handler.h" // mysql_ha_rm_tables #include "sql/sql_table.h" // build_table_filename +#include "sql/strfunc.h" // casedn #include "sql/system_variables.h" #include "sql/table.h" // TABLE_LIST #include "sql/thd_raii.h" @@ -475,6 +476,60 @@ class Rmdir_error_handler : public Internal_error_handler { bool m_is_active; }; +/** + Check if two schema names are equal, taking lower_case_table_names into + account. FIXME: this must be handled better: this is hack to be revisited + once Dictionary_client::fetch_fk_children_uncached, documented to be + a "temporary workaround until WL#6049", changes. + @param t1 first schema name + @param t2 second schema name + @return whether the two schema names are equal + */ +MY_ATTRIBUTE((warn_unused_result)) +static bool schema_names_equal(const dd::String_type &t1, + const dd::String_type &t2) { + if (!lower_case_table_names) return t1 == t2; + + return dd::String_type(casedn(system_charset_info, t1)) == + dd::String_type(casedn(system_charset_info, t2)); +} + +/** + Check if DROP DATABASE would fail because of a foreign key constraint + violation. Allows the drop if there are FK constraints between the tables in + the database being dropped and forbids if there are constraints to other + databases with ER_ROW_IS_REFERENCED_2 returned. + @param thd thread handle + @param tables list of the tables in the database being dropped + + @retval false drop is allowed + @retval true drop is forbidden or a DD error has occured + */ +MY_ATTRIBUTE((warn_unused_result)) +static bool check_drop_database_foreign_keys(THD *thd, TABLE_LIST *tables) { + DBUG_ASSERT(thd->lex->sql_command == SQLCOM_DROP_DB); + + if (thd->variables.option_bits & OPTION_NO_FOREIGN_KEY_CHECKS) return false; + + for (const auto *table = tables; table; table = table->next_local) { + std::vector child_schemas; + std::vector child_names; + if (thd->dd_client()->fetch_fk_children_uncached( + table->db, table->table_name, "", false, &child_schemas, + &child_names)) + return true; + + for (const auto &child_schema : child_schemas) { + /* Allow FK constraints to any table in the database being dropped */ + if (!schema_names_equal(table->db, child_schema)) { + my_error(ER_ROW_IS_REFERENCED_2, MYF(0), child_schema.c_str()); + return true; + } + } + } + return false; +} + /** Drop all tables, routines and events in a database and the database itself. @@ -584,6 +639,8 @@ bool mysql_rm_db(THD *thd, const LEX_CSTRING &db, bool if_exists) { lock_db_routines(thd, *schema) || lock_trigger_names(thd, tables)) DBUG_RETURN(true); + if (check_drop_database_foreign_keys(thd, tables)) DBUG_RETURN(true); + /* mysql_ha_rm_tables() requires a non-null TABLE_LIST. */ if (tables) mysql_ha_rm_tables(thd, tables); diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 7f843e47508..412e1be9d24 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2468,9 +2468,13 @@ static bool drop_base_table(THD *thd, const Drop_tables_ctx &drop_ctx, if (atomic && hton->post_ddl) post_ddl_htons->insert(hton); if (error) { - if (error == HA_ERR_ROW_IS_REFERENCED) + if (error == HA_ERR_ROW_IS_REFERENCED) { + // Should be impossible for DROP DATABASE, as FK relationships have been + // checked beforehand, after MDL locks for tables in FK relationships + // have been acquired. + DBUG_ASSERT(thd->lex->sql_command != SQLCOM_DROP_DB); my_error(ER_ROW_IS_REFERENCED, MYF(0)); - else if (error == HA_ERR_TOO_MANY_CONCURRENT_TRXS) + } else if (error == HA_ERR_TOO_MANY_CONCURRENT_TRXS) my_error(HA_ERR_TOO_MANY_CONCURRENT_TRXS, MYF(0)); else { String tbl_name;