Bug #69143 Create table replication does not obey replicate-rewrite-db
Submitted: 4 May 2013 5:36 Modified: 7 May 2013 19:35
Reporter: Lucian Kafka Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.66 OS:Linux (mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (i486) using readline 6.1)
Assigned to: CPU Architecture:Any
Tags: CREATE TABLE, replicate-rewrite-db, replication

[4 May 2013 5:36] Lucian Kafka
Description:
replicate-rewrite-db does not seem to get applied to 'create table' statements. The statements get executed within the scope of the original database name rather than the rewritten name.

How to repeat:
server1: master
replicated database: test

server2: slave
in server 2 config: replicate-rewrite-db="test->mirror_test"

* a database named 'test' (different than the one intended for replication) also exists on the slave (which is why we need the rewrite above).

*Run on server1 in db test:

CREATE TABLE `test`.`test_broken_replication` (
`id` INT NOT NULL
) ENGINE = MYISAM ;

This creates the new table 'test_broken_replication' on the slave in database 'test' instead of 'mirror_test'.
[4 May 2013 5:39] Lucian Kafka
A consequence of this is that the replication breaks and the slave stops as soon as we insert any record in the newly created table on the master.
[4 May 2013 7:19] MySQL Verification Team
The manual says: "Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master."

I suppose you should try testing like this? 
----
USE `test`;
CREATE TABLE `test_broken_replication` (
`id` INT NOT NULL
) ENGINE = MYISAM ;
----
[4 May 2013 7:46] Lucian Kafka
The create operation was initiated from phpmyadmin. I understand your manual quote, however, it may make sense for the full scope to work as well.
[4 May 2013 8:56] Lucian Kafka
Actually re: previous comments - all queries work without explicit use of select - ie. INSERT databasename.table is fine. It is just the CREATE function that fails. In a complex replication/application it is not practical to 'USE' each database being used in a single connection.
[7 May 2013 19:35] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replic...:

--replicate-rewrite-db=from_name->to_name
...
Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name is the default database on the master. This does not work for cross-database updates.