MySQL Bugs: #24883: Replication stalls when inserting to fully qualified table names on other db

Bug #24883	Replication stalls when inserting to fully qualified table names on other db
Submitted:	7 Dec 2006 12:55	Modified:	7 Dec 2006 20:42
Reporter:	Daniel Fiske	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

Description:
This more about the binary logs, but....

If a master is set to only binary log certain databases using the binlog_do_db parameter, it still logs queries that are run like this.

use db001;
insert into db002.some_table VALUES(1);

even if binlog_do_db is only set to db001.

This then can stall replication because the logged insert to db002 is sent to slaves that may not have db002.

How to repeat:
1. Set up standard master-slave configuration
2. Add the line binlog_do_db = db001
3. Restart master
4. On the master run the following.

create database db001;
use db001;

CREATE TABLE 
`tbl_bug_test` (   
`dbl_bug_test` double(12,4) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create database db002;
use db002;

CREATE TABLE 
`tbl_bug_test` (   
`dbl_bug_test` double(12,4) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

use db001;
INSERT INTO db002.tbl_bug_test VALUES(1);

5. On the slave type

SHOW SLAVE STATUS;

....you should see that it has stalled because 

Error 'Table 'db002.tbl_bug_test' doesn't exist' on query. Default database: 'db001'. Query: 'INSERT INTO db002.tbl_bug_test VALUES(1)'

Suggested fix:
Personally I don't think it should have logged the insert because it use an incorrect table that shouldn't be logged. However this could all be by design?

According to manual it is expected behavior.

Below is quote from http://dev.mysql.com/doc/refman/5.0/en/binary-log.html:

 The server evaluates the options for logging or ignoring updates to the binary log according to the following rules. As described previously, there is an exception for the CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements. In those cases, the database being created, altered, or dropped  replaces the default database in the following rules:

   1. Are there --binlog-do-db or --binlog-ignore-db rules?
          * No: Write the statement to the binary log and exit.
          * Yes: Go to the next step.
   2. There are some rules (--binlog-do-db, --binlog-ignore-db, or both). Is there a default database (has any database been selected by USE?)?
          * No: Do not write the statement, and exit.
          * Yes: Go to the next step.
   3. There is a default database. Are there some --binlog-do-db rules?
          * Yes: Does the default database match any of the --binlog-do-db rules?
                o Yes: Write the statement and exit.
                o No: Do not write the statement, and exit.
          * No: Go to the next step.
   4. There are some --binlog-ignore-db rules. Does the default database match any of the --binlog-ignore-db rules?
          * Yes: Do not write the statement, and exit.
          * No: Write the query and exit.