| 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: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.56sp1 | OS: | Any |
| Assigned to: | Zhenxing He | CPU Architecture: | Any |
| Tags: | replication | ||
[28 May 2008 23:07]
MySQL Verification Team
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]
MySQL Verification Team
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)
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: .