MySQL Bugs: #4680: Failed drop database causes slave to lose data consistency

Bug #4680	Failed drop database causes slave to lose data consistency
Submitted:	21 Jul 2004 18:13	Modified:	31 Aug 2005 19:45
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	all	OS:	Any (all)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
If DROP DATABASE fails because an extra file is in the database directory, then all the tables in the database will have been dropped on the master (they're gone), but the DROP DATABASE command never makes it to the binlog, so they are still on the slave.

If the user then tries to recreate the table in that database, the slave will stop with an error.

By the way, this came up because Mac OS X creates a .DS_Store file every time you browse a folder through the GUI.  But this error could also come if a user did SELECT INTO OUTFILE into the database directory, of for any number of other quasi-legitimate reasons.

How to repeat:
tim@sand:m/40/m$ mysqladmin create repa;
tim@sand:m/40/m$ mysql -e 'create table x (x int)' repa;
tim@sand:m/40/m$ touch data/repa/foo
tim@sand:m/40/m$ ls data/repa
foo   x.MYD x.MYI x.frm
tim@sand:m/40/m$ mysqladmin drop repa 
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'repa' database [y/N] y
/home/tim/m/40/m/bk/client/.libs/lt-mysqladmin: DROP DATABASE repa failed;
error: 'Error dropping database (can't rmdir './repa/', errno: 17)'
tim@sand:m/40/m$ ls data/repa
foo
tim@sand:m/40/m$ mysqlbinlog data/sand-bin.011 | tail
create database repa;
# at 639
#040721 10:03:11 server id 40000  log_pos 639   Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1090425791;
create database `repa`;
# at 692
#040721 10:03:30 server id 40000  log_pos 692   Query   thread_id=9     exec_time=0     error_code=0
use repa;
SET TIMESTAMP=1090425810;
create table x (x int);
tim@sand:m/40/m$ 

Suggested fix:
There are two possible approaches that I see.

1) don't remove any tables until you are sure that the DROP DATABASE operation will succeed

2) log the DROP DATABASE command to the binlog even if it doesn't succeed, and handle it in the slave somehow

(A third approach is to say, "This isn't a MySQL bug - nothing should touch the database directory besides the MySQL server.  This seems fair in one way, but harsh for the person who doesn't even know, e.g., that the GUI browser creates this .DS_Store file, for example.)

Approach #1 is difficult, because even if you check that the only files in the directory are ones that belong to tables (i.e., *.frm, *.MY?, etc.), you still might have a permissions problem, or whatever.  You can't guarantee that the remove operation will be successful.

However, you can catch the most obvious problems.

The other problem is that it could slow down DROP DATABASE significantly, in the case where a user has thousands of tables in a database.  This could be avoided with something like DROP DATABASE foo QUICK, or with SET sql_drop_database_quick = 1.

Approach #2 seems messy to me.

Solution to implement:
- log DROP DATABASE with its non-zero error code
- when slave executes it: as the master's error code is non-zero, do not try to rmdir the directory, just delete all its files.
Then master and slave will be consistent.
This is however not easy to code, as it means the mysql_rm_db() must interrupt itself based on the information in Query_log_event.

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/internals/27872

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/internals/28499

Fixed in versions 4.1.15 and 5.0.12 by adding logging of DROP TABLES for each table in the database we cannot rmdir the database directory because of the extra files in it.

Noted in 4.1.15, 5.0.12 changelogs.