From: Jon Olav Hauglid Date: March 15 2011 10:08am Subject: bzr commit into mysql-5.5 branch (jon.hauglid:3368) Bug#11765416 List-Archive: http://lists.mysql.com/commits/132988 X-Bug: 11765416 Message-Id: <201103151008.p2FA8IQJ022615@acsmt357.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8229313273706111520==" --===============8229313273706111520== 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:chuck.bell@stripped 3368 Jon Olav Hauglid 2011-03-15 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 binlog.binlog_database.test. modified: mysql-test/extra/binlog_tests/database.test mysql-test/suite/binlog/r/binlog_database.result sql/sql_db.cc === modified file 'mysql-test/extra/binlog_tests/database.test' --- a/mysql-test/extra/binlog_tests/database.test 2009-01-23 12:22:05 +0000 +++ b/mysql-test/extra/binlog_tests/database.test 2011-03-15 10:07:58 +0000 @@ -30,3 +30,30 @@ drop table tt1, t1; source include/show_binlog_events.inc; FLUSH STATUS; + + +--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 'mysql-test/suite/binlog/r/binlog_database.result' --- a/mysql-test/suite/binlog/r/binlog_database.result 2010-08-20 02:59:58 +0000 +++ b/mysql-test/suite/binlog/r/binlog_database.result 2011-03-15 10:07:58 +0000 @@ -39,6 +39,28 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `tt1` /* generated by server */ master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ FLUSH STATUS; +# +# 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; set binlog_format=mixed; reset master; create database testing_1; @@ -80,6 +102,28 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `tt1` /* generated by server */ master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ FLUSH STATUS; +# +# 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; set binlog_format=row; reset master; create database testing_1; @@ -122,6 +166,28 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `tt1` /* generated by server */ master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ FLUSH STATUS; +# +# 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; show databases; Database information_schema === modified file 'sql/sql_db.cc' --- a/sql/sql_db.cc 2010-12-10 11:32:58 +0000 +++ b/sql/sql_db.cc 2011-03-15 10:07:58 +0000 @@ -1,4 +1,4 @@ -/* Copyright (C) 2000-2003 MySQL AB, 2008-2009 Sun Microsystems, Inc +/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -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 @@ -923,7 +920,7 @@ update_binlog: thd->server_status|= SERVER_STATUS_DB_DROPPED; my_ok(thd, deleted_tables); } - else if (mysql_bin_log.is_open()) + else if (mysql_bin_log.is_open() && !silent) { char *query, *query_pos, *query_end, *query_data_start; TABLE_LIST *tbl; @@ -938,6 +935,16 @@ update_binlog: for (tbl= tables; tbl; tbl= tbl->next_local) { uint tbl_name_len; + bool exists; + + // Only write drop table to the binlog for tables that no longer exist. + if (check_if_table_exists(thd, tbl, &exists)) + { + error= true; + goto exit; + } + if (exists) + continue; /* 3 for the quotes and the comma*/ tbl_name_len= strlen(tbl->table_name) + 3; --===============8229313273706111520== 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: 14924f4bf7bddf99d546b96ade42ff44c78657da # timestamp: 2011-03-15 11:08:07 +0100 # base_revision_id: chuck.bell@stripped\ # 0vl89i702z9v3gg3 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWdwuBSAABWz/gDQwkAB5d/// f//84P////pgC32u74vdc6vPcelejXroA9NSrbPbEPbbHRiHSEkiExqTyNJsmmp6FPEZpU8aCQaM gDTQGgaAyTSYVPaNTZTSntSekGh6hpkDE0yNAAAAPUGUwqYRPKMTQ9RtQZDQDQAaAAAAAMJJoaTK g2jGpNlBo0Bo9EAYgNAAADjJk0aA0aYjI0MQwJo0xBiNBhAAYJIhMgRkaCYpgRoyT0p5qj2qfqm1 PyiemTTSHqNHqek2DYSP2dk3n0H7A+owUUdPTnGECejvrV0utE2kFiF83OH391exPcm27Zdcmxj4 N16zQ+c4m4KirFcYkaMzbV2wIrJNdhZOvFgfflLNMrJsP0ZoJIE8qBmb9/fEstuhWayIJZVqSzmu 59xaIIMRItAYgQ0SgeNMtqxr+VAagsDKH9R/w2fE7mqCwYGGSr94pojEPdPd0WxGGE1+irckTbO3 S4SLIjsMed4RdfstfL5ulHIHijvTnd+iFEGS60cNXFp/ECwPpIP7hd9cLwKgP9hhQGBgxDk8YcYa EfNGQWgMwMH0C/uF16U6MK3Rcbo4g+CaO+O6T5k0lAFwMAwMv2BpFMxXq75hHu08GkBFNaythGRy YB+kiOmePPA4yvgqNgyoneYieDtfOHLBokQWYAJUnMRrM42FYCdy9WuJp60l13A0gIByARM0sRLg WQGdKi9KTve6qrajkbYy0+1x4NV2Gmsvazw2/l9x2U781x7Y/xlPtTUfQDMAfigu/omYTMmG9/vh aHuTo3MB7gYDyg4Ol+Ww9GTO2fg62V8AeiT4wLpow9IenlpmREBjlL9ydja0CVUHwKDoAOTxM5lb deukmx8UqhFBt9sCh6umcKFk88X0XjzXjVVFPJR6VJMAcoOekQkesj7ATw06OOtTSyLKuaecd67j tAHURSxRHndc8zAqRsVZRWs2nkCjIgLSQpmtsKBthgli5XVPB0MElzowTYFIYNg6GYhzJVjdqwv/ k1J7DRNYBmOUWAU0siQRa+FUAUu2YjOtLd+lyiEx0Gz2QEtofqPUHcjnnSWls82MxDfozQ3YUNOe z0oe5NWDb82gkI3bUbLgdhbDgVHk0VLp1hivIawlbK18gRkuCdX8RUsk8WLDhnkHIEa4HKdsidpC NAaaNoSxCDDKjxbhaIF7r31sMc5veGPFYPEpnfY5InE1hsjAX4G0tyvNZnKFRo5SnHzaVnMLQpuY MLpjwdCY2T3EJYF+JDXqrMxvhpCAqHbyjI0AG4q0QYnSbNgInZWFsc1UUjOESx4UFujFYkZ5ODGo thZaajXLm6xHChg4XStLzdwYYskumbCjVZQCvMQbtDLU800iyNnC03bpoyr/RpBHC29Dm+VOBsIm JbzZbLBEzkLddGdgY354X8XklEKsdH7jCdQ1V2ZbUbShMLQgQLeXWbPBUzULhtRxrSEjGrsyugZy Yi/OBeQNUDTpRgaRhmyJGNZlXIOQ3I+YBmp1OcgGAxpBAyKKRYL1DTfeMDg19JdsK6uURhdWXjLM QzQLRyWxx6iwRUPC86CgtFewMDtm00AxPPrkHLaUWmHoStVYnSAHVBCaiFBVTbH4zC/ja4IB7A2B AKlFL5qIOj5LWvWDb4QCQZua6knoNfipF5bdpqUqR0+aAXtR7EfujyoS94fmAogFSJIyxNSZMzfQ BQcHQJ0Vex0H7I+aKgCApJDIScEzMwMwfFFgMH/QcEwDoTMwDciEW3RRFEREkMjMhkOGCKKuYUCo KBED9UL4izAQCl6HEQRUDlaGAijBDgrkfYD967FomiaFkhFyGD/CJr7BILkOiaPqiAigLEGQwAaU f+B6KiEqkXidOFyMgDPUEkSQ6LwsDahKxTqBaAM6EnCYURAMyC9bUVAwKICkgWpDoQwMhgJogxei pCP9Su4du+nAAxzyjXNEshU9WcUBwPdMZZ0AhJzyAsYcF5VMR6Ra0vkAwmAoBWSO88A9pmO4yqPD Nq7/diJatTgdC03HwrQGyEb/AeuoXwmjq3RD739em6eAsKXDdqpfSVZ6gWrYTqA403t6gMyHoa4C ZlLmi5yJFOTcQc/hzArBB4Mkjf0OxznN4kjxJzA2ASKbjWx9y7D515xAOPXHEf4m0wA1jJHubqOE 9pjPQvJCv0sP6R1jq7NBn6KTebTScS646jMdl6l4/hIYi+u7ROGKwMYWKp/JFAUzICmdbvrGFhoK fEyvV/lZ3dma9xqVicny0dcC/eXZojQaximCJ0NpeIHLuM+9Q3D9SYUQA96y4AWZenfjV5qwKtUL gv3a5iuvfnJGoYzQVNw3VXuyyHgpgGjvKoWioWMsC7hhixyoHgw8A7HJOVVew4OEbAifNPPF42mk 3kZ3qpZgIpvM6Z8AsEyrMDKGbgECBPXht2xLPPn69Ta82PbynCjBul6Wu+DmIFi5MivFVaddSOpV Kbb2WsQyKQr3SdS1M3Wnr6urPfwXjO6cAeDaDBKjDdJcOJwLEU0eALsTvVSCe/h+ryZKLYoUBh7G DULed3bfgV6/QQyJfEd8Y4Jkg7NvavMq1NjFvX1g5ayMN3kgoVc9EqEOSIeqQ/LFdcyJIAsxSjbl 6lkRAagJlj6IktZ1CG6gNvQjSkX4EEHSyZg1+2uMeVh+0iHsuDccRFQ2ZC8FOr6jwW2SLlfeOtwU UjknJHe+OFvmNigEZ5Y1k1oGqduh2xg4gHBY0y2PkFoAsdxzmIA94EEcceG6eWLKBlgUHHsEZbOY ojwFa7nK8dVDGtW07xHtOYMs9e4m6TcTwK48aOTFOVeewVWyTw8Ojmxt9x2BlBk9wosyI3hxzGkj ZFpvyFsABloo63SA0SWgYUjgChzQPHpmoTqy6UbBnZDJkPIKpVaS/Pr8e4hbaoDIDrdQZE9UKNlG +ekq6tUvGE8oTNh55DoF9G85hiGztMkspRmOQwOALq4NBA8hsYAtb+1MVELAIF2BXAA5mAdXX7Uh zfhSdoMrQZgHZOyBq2XzEPrD0Bz9DK9lPdCNpfAB8yOyIl+aYC4z1LmQFbfa4wSGRb6bKkdONiNT Lzt2RxCFf8ABoB0WAdBAJS5HH3lhxyITbf4jpnU5ino3+pz2h/PKxBy2h6GTIaakFFjWcts+MlwV VddRZEy5Bsx9whkELOLfNhjOyIQC9ggy3dEBIcJcMOwnVBn4nWNqE3ctHqgE809keCjENVhfoMcF P1BFjAuNLxpGioZDVUrgGS4ChZEDqwW6a5u2LFIqHfEkJILTUYSCi1sEKAG1j0RLObKJw4QAzR12 BlOc7uP1vBuEyxgc6Gc1q7wmiSS1CNwQwzzBilawXKvEMMRcQTU2Ym3TKnAbXYbNoStUTXZVFRH2 wHGGK9QN+KwUAgwYjgtOiyKpWvMJ3UQJhpPW6UkNEKZkd1cIWNLcUBnvDAutE7IuODiWTfypC63Y Y5u/zlipqHoKa0uDwDSOuKIPY9z0RqpRHyW7JlcVvnXTB3/L7UYYGgFNuKaw66cIjfRYoTb5K1yH Vr9hJeKbLuVq4Igoji6Vh2VCLlx4jhxmg1tCLpQOBxE7heSjgZ0yZStakF+G5cn39BM3ZibBc/FF NZ6rxF5ars1gZussxGclPqP/F3JFOFCQ3C4FIA== --===============8229313273706111520==--