Bug #3365 mysql replication issue with replicate-rewrite-db
Submitted: 2 Apr 2004 5:12 Modified: 2 Apr 2004 12:42
Reporter: Jacques Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.0.18 OS:
Assigned to: Guilhem Bichot CPU Architecture:Any

[2 Apr 2004 5:12] Jacques 
Description:
Using the replicate-rewrite-db option in /etc/my.cnf and running "LOAD DATA FROM MASTER;" to replicate all the tables ignores the replicate-rewrite-db table in question, even though the documenatation suggests otherwise.

replicate-rewrite-db = maildb->maildb_leopard
replicate-do-db = maildb_leopard

How to repeat:
Edit /etc/my.cnf and add the two replicate-* lines below on another slave and type "LOAD DATA FROM MASTER;" to replicate all the tables ignores the replicate-rewrite-db table in question, even though the documenatation suggests otherwise.

replicate-rewrite-db = maildb->maildb_leopard
replicate-do-db = maildb_leopard
[2 Apr 2004 7:23] Jacques 
I'm noticing that this bug causes replication to stop when it encounters a INSERT or UPDATE to any table which is under the rewritten database name.

Trying the "start slave;" and checking the output shows that the slaves not restarting, and the only way to restart it is first to reload all the data using "load data from master;" and then type "start slave;".
[2 Apr 2004 7:24] Dean Ellis 
Verified, and thank you.
[2 Apr 2004 12:42] Guilhem Bichot 
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Thank you Jacques for your bug report.
In fact, LOAD DATA FROM MASTER cannot take replicate-rewrite-db into account because this case can happen: 
one user could, with this option, set up a non unique
mapping like this: replicate-rewrite-db=db1->db3 and
replicate-rewrite-db=db2->db3, which would confuse the slave when it
loads the master's tables (imagine there exists db1.tbl and db2.tbl on the master: it would create on the slave db3.tbl (a copy of db1.tbl), then this db3.tbl would be overwritten by the copy of db2.tbl.
You are right, the manual was confusing about this; I have now documented this limitation in the manual, to make it clearer.

And thank you for using MySQL!!

Guilhem