MySQL Bugs: #17708: replication rules fail if table missing on slave

Bug #17708	replication rules fail if table missing on slave
Submitted:	24 Feb 2006 18:31	Modified:	2 May 2006 13:45
Reporter:	Bernd Ocklin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18-max	OS:	Linux (Linux 2.6.9 i686 RHE4)
Assigned to:		CPU Architecture:	Any

Description:
Replicataion ignore rules fail under certain conditions if the table to be ignored is missing on the master. This is true for replicate-wild-ignore-table & for replicate-ignore-table filter options. 

Simple standard update / insert statements involving only one table seem to work: e.g. like update <table-name> set <col> where ... works.

update <table1>, <table2> set <table1.col> ... where ... doesn't work.

How to repeat:
Have a master and a slave. Both have a database db1.

On master:
server-id=7
binlog-do-db = db1

On slave:
server-id=9
replicate-do-table = db1.real

replicate-ignore-table= db1.test1
replicate-ignore-table= db1.test2
or
replicate-wild-ignore-table= db1.%

Create two tables on the master in database db1.

create table test1 (i int, name char(1));
create table test2 (i int, name char(1));
create table real (i int, name char(1));

Do *only* create table real on the slave in db1. Start the slave.
create table real (i int, name char(1));

Now start the test.

insert into test1 (1, 'V'), (2, 'X'); [OK, not replicated]
insert into test2 (1, 'V'), (2, 'X'); [OK, not replicated]

update test, test2 set test.name = 'V' where test.i = test2.i; [FAILED, see error message]

60224 18:07:25 [ERROR] Slave: Error 'Table 'db1.test1' doesn't exist' on query. Default database: 'db1'. Query: 'update test1, test2 set test.name = 'V' where test1.i = test2.i', Error_code: 1146

Suggested fix:
Implement behavior for replicate-[wild]-ignore-table for all statements.

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour you described with 5.0.21-BK version on slave. In error log I've got only:

060402 11:59:03 [Note] Slave I/O thread: connected to master 'repl@127.0.0.1:334
0',  replication started in log 'mysql-bin.010' at position 304
060402 11:59:03 [Note] Slave SQL thread initialized, starting replication in log
 'mysql-bin.010' at position 304, relay log './suse-relay-bin.000001' position:
4

while performing both inserts and update - they are all ignored. My configuration was the following:

openxs@suse:~/dbs/5.0> cat my.cnf
[mysqld]
server-id=9
replicate-do-table = test.real1

replicate-ignore-table= test.test1
replicate-ignore-table= test.test2

#character-set-connection=latin1
#collation-connection=latin1_german1_ci
openxs@suse:~/dbs/5.0>

I was forced to change table name from real to real1, but otherwise my test was similar to those you described. So, please, try ot repeat with a newer version, 5.0.19, and inform about the results.

Cool, tested it with 5.0.20 and the error doesn't occur anymore. 

Obviously: bug was fixed. Thanks.

Thank you for the feedback.