From: Jon Olav Hauglid Date: February 22 2011 2:02pm Subject: bzr commit into mysql-5.5 branch (jon.hauglid:3353) Bug#11765416 List-Archive: http://lists.mysql.com/commits/131856 X-Bug: 11765416 Message-Id: <201102221402.p1M11gqQ006551@rcsinet13.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============4635561389480200665==" --===============4635561389480200665== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/x/mysql-5.5-bug11765416/ based on revid:jorgen.loland@stripped 3353 Jon Olav Hauglid 2011-02-22 Bug #11765416 (former 58381) FAILED DROP DATABASE CAN BREAK STATEMENT BASED REPLICATION The first phase of DROP DATABASE is to delete the tables in the database. If deletion of one or more of the tables fail (e.g. due to a FOREIGN KEY constraint), DROP DATABASE will be aborted. However, some tables could still have been deleted. The problem was that nothing would be written to the binary log in this case, so any slaves would not delete these tables. Therefore the master and the slaves would get out of sync. This patch fixes the problem by making sure that DROP TABLE is written to the binary log for the tables that were in fact deleted by the failed DROP DATABASE statement. Test case added to mysqlbinlog.test. modified: mysql-test/r/mysqlbinlog.result mysql-test/t/mysqlbinlog.test sql/sql_db.cc === modified file 'mysql-test/r/mysqlbinlog.result' --- a/mysql-test/r/mysqlbinlog.result 2010-12-29 05:22:52 +0000 +++ b/mysql-test/r/mysqlbinlog.result 2011-02-22 14:02:34 +0000 @@ -903,3 +903,26 @@ master-bin.000002 # Query # # CREATE DAT master-bin.000002 # Query # # use `test1`; CREATE TABLE t1(id int) master-bin.000002 # Query # # use `test1`; DROP TABLE `t1` /* generated by server */ master-bin.000002 # Query # # DROP DATABASE test1 +USE test; +# +# Bug#11765416 58381: FAILED DROP DATABASE CAN BREAK STATEMENT +# BASED REPLICATION +# +DROP DATABASE IF EXISTS db1; +DROP TABLE IF EXISTS t3; +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +CREATE TABLE db1.t2 (b INT, KEY(b)) engine=innodb; +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 +SHOW TABLES FROM db1; +Tables_in_db1 +t2 +show binlog events from ; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `db1`; drop table `t1` +DROP TABLE t3; +DROP DATABASE db1; === modified file 'mysql-test/t/mysqlbinlog.test' --- a/mysql-test/t/mysqlbinlog.test 2010-12-29 05:22:52 +0000 +++ b/mysql-test/t/mysqlbinlog.test 2011-02-22 14:02:34 +0000 @@ -526,4 +526,32 @@ exec $MYSQL_BINLOG $MYSQLD_DATADIR/$mast let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); source include/show_binlog_events.inc; +USE test; +let $binlog_file=; + +--echo # +--echo # Bug#11765416 58381: FAILED DROP DATABASE CAN BREAK STATEMENT +--echo # BASED REPLICATION +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +DROP TABLE IF EXISTS t3; +--enable_warnings + +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +CREATE TABLE db1.t2 (b INT, KEY(b)) engine=innodb; +CREATE TABLE t3 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b)) + engine=innodb; +RESET MASTER; + +--error ER_ROW_IS_REFERENCED +DROP DATABASE db1; # Fails because of the fk +SHOW TABLES FROM db1; # t1 was dropped, t2 remains +--source include/show_binlog_events.inc # Check that the binlog drops t1 + +# Cleanup +DROP TABLE t3; +DROP DATABASE db1; === modified file 'sql/sql_db.cc' --- a/sql/sql_db.cc 2010-12-10 11:32:58 +0000 +++ b/sql/sql_db.cc 2011-02-22 14:02:34 +0000 @@ -833,12 +833,9 @@ bool mysql_rm_db(THD *thd,char *db,bool } thd->push_internal_handler(&err_handler); - if (thd->killed || - (tables && mysql_rm_table_no_locks(thd, tables, true, false, true, true))) - { - tables= NULL; - } - else + if (!thd->killed && + !(tables && + mysql_rm_table_no_locks(thd, tables, true, false, true, true))) { /* We temporarily disable the binary log while dropping the objects @@ -926,6 +923,7 @@ update_binlog: else if (mysql_bin_log.is_open()) { char *query, *query_pos, *query_end, *query_data_start; + char buf[FN_REFLEN + 1]; TABLE_LIST *tbl; uint db_len; @@ -939,6 +937,12 @@ update_binlog: { uint tbl_name_len; + // Only write drop table to the binlog for tables that no longer exist. + build_table_filename(buf, sizeof(buf) - 1, tbl->db, + tbl->table_name, reg_ext, 0); + if (access(buf, F_OK) == 0) + continue; + /* 3 for the quotes and the comma*/ tbl_name_len= strlen(tbl->table_name) + 3; if (query_pos + tbl_name_len + 1 >= query_end) --===============4635561389480200665== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jon.hauglid@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jon.hauglid@stripped # target_branch: file:///export/home/x/mysql-5.5-bug11765416/ # testament_sha1: 85d8dcae5a567e990cb443392f5f3018764501bc # timestamp: 2011-02-22 15:02:41 +0100 # base_revision_id: jorgen.loland@stripped\ # lbyzzk52hiht7i5j # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWR5V/0oABDt/gHQwkIB9b/// f//+6v////BgCg6U53c+PR60atuqUt3veQEqHXdvvvfT19hCRRTAJM0JjQSbUj2JPST1PU2KeoyA HknqaaemmQmgSiJkmZGTVM9RT0jJoaD1AAZBoAAAAAJRJtTEaaTU9JPUwnpqPUAGNIyaDRoAAAAB iJGhNAU/1E9JG1PKPUPRNPRNGmjTI9T1AaA000DQRqUaGmgADQABoAGgyAAAAAaBJIEAJphTAmjJ Nqnk01NJ7VM9UMNQ0ek2mo0AbKUY2L55b3X4nri3xEkN6Kkz/a9VerjDRPR38Xu8dZS7/DsvVV3M 7Rhf1SvB07weVYDCVMrBCJMaGPBYw6eZqsDGjoaoXHZ9UBHYxTxL2bLlxTYuV1ST3ZdFPLiq8DKk gmSylBBUB7e74U5fdWWGQjePHDL5LfrW8rUxsbbOry5PBGCWnprZ7nkj8ZmEJtERB807MmWZZmYP t7MtGI5R6TxsOYlwkblpty4v0Cg75D2Dq+XJdCUPAvTDGXzyfnhMC7ViKgbGdxd6TkwwW/HbbukD /XLokzy9Tl3gzjBj9o5a1NfPqjLf1sxwVYSrhZtOD8rA3OLGOXNc6L4uxLomKvvq1JksVoOcZVHQ vgk3sYdlgfU+bi6lsuizHNFOMklFZVsIiNzGdaGK5SxtfRrYq9XOZFHc7bhjS4it3F6NF/DBV1jh 4vC2lz5sK4gXQJ1Hs8aQSQWvW+wF6AieUIjEQK8uWRfvHbY+q2E/NtIrpCq24UdBdlkmVEgTkHn+ grjvOnGBnKg4ADcYPuIVOuP4pbSf0nHDUsAUnLoNtMIlRX2CKb2xynOq7bPTtU8RhKhnJFDKdFus Tg8+7apSnDJW/W2yYAqZPc36pd5qCE7DlIZCYQYCMDrNqkEScragjmxUKalE6c1pH+GehFXQ8epA 6dz2E37ZmCp3akEcaDDtex6CKsE4U264UM8Estd1aWbOYvmtLRGpGQ8ivK3VOqzQWKGY6IbevDoi G7iQU5pjRIe7YIwV+18DEbBUm1wpJ41TFRYCJq0Kjy1p4hhEiKsHiHjGAP11kxAOllI0lS6y60zw Xbq0TNHROrRx+uM5xWSN/hYQMZLfpMHBtJlFwo21QEOx13G85kc2BhqDRUWhOV1cygsCFIbWjK2u jLYIW0QC+bVsEwm9IEiEa3aLTbNxxwKFLVYnHTyERIUC1Mq/03iPaSmvywgXsdFJLkOHGBp5Ljvr 0hauM9gVBAccuVoYESUlapGo2nxrgMNX0nJRyp+RVTfbaxqwePTiRXYHDlMSK8dLkwVEI79ovhNc 6uaweYyyIG7BnNnM0FC/VQITvMNBmIYoiVkjwM1NQ1Upt8XjA8zWQ+2LMnuuxJPtJxr5Kwz5ECCZ QOPKcypmMUk4czHcBQZAVlg8a9Z6SJzIkiDmL01iIwxYiEnGy97wejKUqXKrCqpgjF6Gc0AmFKZv PD75z4+zZiQHmoeSgvwgOkx68nODrkrJWzmWmIxMlYafQptXOzVOJcZ0JC7F9y/NdiA+89wlACSi rjFmGZhvzEtLhyE5S7nI967VKBFSJNAQE22NnYqRniQEwgk2wMulIqrgoKCUU9ytTJ5oU9EickTk A9qX8y0Hk9gfF6UFMOKUwRV6cKxfEPlTVhMv0nSzSLFggf4qJSUuqCc6+KgKgVFeEimIIr5EKChA TK0c4sWQFMhoqKerCk1oCmaQtIXIXyGB5OUJ5ci3YpDCgJRQs05IYZMEyewWqSR0cXfZPTf6Olbe tauuO1RXpYvVsmiOSSfsiK45d0JiwxruBRQ7BIDV4jkKcJcNxzvcct4mMnAacnhMPXmREzxfhiNc J63puarBanndMSb460zmyVlSGughyCERh5XqV8uQqaFQ/gmJG1RedDWNOMGoEj1fBZUnAk9Q62A4 jzIP2si1L1/9/9mUpcxkFw3A+nBVV/BxYt/rEaOJkde/x75nHA/gqLcJhO1PPrnJB/le1ZrLfhdC wlLsZM8UlN7C+gNGDL8YTG02FVehwhBt1wNZmUC2vTaWkKdWBUidG0duJJOTJDW+oyMe73doPAPd tDT45bOvkZ0ChuDA4Fwej4GJFsKAzvdwo3zdlj93UiDRTHo4OkP3ygqWUYkDOemZ40nMQSXNlnwM dVp3vrd7s+w9ZMUlvsWhW4hws4OTQCkTBTm7dnvCECiVux5Vo+b35t+uz8uPSYtHsyxZ/NxeLAmv UM1nHiu6Y1suQmRtfTRF1UXP4bt/K3f+yGgXwoHLnR16PO0B2lTaWFj6dMRPfMlvnrO72sHQ2ldg uqHE8XBvgeBlE6yLii9EOYem4NPoXlWOdNrIchsMBcLzW7z6yPMXMFqKCB54EUo6YUsOHYXsyTU9 e0gFIKYRryW0ym7SXR0gcRDP9KMEi5wHayZgy83v6kw6YvKAqLx7J2XaI0mg4IKllzK3mHk4c1ME oJEJ8fPwhVgPMrODgEQfmp4bhAqD57mOD5C6gzdXgTIXvCpdlfbypfvFfwKy4zyFkerbwYaJRSG7 6mEVkT0qg955Ay04MunvKayytQXkXYP9nULZOX0NTzrMTeXBEUMnhEOa9sh6XatgU2jqnprEk4HM mDwdY8cyDhYKbiI7AVLCNE5ar9q1KioDF30a4uekzCW8crTCWVaDGIBvNkUalJdJkShvvPERlvuo 8bKqAphpH/Vy2jMTsXF1TnYPcErAxIgUA6bZSiYHv3WSBR1RcBLhQgHoAcqsNgiKFqYTVV50QNxf omrHW2KDUGk6GnLmbA38WrYJFxshWcHA65FkBL8GKzAcpbUBYyzokgCMCaJHsQeAcgRlgA7IOTiQ BiKZZYDaueCDPbWBjTYyLy9CRhBArIHSVFQWtg00jdXaP0NGN5AsZn4U2yTltHcEslSypmRdxdQv ghCBBFoTW4QZNdZUCFyS2WBqLbUWNUVoUvO5MldbsLFNMZmG6kMGrBGDOE6MriyTqLRUU9bQQdBY s7idwzY5sk1E9ukJmqWLCEO0hI7G/juAwOFhpwEWQAq3DSkkm2mYMCaMBvrG2sq2FZTpNF4thJdg +VA0JirccqIGqUoQLHfQ4cXIBhVYFy3nDoIHxZzOLV6kvWCmhEWuBeN0pU/nKY7F6qoVuTgKB3wp MIxNor98BLI81EaqbTCyM6zGkKK1uHPWlxQyHLvMvmNe2B14tQqPL5eAfxTZAm7jEPT16MUVYuom d6j+kIePQfQQxJzWP+mtblnAm4lHrJCwwu7xjpMTzNA6DJeBY2FVw8RwHgXsy2YNO9XCNi+c6M8B GSRI+4RSug0XoieggrdDjRYVrQf+LuSKcKEgPKv+lA== --===============4635561389480200665==--