commit 894491c22b9b9de7a966e137a4f524d7895ae1ad Author: Laurynas Biveinis Date: Mon Jul 24 06:52:52 2017 +0300 Fix bug 79610 / 1525407 (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 6e4faa63903..28bc605189c 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,11 +80,11 @@ 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 -SHOW TABLES FROM db1; # t1 was dropped, t2 remains +SHOW TABLES FROM db1; # t1 and t2 remain --let $mask_binlog_commit_events= 1 ---source include/show_binlog_events.inc # Check that the binlog drops t1 +--source include/show_binlog_events.inc # Check that the binlog does not drop t1 and t2 --let $mask_binlog_commit_events= 0 # Cleanup diff --git a/mysql-test/r/drop_debug.result b/mysql-test/r/drop_debug.result index 3366a54301b..3a2451ef0e5 100644 --- a/mysql-test/r/drop_debug.result +++ b/mysql-test/r/drop_debug.result @@ -162,13 +162,6 @@ 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 might change once WL#6049 is -# implemented and we support smarter checks while dropping -# tables with foreign keys. Also once InnoDB truly supports -# atomic DDL side-effect will be limited to non-atomic SEs -# only. 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; @@ -178,17 +171,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, t_i_1 is gone too (this will change after -# WL#6049 and once support for atomic DDL is implemented in InnoDB). +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 +t_m SELECT * FROM mysqltest.t_i_1; -ERROR 42S02: Table 'mysqltest.t_i_1' doesn't exist -# Database and table t_i_2 are still there. +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 @@ -199,8 +189,6 @@ mysqltest.f1() # Once InnoDB supports atomic DDL removal of InnoDB tables # should be rolled back (they are not now). DROP TABLE t1; -CREATE TABLE mysqltest.t_m (t_m INT) ENGINE=MyISAM; -CREATE TABLE mysqltest.t_i_1 (t_i_1 INT) ENGINE=InnoDB; SET SESSION DEBUG='+d,rm_db_fail_after_dropping_tables'; DROP DATABASE mysqltest; ERROR HY000: Unknown error diff --git a/mysql-test/suite/binlog/r/binlog_database.result b/mysql-test/suite/binlog/r/binlog_database.result index 3ced8837705..f4978ebd812 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,13 +76,13 @@ 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 t2 include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `db1`; DROP TABLE IF EXISTS `t1` DROP TABLE t3; DROP DATABASE db1; RESET MASTER; @@ -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,13 +162,13 @@ 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 t2 include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `db1`; DROP TABLE IF EXISTS `t1` DROP TABLE t3; DROP DATABASE db1; RESET MASTER; @@ -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,13 +250,13 @@ 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 t2 include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `db1`; DROP TABLE IF EXISTS `t1` DROP TABLE t3; DROP DATABASE db1; RESET MASTER; diff --git a/mysql-test/suite/rpl/r/rpl_drop_db.result b/mysql-test/suite/rpl/r/rpl_drop_db.result index e027302b722..345a6b42a31 100644 --- a/mysql-test/suite/rpl/r/rpl_drop_db.result +++ b/mysql-test/suite/rpl/r/rpl_drop_db.result @@ -21,16 +21,71 @@ 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 [Table t4 is not dropped] +include/assert.inc [Tables in test1 database are not dropped] +SET foreign_key_checks=0; +DROP DATABASE test1; +SET foreign_key_checks=1; +CREATE DATABASE test1; +USE test1; +CREATE TABLE test1.t4 (pk INT PRIMARY KEY); 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) SHOW TABLES; Tables_in_test1 +db_test1_long_table_name1 +db_test1_long_table_name10 +db_test1_long_table_name11 +db_test1_long_table_name12 +db_test1_long_table_name13 +db_test1_long_table_name14 +db_test1_long_table_name15 +db_test1_long_table_name16 +db_test1_long_table_name17 +db_test1_long_table_name18 +db_test1_long_table_name19 +db_test1_long_table_name2 +db_test1_long_table_name20 +db_test1_long_table_name21 +db_test1_long_table_name22 +db_test1_long_table_name23 +db_test1_long_table_name24 +db_test1_long_table_name25 +db_test1_long_table_name26 +db_test1_long_table_name27 +db_test1_long_table_name28 +db_test1_long_table_name29 +db_test1_long_table_name3 +db_test1_long_table_name30 +db_test1_long_table_name31 +db_test1_long_table_name32 +db_test1_long_table_name33 +db_test1_long_table_name34 +db_test1_long_table_name35 +db_test1_long_table_name36 +db_test1_long_table_name37 +db_test1_long_table_name38 +db_test1_long_table_name39 +db_test1_long_table_name4 +db_test1_long_table_name40 +db_test1_long_table_name41 +db_test1_long_table_name42 +db_test1_long_table_name43 +db_test1_long_table_name44 +db_test1_long_table_name45 +db_test1_long_table_name46 +db_test1_long_table_name47 +db_test1_long_table_name48 +db_test1_long_table_name49 +db_test1_long_table_name5 +db_test1_long_table_name50 +db_test1_long_table_name6 +db_test1_long_table_name7 +db_test1_long_table_name8 +db_test1_long_table_name9 t4 -include/assert.inc [db_test1_long_table_name tables are dropped] -include/assert.inc [Table t4 is not dropped] +include/assert.inc [Tables in test1 database are not dropped] USE test2; CREATE TABLE t1 (n INT); INSERT INTO t1 VALUES (1234); 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 9fbfc292c94..5e01bde046c 100644 --- a/mysql-test/suite/rpl/t/rpl_drop_db.test +++ b/mysql-test/suite/rpl/t/rpl_drop_db.test @@ -40,14 +40,21 @@ 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; -# Some tables can be dropped. USE test1; ---let assert_text= Table t4 is not dropped ---let assert_cond="[SHOW TABLES LIKE "t4"]"="t4" +--let assert_text= 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; + +CREATE DATABASE test1; +USE test1; +CREATE TABLE test1.t4 (pk INT PRIMARY KEY); # Test the branch of the code that deals with the query buffer overflow --disable_query_log let $1=50; @@ -58,16 +65,12 @@ while ($1) } --enable_query_log ---error ER_ROW_IS_REFERENCED +--error ER_ROW_IS_REFERENCED_2 DROP DATABASE test1; SHOW TABLES; ---let assert_text= db_test1_long_table_name$i tables are dropped ---let assert_cond="[SHOW TABLES LIKE "db_test1_long_table_name%"]"="" ---source include/assert.inc - ---let assert_text= Table t4 is not dropped ---let assert_cond="[SHOW TABLES LIKE "t4"]"="t4" +--let assert_text= Tables in test1 database are not dropped +--let assert_cond="[SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="test1"]"=51 --source include/assert.inc # Ensuring that replication is successful 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 2847c9ca692..3e8926e81df 100644 --- a/mysql-test/t/drop_debug.test +++ b/mysql-test/t/drop_debug.test @@ -188,13 +188,6 @@ DROP TABLE IF EXISTS t1; --echo # 4) Error handling by DROP DATABASE. --echo # ---echo # ---echo # 4.a) DROP DATABASE which fails due to foreign key error might ---echo # have side effect. This might change once WL#6049 is ---echo # implemented and we support smarter checks while dropping ---echo # tables with foreign keys. Also once InnoDB truly supports ---echo # atomic DDL side-effect will be limited to non-atomic SEs ---echo # only. 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; @@ -203,17 +196,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, t_i_1 is gone too (this will change after ---echo # WL#6049 and once support for atomic DDL is implemented in InnoDB). ---error ER_NO_SUCH_TABLE +--echo # DROP DATABASE FK check was failed before dropping a single table SELECT * FROM mysqltest.t_m; ---error ER_NO_SUCH_TABLE SELECT * FROM mysqltest.t_i_1; ---echo # Database and table t_i_2 are still there. SELECT * FROM mysqltest.t_i_2; ---echo # Stored function f1() is still there too. SELECT mysqltest.f1(); --echo # @@ -223,8 +211,6 @@ SELECT mysqltest.f1(); --echo # Once InnoDB supports atomic DDL removal of InnoDB tables --echo # should be rolled back (they are not now). DROP TABLE t1; -CREATE TABLE mysqltest.t_m (t_m INT) ENGINE=MyISAM; -CREATE TABLE mysqltest.t_i_1 (t_i_1 INT) ENGINE=InnoDB; SET SESSION DEBUG='+d,rm_db_fail_after_dropping_tables'; --error ER_UNKNOWN_ERROR DROP DATABASE mysqltest; diff --git a/sql/sql_db.cc b/sql/sql_db.cc index 6089d2868ab..7a558403543 100644 --- a/sql/sql_db.cc +++ b/sql/sql_db.cc @@ -84,6 +84,7 @@ #include "sql_security_ctx.h" #include "sql_string.h" #include "sql_table.h" // build_table_filename +#include "strfunc.h" // casedn #include "system_variables.h" #include "table.h" // TABLE_LIST #include "template_utils.h" @@ -513,6 +514,68 @@ private: 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_sql_command(thd) == SQLCOM_DROP_DB); + + if (thd_test_options(thd, 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, + &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. @@ -625,6 +688,9 @@ bool mysql_rm_db(THD *thd,const LEX_CSTRING &db, bool if_exists) || 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);