MySQL Bugs: #9602: replicate-do-db does not check database prefix on queries

Bug #9602	replicate-do-db does not check database prefix on queries
Submitted:	4 Apr 2005 10:49	Modified:	4 Apr 2005 14:10
Reporter:	Balint Toth	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.18, for pc-linux-gnu (i686)	OS:	Linux (Debian Linux 3.0)
Assigned to:		CPU Architecture:	Any

Description:
Replication does not check if the table in the query is prefixed with a database. 

There's also a problem when you use 'drop table if exists' because the 'if' evaluates on master, and if the table only exists on the slave, the query is not replicated to the slave.

How to repeat:
configure slave as: replicate-do-db=a

on slave:
mysql> use test;
mysql> create table asd (i int unsigned not null);

on master:
mysql> use a;
mysql> create table test.asd (i int unsigned not null);
mysql> drop table test.asd;

on slave:
mysql> show slave status;

           Last_errno: 1050
           Last_error: Error 'Table 'asd' already exists' on query 'create table
test.asd (i int unsigned not null)'. Default database: 'a'

Suggested fix:
check prefix or issue a warning if replication is enabled and tables being created/updated outside the default 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:

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;