Bug #37881 Check the table options don't work as described if replicate-do-table used
Submitted: 4 Jul 2008 15:58 Modified: 15 Jul 2008 9:05
Reporter: Sveta Smirnova Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:4.1 BZR OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[4 Jul 2008 15:58] Sveta Smirnova
There is next text at http://dev.mysql.com/doc/refman/4.1/en/replication-rules.html:

 Stage 2. Check the table options.

If the slave reaches this point, it executes all statements if there are no table options. If there are “do” table options, the statement must match one of them if it is to be executed; otherwise, it is ignored. If there are any “ignore” options, all statements are executed except those that match any ignore option. The following steps describe how this evaluation occurs in more detail.

   1. Are there any --replicate-*-table options?
          * No: There are no table restrictions, so all statements match. Execute the statement and exit.
          * Yes: There are table restrictions. Evaluate the tables to be updated against them. There might be multiple tables to update, so loop through the following steps for each table looking for a matching option (first the non-wild options, and then the wild options). Only tables that are to be updated are compared to the options. For example, if the statement is INSERT INTO sales SELECT * FROM prices, only sales is compared to the options). If several tables are to be updated (multiple-table statement), the first table that matches “do” or “ignore” wins. That is, the server checks the first table against the options. If no decision could be made, it checks the second table against the options, and so on.

But UPDATE ignores if one table is specified as argument of replicate_do_table option and other does not.

Version 5.x behaves as described.

How to repeat:
$cat rpl_bug-slave.opt:

$cat rpl_bug.test:
--source include/master-slave.inc

drop table if exists t1;
drop table if exists t2;
create table t1(id int);
create table t2(id int);
insert into t1 values (1),(2),(3),(4),(5);
insert into t2 values (1),(2),(3),(4),(5);

update t1 a join t2 b on a.id=b.id set a.id=2*a.id;

sleep 1;

connection slave;

show slave status;

Suggested fix:
Fix either version 4.1 or documentation for this version.
[4 Jul 2008 16:03] Sveta Smirnova
See also comment "[9 Jan 18:15] Matthew Montgomery" to bug #3989
[5 Jul 2008 20:04] Sven Sandberg
See also BUG#37051.
[8 Jul 2008 20:02] Jon Stephens
I'll take this one since it involves Replication docs.
[15 Jul 2008 9:05] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.