MySQL Bugs: #28595: DROP TABLE without USE TABLE not replicated

Bug #28595	DROP TABLE without USE TABLE not replicated
Submitted:	22 May 2007 14:45	Modified:	19 Mar 2010 14:12
Reporter:	Hubert Hoefsloot	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.32	OS:	Linux (Debian etch)
Assigned to:		CPU Architecture:	Any
Tags:	drop table, replication, slave

Description:
A drop table statement like 'DROP TABLE `gpt-test`.`test_table`' does not show in the binlog, but 'USE `gpt-test`;DROP TABLE `test_table`' will.

How to repeat:
On master:

scripts@kimiko:~$ mysql -u wp_upd -p.....
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 208277
Server version: 5.0.32-Debian_7etch1-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE `gpt-test`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `test_table` (
    -> `index` int( 11 ) NOT NULL AUTO_INCREMENT ,
    -> PRIMARY KEY ( `index` )
    -> ) ENGINE = MYISAM DEFAULT CHARSET = latin1 PACK_KEYS =0 CHECKSUM =1;
Query OK, 0 rows affected (0.04 sec)

mysql> DROP TABLE IF EXISTS `gpt-test`.`test_table`;
Query OK, 0 rows affected (0.00 sec)

So far, this is replicated without problems. However, if you sever the connection after creating the table:

mysql> CREATE TABLE `test_table` ( `index` int( 11 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `index` ) ) ENGINE = MYISAM DEFAULT CHARSET = latin1 PACK_KEYS =0 CHECKSUM =1;
Query OK, 0 rows affected (0.02 sec)

mysql> Bye

and try dropping again:

scripts@kimiko:~$ mysql -u wp_upd -p....
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 208407
Server version: 5.0.32-Debian_7etch1-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `gpt-test`.`test_table`;                                                                    Query OK, 0 rows affected (0.00 sec)

This final DROP TABLE is not replicated.

Other commands like CREATE and RENAME seem to be affected as well; without a USE TABLE beforehand using the complete `database`.`table` syntax the command does not seem to be replicated.

Thank you for the report.

Several replication bugs were fixed since version 5.0.32. Please upgrade to current 5.0.40 or 5.0.41 version, try it and say us result.

Additionally if you can repeat the problem provide configuration files for both master and slave.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

The bug seems to be fixed in version 5.0.41

Thank you for the feedback.

I'll close the report as "Can't repeat"

I`ve still the same problem with mysql-5.0.67-solaris10-x86_64 and it`s always reproduceable.

Please look here, I think it will explain the problem:

http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous...