Bug #3989 | multi-table update will broke replication if replicate-do-table is in use | ||
---|---|---|---|
Submitted: | 3 Jun 2004 20:19 | Modified: | 28 Nov 2008 20:02 |
Reporter: | Sergey Kostyliov | Email Updates: | |
Status: | Won't fix | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
Version: | 5.0 BK, 5.1 BK | OS: | Linux (Linux (Gentoo 2004.1)) |
Assigned to: | Mats Kindahl | CPU Architecture: | Any |
[3 Jun 2004 20:19]
Sergey Kostyliov
[17 Jun 2004 11:14]
Guilhem Bichot
Need to get colleagues' feedback on #4118 before I can choose how to fix this one.
[30 Jul 2004 21:18]
Guilhem Bichot
Will fix it once Monty has fixed BUG#4118 (which is almost the same bug, as the same small function is guilty).
[26 Oct 2004 6:52]
Sergey Kostyliov
#4118 has already fixed. And I've just checked that I could not reproduce the problem described with mysql-4.1.6. Thank you!
[6 Feb 2007 11:47]
Sveta Smirnova
It is repeatable with current 5.0 and 5.1 BK sources. See also bug #26109
[8 Jan 2008 19:16]
MySQL Verification Team
Affects only SBR not RBR in 5.1
[9 Jan 2008 17:15]
MySQL Verification Team
Slight variation of this test case under 4.1.21 to update table `b` : (On Master) DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; CREATE TABLE a (l int, m int, n int); INSERT INTO a VALUES (1,1,1); INSERT INTO a VALUES (2,2,2); CREATE TABLE b (l int, m int, o int); INSERT INTO b VALUES (1,1,1); INSERT INTO b VALUES (2,2,2); SELECT * FROM a; +------+------+------+ | l | m | n | +------+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | +------+------+------+ 2 rows in set (0.00 sec) select * from b; +------+------+------+ | l | m | o | +------+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | +------+------+------+ 2 rows in set (0.00 sec) UPDATE a,b set o=5 WHERE a.l=b.l and n=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM b; +------+------+------+ | l | m | o | +------+------+------+ | 1 | 1 | 1 | | 2 | 2 | 5 | +------+------+------+ 2 rows in set (0.00 sec) (On Slave) mysql> 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: test Replicate_Ignore_DB: Replicate_Do_Table: test.b ... Last_Errno: 0 Last_Error: ... mysql> select * from b; +------+------+------+ | l | m | o | +------+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | +------+------+------+ 2 rows in set (0.00 sec) Replication continues but update to `b` is ignored. This is not "fixed" in 4.1.x but rather ignored. This permits slave inconsistency. I.e. The 4.1 behavior is that if all tables required for a multi-table update are not present on the slave in SBR mysqld ignores the update where the 5.x behavior is such that if all tables required for a multi-table update are not present on the slave in SBR replication fails with an error. Probably this is "Not a bug". You should include all tables a & b on the slave when using SBR while RBR permits table b only on the slave. If you prefer the 4.1 behavior you can use: --slave-skip-errors=1146 (Error 'Table '%s' doesn't exist') but must be aware of the possibility for slave becoming inconsistent.
[4 Jul 2008 16:04]
Sveta Smirnova
See also bug #37881