MySQL Bugs: #15054: sql_thread executing erroneous statements when using replicate-do-table

Bug #15054	sql_thread executing erroneous statements when using replicate-do-table
Submitted:	18 Nov 2005 19:09	Modified:	28 Feb 2006 12:28
Reporter:	Matthew Lord	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.0.17-20051115snapshot	OS:	Any (all)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
We have setup replication between a master and a slave where we only wish to replicate
statements for a subset of the tables in one database.  We are not using any replicate-do-table
or replicate-ignore-table statements but only a list of replicate-do-table=DB.TABLE lines.

The SQL thread appears to contine to try and run all statements!

How to repeat:
Setup a replication/slave environment.
On the master:
mysql>create database repltest;
In the master's my.cnf just use:
replicate-do-table=repltest.foo

Now execute any statement which would cause replication to break such
as dropping a DB or table which doesn't exist on the slave, again a statement
that has nothing to do with repltest let alone repltest.foo.

Suggested fix:
The SQL_THREAD should ignore any statement not pertaining to repltest.foo.

Not able to verify with the latest build of either 4.1 or 5.0, which I did on my development box with BUILD/compile-pentium-debug-max. I used gcc 3.3.4, glibc 2.3.4, kernel 2.4.26.

Tried the following addition to the already existing test rpl_replicate_do:

#cleanup

connection master;
drop table t1;
sync_slave_with_master;

# BUG#15054
connection master;
create table t9 (n int);
sync_slave_with_master;
connection slave;
--error 1146
select count(*) from t9;
connection master;
drop table t9;

sync_slave_with_master;
connection master;
create table t1 (n int);
insert into t1 values (1),(2),(3);
sync_slave_with_master;
connection slave;
select count(*) from t1;

#cleanup
connection master;
drop table t1;
sync_slave_with_master;

rpl_replicate_do runs the slave with 
--replicate-do-table=test.t1

Need more details the environment and the test case in which the bug was verified.

The problem seems to only occur when replicating from within the same instance using
replicate-same-server-id
along with the 
replicate-do-table

I was able to repeat the problem by adding replicate-same-server-id to the test and replicating
from within the same instance.

I apologize for such a poor report.

Specifically they were doing this:
replicate-same-server-id
replicate-rewrite-db=foo->bar
replicate-do-table=bar.t1
replicate-do-table=bar.t2

In doing so the bar DB would have a copy of the foo.t1 and t2 tables.

For a quick test I tried the following test case:

change master to master_host='localhost',master_user='root',master_port=9306;
start slave;
set sql_log_bin=0;
drop database if exists d2;
drop database if exists d2;
create database d1;
create database d2;
set sql_log_bin=1;

use d1;
create table t1 (n int);
insert into t1 values(1);
sleep 3;
use d2;
select * from t1;

With the following in the options file:

--replicate-same-server-id --server-id=1 --replicate-rewrite-db=d1-\>d2 --replicate-do-table=d2.t1

The updates were propagated into d2 like they were supposed to. Check to make sure the replication is actually happening at all. Note that if the server id is not set explicitly, the master will refuse connections from slaves. Also, the slave will not connect either the id was not explicitly given.

I followed with Sasha's last setup to catch  replicating of what is not specified.
Namely 
use foo; create table t3 (f3 int); insert into t3 values (3);
was not replicated to `bar' db.
Neigher similar happens when there is no foo->bar convertion.
Checking was done with 5.0.19.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".