| Bug #48084 | slave breaks if 'drop database' fails on master and mismatched tables on slave | ||
|---|---|---|---|
| Submitted: | 15 Oct 2009 14:28 | Modified: | 3 Sep 2010 17:25 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S3 (Non-critical) |
| Version: | 5.1.39, 5.1.41-bzr | OS: | Any |
| Assigned to: | Libing Song | CPU Architecture: | Any |
[15 Oct 2009 15:26]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with recent 5.1.41 from bzr on Mac OS X.
[19 Apr 2010 5:06]
Libing Song
I don't think it is a bug. -------------------------- The process of 'DROP DATABASE' likes this: 1. delete all tables' files one by one. 2. delete the database directory. There sometimes has error happening when deleting a file or directory. So some of the tables in the database are deleted while others are not deleted after 'DROP DATABASE ...' statement. So It is reasonable to binlog a 'DROP TABLE ...' query for all deleted tables when an error happening.
[19 Apr 2010 5:30]
MySQL Verification Team
I'm only asking that 'drop table if exists' is written to binlog when I run 'drop database if exists' on the master. Any reason to not keep the 'if exists' part?
[19 Apr 2010 9:36]
Libing Song
Shane Bester: Sorry, I misunderstood it. So I think we should always use 'DROP TABLE IF NOT EXISTS ...', even though there is no 'IF NOT EXISTS' option in 'DROP DATABASE ...' statements. What is your opinion?
[19 Apr 2010 9:53]
MySQL Verification Team
Agreed, however.. s/IF NOT EXISTS/IF EXISTS/
[20 Apr 2010 4:29]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/106064 3185 Li-Bing.Song@sun.com 2010-04-20 Bug #48084 slave breaks if 'drop database' fails on master and mismatched tables on slave 'DROP TABLE <deleted tables>' was binlogged when 'DROP DATABASE' failed and at least one table was deleted from the database. The log event would lead slave SQL thread stop if some of the tables did not exist on slave. After this patch, It is always binlogged with 'IF EXISTS' option.
[29 Apr 2010 5:11]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/106892 3185 Li-Bing.Song@sun.com 2010-04-29 Bug #48084 slave breaks if 'drop database' fails on master and mismatched tables on slave 'DROP TABLE <deleted tables>' was binlogged when 'DROP DATABASE' failed and at least one table was deleted from the database. The log event would lead slave SQL thread stop if some of the tables did not exist on slave. After this patch, 'DROP DATABASE ..' statement will be binlogged with the error number when 'DROP DATABASE ...' statement fails and at least one table is deleted.
[6 May 2010 6:59]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/107592 3185 Li-Bing.Song@sun.com 2010-05-06 Bug #48084 slave breaks if 'drop database' fails on master and mismatched tables on slave 'DROP TABLE <deleted tables>' was binlogged when 'DROP DATABASE' failed and at least one table was deleted from the database. The log event would lead slave SQL thread stop if some of the tables did not exist on slave. After this patch, It is always binlogged with 'IF EXISTS' option.
[31 May 2010 6:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/109575 3185 Li-Bing.Song@sun.com 2010-05-31 Bug #48084 slave breaks if 'drop database' fails on master and mismatched tables on slave 'DROP TABLE <deleted tables>' was binlogged when 'DROP DATABASE' failed and at least one table was deleted from the database. The log event would lead slave SQL thread stop if some of the tables did not exist on slave. After this patch, It is always binlogged with 'IF EXISTS' option.
[27 Jun 2010 6:25]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/112285 3200 Li-Bing.Song@sun.com 2010-06-27 Bug #48084 slave breaks if 'drop database' fails on master and mismatched tables on slave 'DROP TABLE <deleted tables>' was binlogged when 'DROP DATABASE' failed and at least one table was deleted from the database. The log event would lead slave SQL thread stop if some of the tables did not exist on slave. After this patch, It is always binlogged with 'IF EXISTS' option.
[30 Jun 2010 7:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/112519 3201 Li-Bing.Song@sun.com 2010-06-30 Postfix for BUG#48084. In different platforms, tables are deleted in different order when dropping a database. Both of 'DROP TABLE IF EXISTS `t1`, `t2`' and 'DROP TABLE IF EXISTS `t2`, `t1` are right. So we can't show the binlog event in result file. This postfix uses wait_for_binlog_event.inc instead of show_binlog_events.inc to check that 'DROP TABLE IF EXISTS' is binlogged. On windows platform, the directory is formed with '\'. This postfix uses '/' instead of '\' when writing it into the result file.
[3 Jul 2010 2:03]
Libing Song
Pushed into mysql-5.1-rep+2 and mysql-next-mr-rpl-merge
[23 Jul 2010 12:33]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[4 Aug 2010 8:07]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[4 Aug 2010 8:23]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[6 Aug 2010 14:16]
Jon Stephens
No 5.6 changelog entry needed (does not appear in a 5.6 release). Set status = NM, waiting for pushes to 5.1/5.5.
[3 Sep 2010 17:25]
Jon Stephens
Need Merge status set in error -- my bad. Closed.
Description: a 'drop database if exists' command can fail if there are non-mysql files in the current database. this is sometimes ok, but the binlogs contain unconditional 'drop table a,b,c,d,x' statement that might break when run on slaves. For the testcase, the binlog effectively contains the following: 725 | create database if not exists test 811 | use `test`; drop table if exists t1 897 | use `test`; drop table if exists t2 982 | use `test`; create table t1(a int) 1067 | use `test`; create table t2(a int) 1150 | use `test`; drop table `t1`,`t2` The problem is the last statement. We run "drop database if exists" because we might not know or care what tables existed on the slave. If the slave didn't have t1 or t2 that statement fails. How to repeat: start mysqld with --log-bin --binlog-format=row and a empty test database. create database if not exists test; use test; drop table if exists t1; drop table if exists t2; create table t1(a int); create table t2(a int); select 'hello' into outfile 'stopdrop.sql'; #cause next statement to fail.. drop database if exists test; show binlog events; Suggested fix: At the very least, the last command in binlog should have been: use `test`; drop table if exists `t1`,`t2`