Bug #37051 Replication rules not evaluated correctly.
Submitted: 28 May 2008 22:48 Modified: 9 Mar 2009 15:55
Reporter: Matthew Montgomery Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.56sp1 OS:Any
Assigned to: Zhenxing He CPU Architecture:Any
Tags: replication
Triage: Needs Triage: D2 (Serious) / R2 (Low) / E2 (Low)

[28 May 2008 22:48] Matthew Montgomery
Description:
Replication rules regarding --replicate-*-table options states that "Only tables that are to be updated are compared to the options."  

However, in  5.0 tables that are not updated by the query but are JOINed to the updated table are matched and raise an error on the slave breaking replication.

How to repeat:
Configure replication.
Set up slave server with --replicate-do-table=test.t1;

master> use test;
Database changed
master> CREATE TABLE t1 (id int); 
Query OK, 0 rows affected (0.01 sec)

master> CREATE TABLE t2 (id int, t1id int);
Query OK, 0 rows affected (0.01 sec)

slave> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
...
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: test.t1
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
...
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

slave> show tables; 
+----------------+
| Tables_in_test |
+----------------+
| t1             | 
+----------------+
1 row in set (0.00 sec)

master> update t2 LEFT JOIN t1 ON (t1.id = t2.t1id) set t2.t1id = 1 where t1.id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

slave>  show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
...
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: test.t1
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1146
                 Last_Error: Error 'Table 'test.t2' doesn't exist' on query. Default database: 'test'. Query: 'update t2 LEFT JOIN t1 ON (t1.id = t2.t1id) set t2.t1id = 1 where t1.id = 1'
               Skip_Counter: 0
...
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

Suggested fix:
.
[28 May 2008 23:07] Matthew Montgomery
Tested on 4.1.22-max and confirmed proper behavior in 4.1.
[29 May 2008 6:52] Kenny Gryp
This is working on MySQL 4.1.22
[3 Jun 2008 21:45] Matthew Montgomery
Partial workaround is to explicitly add --replicate-ignore-table=t2.

This is not workable for dbs with large numbers of tables.
[3 Jul 2008 10:29] Sven Sandberg
The problem is not in replication code, it is in parser code. I added debug traces like this:

