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:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.1.12-standard-log OS:Linux (Linux i686)
Assigned to: CPU Architecture:Any

[24 May 2005 15:22] Oleg Cherniy
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:


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 ]


use [binlog-do-db];
use [go back to not pointed at binlog-do-db database];
[26 May 2005 13:22] MySQL Verification Team
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:


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.