Bug #13528 Replication fails to replicate some changes
Submitted: 27 Sep 2005 15:13 Modified: 27 Sep 2005 17:38
Reporter: Tyler Hutcheon Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:Ver 14.7 Distrib 4.1.10 OS:FreeBSD (FreeBSD 5.3)
Assigned to: MySQL Verification Team CPU Architecture:Any

[27 Sep 2005 15:13] Tyler Hutcheon
Description:
If you are using mysql client, change to a non-replicated database on the master, and try to change a table in the replicated database, the changes you make will not be replicated.

How to repeat:
Create DB1, DB2 on Server1, replicate DB1 to Server2.
Start up a mysql client on Server1, run:

use DB2;
delete from DB1.table1;

These changes will not be replicated to Server2.
[27 Sep 2005 15:55] Tyler Hutcheon
Also, when you are using a replicated database, and perform operations on non-replicated databases, it writes the commands to the binary log, the clients cannot run them, and it stops the replication clients.

use DB1;
delete from DB2.table;

The clients will stop working with error:
Error number: 1146 
Error description: Error 'Table 'DB2.table' doesn't exist' on query. Default database: 'DB1'. Query: 'truncate table DB2.table'
[27 Sep 2005 16:22] MySQL Verification Team
The below from our Manual, can be apply what are you reporting:

--replicate-do-db=db_name 

Tells the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database. If you need cross-database updates to work, make sure that you have MySQL 3.23.28 or later, and use --replicate-wild-do-table=db_name.%. Please read the notes that follow this option list. 

An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated: 

USE prices;
UPDATE sales.january SET amount=amount+1000;
[27 Sep 2005 17:16] Tyler Hutcheon
I have that option set in my my.cnf file.  It is being read by mysql, as it shows up in the master's status:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| l1-02-bin.000014 |      150 | mail         |                  |
+------------------+----------+--------------+------------------+
[27 Sep 2005 17:38] MySQL Verification Team
Then is not a bug as explained in the Manual.