Bug #47854 Replication not working when using database.table in statements
Submitted: 6 Oct 2009 9:52 Modified: 6 Oct 2009 20:42
Reporter: Kasper Sor Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5 OS:Linux (Debian 5)
Assigned to: CPU Architecture:Any

[6 Oct 2009 9:52] Kasper Sor
Description:
If I have a Master server running MySQL and have a Slave server running MySQL replicating the Master server, then if I issue the commands

use a;
INSERT INTO b VALUES('c');
then this is replicated like expected.

But if I do:

INSERT INTO a.b VALUES('c');
then nothing is replication!

This seems very strange and therefore I have reported it as a bug. I could not find the bug anywhere else but Im not the best searcher.

There is even a website that explains better than what I can do:
http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous...

I do not understand why this is the case!

How to repeat:
Install MySQL-server on two computers.

Set up a Master:
Give it server-id = 1
binlog-do-db = a

Set up a slave to replicate the master

Create a table b on the master
CREATE TABLE B
(
  HEY VARCHAR(20)
);

Issue the command
INSERT INTO a.b VALUES('c');

Check that the slave havent got any replication.

Suggested fix:
Look at what is being changed instead of where a connection is currently.
[6 Oct 2009 10:30] Valeriy Kravchuk
This behavior of binlog-do-db is clearly explained in the manual, http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_b.... 

If you use 5.1.x, row-based logging may help. Ohterwise just log everything on master and use replicate-wild-do-table (http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replic...) on slave(s).

This is not a bug, but intended and documented behavior.
[6 Oct 2009 20:42] Kasper Sor
Im sorry to have wasted your time then. :(