MySQL Bugs: #14396: replication: insert data from one db to another db not existing on the slave

Bug #14396	replication: insert data from one db to another db not existing on the slave
Submitted:	27 Oct 2005 14:22	Modified:	29 Oct 2005 7:43
Reporter:	Armin Lorenz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.15 5.0.15	OS:	W2000 Server
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
Master 4.1.7
Slave 4.1.15 und 5.0.15

DB ipc_tlm_backup only exists on the master

Slave has only this DB "replicate-do-db=ipc_tlm" in the ini.

051027 16:00:57 [ERROR] Slave: Error 'Unknown database 'ipc_tlm_backup'' on query. Default database: 'ipc_tlm'. Query: 'create table ipc_tlm_backup.notused_tblstatistic3 select * from ipc_tlm.tblstatistic where sreason = 7 and toolsid < 0', Error_code: 1049

How to repeat:
Create a not replicated db only on the master. Create a table in this database with data from a table in a replicated db.

Suggested fix:
Filter by target db.

Description:
Master 4.1.7
Slave 4.1.15 und 5.0.15

Why is the master not updated to at least 4.1.15?

Its a industrial production 24/7 solution. Did not find any fixed error in the following important to me. And so never touch a running system. Please correct me if i oversee the problem described in this report. Slave was easier to upgrade.

For "a industrial production 24/7 solution." it usually best to keep all version at the same rev level. 

Thanks
JBM

Ok. This is your opinion. But does this belong to the bug?

I dont want to be rough. But there are several reasons for not upgrading the master without need. The first time the problem showed up both servers are on the same old version. I tried out the 4.1.15 on the slave because i can shutdown this server because its only load balance. But the problem did remain. I cannot shutdown the master so easily.

Hello Armin,
 
What you experience is the expected and documented behaviour of replicate-do-db (so I'm closing this report as "not a bug").
Slave has "replicate-do-db=ipc_tlm" so any master's binlog event which has ipc_tlm as its *default* database will be executed. This is the case of this event:
051027 16:00:57 [ERROR] Slave: Error 'Unknown database 'ipc_tlm_backup'' on
query. Default database: 'ipc_tlm'. Query: 'create table
ipc_tlm_backup.notused_tblstatistic3 select * from ipc_tlm.tblstatistic where
sreason = 7 and toolsid < 0', Error_code: 1049
It's as documented here: http://dev.mysql.com/doc/refman/4.1/en/binary-log.html
Two solutions:
1) do a "USE ipc_tlm_backup" on your master's connection before doing the CREATE TABLE, this way the default db will be ipc_tlm_backup and so the event will be ignored by the slave
or
2) don't use replicate-do-db and use replicate-wild-do-table=ipc_tlm.%