=== modified file 'sql/rpl_filter.cc'
--- sql/rpl_filter.cc	2007-08-13 13:11:25 +0000
+++ sql/rpl_filter.cc	2008-07-02 09:26:17 +0000
@@ -88,6 +88,11 @@
 {
   bool some_tables_updating= 0;
   DBUG_ENTER("Rpl_filter::tables_ok");
+
+  for (TABLE_LIST *t = tables; t; t = t->next_global)
+  {
+    DBUG_PRINT("sven", ("table_name=%s updating=%d", t->table_name, t->updating));
+  }
   
   for (; tables; tables= tables->next_global)
   {

Then I ran this test (the same as Matthew did above):

======== BEGIN rpl_bug37051.test ========
source include/master-slave.inc;
source include/have_binlog_format_statement.inc;

CREATE TABLE t1 (id int); 
CREATE TABLE t2 (id int, t1id int);

sync_slave_with_master;
query_vertical SHOW SLAVE STATUS;
SHOW TABLES;

connection master;
UPDATE t2 LEFT JOIN t1 ON (t1.id = t2.t1id) SET t2.t1id = 1 WHERE t1.id = 1;

sync_slave_with_master;
query_vertical SHOW SLAVE STATUS;
======== end rpl_bug37051.test ========

As above, I got this:

Last_Error	Error 'Table 'test.t2' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t2 LEFT JOIN t1 ON (t1.id = t2.t1id) SET t2.t1id = 1 WHERE t1.id = 1'

The relevant part of var/log/master.trace is:

T@6    : | | | | | >Rpl_filter::tables_ok
T@6    : | | | | | | sven: table_name=t2 updating=1
T@6    : | | | | | | sven: table_name=t1 updating=1
T@6    : | | | | | | >hash_first

So table->updating was set to 1 for table t1 by the query "UPDATE t2 LEFT JOIN t1 ON (t1.id = t2.t1id) SET t2.t1id = 1 WHERE t1.id = 1".
[3 Jul 2008 10:41] Sven Sandberg
With the test file above, I used this slave option file:

======== BEGIN rpl_bug37051-slave.opt ========
--replicate-do-table=test.t1
======== END rpl_bug37051-slave.opt ========
[5 Jul 2008 20:04] Sven Sandberg
See also BUG#37881
[24 Jul 2008 5:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50372

2630 He Zhenxing	2008-07-24
      BUG#37051 Replication rules not evaluated correctly
      
      Replication filter rules are applied on the tables list for update, 
      however, in order to get the list of update tables, all tables need
      to be opened to resolve update fields, which would result in a failure
      if some of the tables involved in the multi-update statement are not
      exist on slave.
      
      This patch fixes this problem by ignoring tables not exist on slave
      and continue the process of resolving fields with the tables exist
      and apply the filter rules upon the tables exist on slave and will be
      updated by the multi-update statement.
[25 Jul 2008 8:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50476

2632 He Zhenxing	2008-07-25
      BUG#37051 Replication rules not evaluated correctly
      
      The problem of this bug is that we need to get the list of tables
      to be updated for a multi-update statement, which requires to
      open all the tables referenced by the statement and resolve all
      the fields involved in update in order to figure out the list of
      tables for update. However if there are replicate filter rules,
      some tables might not exist on slave and result in a failure
      before we could examine the filter rules.
      
      I think the whole problem can not be solved on slave alone,
      the master must record and send the information of tables
      involved for update to slave, so that the slave do not need to
      open all the tables referencec by the multi-update statement to
      figure out which tables are involved for udpate.
      
      So I added an internal user variable to store the
      value of table map for update on master and written it to binlog
      before the multi-update query log event. And on slave, it will
      try to get the value of this variable and use it to examine
      filter rules without opening any tables on slave.
[25 Jul 2008 8:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50477

2632 He Zhenxing	2008-07-25
      BUG#37051 Replication rules not evaluated correctly
      
      The problem of this bug is that we need to get the list of tables
      to be updated for a multi-update statement, which requires to
      open all the tables referenced by the statement and resolve all
      the fields involved in update in order to figure out the list of
      tables for update. However if there are replicate filter rules,
      some tables might not exist on slave and result in a failure
      before we could examine the filter rules.
      
      I think the whole problem can not be solved on slave alone,
      the master must record and send the information of tables
      involved for update to slave, so that the slave do not need to
      open all the tables referencec by the multi-update statement to
      figure out which tables are involved for udpate.
      
      So I added an internal user variable to store the
      value of table map for update on master and written it to binlog
      before the multi-update query log event. And on slave, it will
      try to get the value of this variable and use it to examine
      filter rules without opening any tables on slave.
[30 Jul 2008 9:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50695

2632 He Zhenxing	2008-07-30
      BUG#37051 Replication rules not evaluated correctly
      
      The problem of this bug is that we need to get the list of tables
      to be updated for a multi-update statement, which requires to
      open all the tables referenced by the statement and resolve all
      the fields involved in update in order to figure out the list of
      tables for update. However if there are replicate filter rules,
      some tables might not exist on slave and result in a failure
      before we could examine the filter rules.
      
      I think the whole problem can not be solved on slave alone,
      the master must record and send the information of tables
      involved for update to slave, so that the slave do not need to
      open all the tables referencec by the multi-update statement to
      figure out which tables are involved for udpate.
      
      So a status variable is added to Query_log event to store the
      value of table map for update on master. And on slave, it will
      try to get the value of this variable and use it to examine
      filter rules without opening any tables on slav, if this values
      is not available, the old approach is used and thus the bug will
      still occur for when replicating from old masters.
[30 Jul 2008 9:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50696

2632 He Zhenxing	2008-07-30
      BUG#37051 Replication rules not evaluated correctly
      
      The problem of this bug is that we need to get the list of tables
      to be updated for a multi-update statement, which requires to
      open all the tables referenced by the statement and resolve all
      the fields involved in update in order to figure out the list of
      tables for update. However if there are replicate filter rules,
      some tables might not exist on slave and result in a failure
      before we could examine the filter rules.
      
      I think the whole problem can not be solved on slave alone,
      the master must record and send the information of tables
      involved for update to slave, so that the slave do not need to
      open all the tables referencec by the multi-update statement to
      figure out which tables are involved for udpate.
      
      So a status variable is added to Query_log event to store the
      value of table map for update on master. And on slave, it will
      try to get the value of this variable and use it to examine
      filter rules without opening any tables on slav, if this values
      is not available, the old approach is used and thus the bug will
      still occur for when replicating from old masters.
[30 Jul 2008 14:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50722

2632 He Zhenxing	2008-07-30
      BUG#37051 Replication rules not evaluated correctly
      
      The problem of this bug is that we need to get the list of tables
      to be updated for a multi-table update statement, which requires to
      open all the tables referenced by the statement and resolve all
      the fields involved in update in order to figure out the list of
      tables for update. However if there are replicate filter rules,
      some tables might not exist on slave and result in a failure
      before we could examine the filter rules.
      
      I think the whole problem can not be solved on slave alone,
      the master must record and send the information of tables
      involved for update to slave, so that the slave do not need to
      open all the tables referenced by the multi-table update statement to
      figure out which tables are involved for update.
      
      So a status variable is added to Query_log event to store the
      value of table map for update on master. And on slave, it will
      try to get the value of this variable and use it to examine
      filter rules without opening any tables on slave, if this values
      is not available, the old approach is used and thus the bug will
      still occur for when replicating from old masters.
[31 Jul 2008 6:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50759

2632 He Zhenxing	2008-07-31
      BUG#37051 Replication rules not evaluated correctly
      
      The problem of this bug is that we need to get the list of tables
      to be updated for a multi-table update statement, which requires to
      open all the tables referenced by the statement and resolve all
      the fields involved in update in order to figure out the list of
      tables for update. However if there are replicate filter rules,
      some tables might not exist on slave and result in a failure
      before we could examine the filter rules.
      
      I think the whole problem can not be solved on slave alone,
      the master must record and send the information of tables
      involved for update to slave, so that the slave do not need to
      open all the tables referenced by the multi-table update statement to
      figure out which tables are involved for update.
      
      So a status variable is added to Query_log event to store the
      value of table map for update on master. And on slave, it will
      try to get the value of this variable and use it to examine
      filter rules without opening any tables on slave, if this values
      is not available, the old approach is used and thus the bug will
      still occur for when replicating from old masters.
[1 Aug 2008 9:34] Lars Thalmann
Have sent some questions to Zhenxing.  Setting to "in review" until
they are answered.
[1 Aug 2008 9:58] Lars Thalmann
My worries unfounded.  Setting back to patch approved.
[7 Aug 2008 3:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51064

2696 He Zhenxing	2008-08-07
      Merge post-fix of BUG#37051
[7 Aug 2008 3:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51065

2635 He Zhenxing	2008-08-07
      post push fix for BUG#37051
[7 Aug 2008 4:22] Zhenxing He
pushed to 5.1-rpl and 6.0-rpl
[26 Aug 2008 10:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52529

2718 He Zhenxing	2008-08-26
      Cherry picking patch for BUG#37051
[26 Aug 2008 10:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52535

2719 He Zhenxing	2008-08-26
      Cherry picking post fixes for BUG#37051
[26 Aug 2008 12:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52552

2720 He Zhenxing	2008-08-26
      Fix cherry picking patch of BUG#37051
[26 Aug 2008 12:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52553

2676 He Zhenxing	2008-08-26
      Merge 5.1-rpl-merge -> 6.0-rpl-merge: cherry picking patch of BUG#37051
[27 Aug 2008 3:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52638

2720 He Zhenxing	2008-08-26
      Fix cherry picking patch of BUG#37051
[28 Aug 2008 9:05] Bugs System
Pushed into 5.1.28  (revid:hezx@mysql.com-20080826121156-v35ovgra38bm79o2) (version source revid:kgeorge@mysql.com-20080828082606-fgzd1k01lux96jf4) (pib:3)
[28 Aug 2008 13:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52846

2677 Georgi Kodinov	2008-08-28
      on behalf of jasonh : fix of a test problem re bug#37051: 
      Wait for slave to stop before set skip counter
[28 Aug 2008 13:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52848

2677 Georgi Kodinov	2008-08-28
      on behalf of jasonh : fix of a test problem re bug#37051: 
      Wait for slave to stop before set skip counter
[2 Sep 2008 15:08] Jon Stephens
Documented in the 5.1 .27 changelog as follows:

        The --replicate-*-table options were not evaluated correctly when 
        replicating multi-table updates. 

        As a result of this fix, replication of multi-table updates no longer
        fails when an update references a missing table but does not update any
        of its columns.
[13 Sep 2008 23:33] Bugs System
Pushed into 6.0.7-alpha  (revid:hezx@mysql.com-20080826100756-ulszt6d5zl6olqoj) (version source revid:john.embretsen@sun.com-20080808091208-ht48kyzsk7rim74g) (pib:3)
[28 Oct 2008 14:08] Sebastian Nohn
Is there a chance to get this fix in 5.0?
[31 Oct 2008 17:18] Bugs System
Pushed into 6.0.8-alpha  (revid:kgeorge@mysql.com-20080828131606-n52u3a174nhaqpfv) (version source revid:joerg@mysql.com-20081017153936-8br5l203muwma9at) (pib:5)
[4 Nov 2008 17:43] Bugs System
Pushed into 5.1.30  (revid:kgeorge@mysql.com-20080828131606-n52u3a174nhaqpfv) (version source revid:mysqldev@production.mysql.com-20080911123935-36bxe9pm1cuo0vbp) (pib:5)
[30 Jan 2009 13:28] Bugs System
Pushed into 6.0.10-alpha (revid:luis.soares@sun.com-20090129165607-wiskabxm948yx463) (version source revid:luis.soares@sun.com-20090129163120-e2ntks4wgpqde6zt) (merge vers: 6.0.10-alpha) (pib:6)
[30 Jan 2009 15:08] Bugs System
Pushed into 5.1.32 (revid:luis.soares@sun.com-20090129165946-d6jnnfqfokuzr09y) (version source revid:msvensson@mysql.com-20080808181043-2bgvk6qhm6p4sgnb) (merge vers: 5.1.28) (pib:6)
[17 Feb 2009 15:00] Bugs System
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 16:47] Bugs System
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 18:23] Bugs System
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090201210519-vehobc4sy3g9s38e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)
[4 Mar 2009 6:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68171

2742 He Zhenxing	2009-03-04
      BUG#37051 Replication rules not evaluated correctly
      
      Backporting patch to 5.0.
[4 Mar 2009 7:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68176

2742 He Zhenxing	2009-03-04
      BUG#37051 Replication rules not evaluated correctly
      
      Backporting patch to 5.0.
[4 Mar 2009 8:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68177

2832 He Zhenxing	2009-03-04
      Null merge backporting of BUG#37051
[4 Mar 2009 9:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68181

2832 He Zhenxing	2009-03-04 [merge]
      Null merge 5.0 -> 5.1, backporting patch of BUG#37051
[5 Mar 2009 10:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68335

2766 He Zhenxing	2009-03-05
      BUG#37051 Replication rules not evaluated correctly
      
      Backporting patch to 5.0.
[5 Mar 2009 10:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68336

2832 He Zhenxing	2009-03-05 [merge]
      Null merge backporting patch of BUG#37051 from 5.0-bugteam to 5.1-bugteam
[5 Mar 2009 10:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68337

3101 He Zhenxing	2009-03-05 [merge]
      Auto merge from 5.1-bugteam
[6 Mar 2009 9:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68458

2767 He Zhenxing	2009-03-06 [merge]
      Auto merge
[9 Mar 2009 14:13] Bugs System
Pushed into 5.0.79 (revid:joro@sun.com-20090309135922-a0di9ebkxoj4d4wv) (version source revid:zhenxing.he@sun.com-20090306093447-ydd6hokrsqw0xjfq) (merge vers: 5.0.79) (pib:6)
[9 Mar 2009 15:55] Jon Stephens
Fix now also documented in the 5.0.79 and 6.0.7 changelogs. Closed.
[13 Mar 2009 19:03] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:zhou.li@sun.com-20090311061050-ihp0g77znonq1tuq) (merge vers: 5.1.33) (pib:6)
[18 Mar 2009 13:18] Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:matthias.leich@sun.com-20090310140952-gwtoq87wykhji3zi) (merge vers: 6.0.11-alpha) (pib:6)
[9 May 2009 16:40] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:37] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:35] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)