MySQL Bugs: #10830: Lost of binlog-record during update from not pointed at binlog-do-db database

Bug #10830	Lost of binlog-record during update from not pointed at binlog-do-db database
Submitted:	24 May 2005 15:22	Modified:	26 May 2005 13:22
Reporter:	Oleg Cherniy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.1.12-standard-log	OS:	Linux (Linux i686)
Assigned to:		CPU Architecture:	Any

Description:
While trying to setup the database replication the database integrity violate. 
Qaries like:

UPDATE [binlog-do-db pointed DB].table SET foo=...

do not replicate if they are executed from the not pointed at binlog-do-db database.

How to repeat:
In my.cnf:

[mysqld]
server-id=1
default-character-set=cp1251
log-bin=/var/local/mysql/replication
binlog-do-db=common

See this example: 

USE common;
UPDATE cards SET `emissionNumber`= 1 WHERE `cardCode`= 2345;

All Ok, query put to binlog.

USE mysql;
UPDATE common.cards SET `emissionNumber`= 1 WHERE `cardCode`= 2345;

This query is not being written in binlog :(

Suggested fix:
Insert in my.cnf line binlog-do-db=[DB from which the queries  are executed ]

or:

use [binlog-do-db];
query;
use [go back to not pointed at binlog-do-db database];

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

From the MySQL manual:

--binlog-do-db=db_name 

Tells the master that it should log updates to the binary log if the current database (that is, the one selected by USE) is db_name. All other databases that are not explicitly mentioned are ignored. If you use this, you should ensure that you only do updates in the current database. 

Observe that there is an exception to the CREATE/ALTER/DROP DATABASE statements, which use the database manipulated to decide if it should log the statement rather than the current database. 

An example of what does not work as you might expect: If the server is started with binlog-do-db=sales, and you do USE prices; UPDATE sales.january SET amount=amount+1000;, this statement does not get written into the binary log.