MySQL Bugs: #3989: multi-table update will broke replication if replicate-do-table is in use

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:	None
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

Description:
Multiple tables update will broke replication even if tables restricted by 
'replicate-do-table' were not updated. 

How to repeat:
The slave settings are: 
... 
replicate-do-db                 = test 
replicate-do-table              = test.b 
... 
 
rathamahata@dev tmp $ cat tc.sql 
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); 
INSERT INTO b VALUES (1,1); 
INSERT INTO b VALUES (2,2); 
 
UPDATE a,b SET n=5 WHERE a.l=b.l AND n=2; 
rathamahata@dev tmp $ 
 
After issuing `mysql < tc.sql' on master you will see on slave: 
 
mysql> SHOW SLAVE STATUS\G 
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 127.0.0.1 
                Master_User: replication 
                Master_Port: 3309 
              Connect_Retry: 60 
            Master_Log_File: self.000005 
        Read_Master_Log_Pos: 995 
             Relay_Log_File: dev-relay-bin.000004 
              Relay_Log_Pos: 996 
      Relay_Master_Log_File: self.000005 
           Slave_IO_Running: Yes 
          Slave_SQL_Running: No 
            Replicate_Do_DB: test 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: test.b 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1146 
                 Last_Error: Error 'Table 'test.a' doesn't exist' on query. 
Default database: 'test'. Query: 'UPDATE a,b SET n=5 WHERE a.l=b.l AND n=2' 
               Skip_Counter: 0 
        Exec_Master_Log_Pos: 920 
            Relay_Log_Space: 1071 
            Until_Condition: None 
             Until_Log_File: 
              Until_Log_Pos: 0 
         Master_SSL_Allowed: No 
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 300 
1 row in set (0.00 sec) 
 
mysql>

Need to get colleagues' feedback on #4118 before I can choose how to fix this one.

Will fix it once Monty has fixed BUG#4118 (which is almost the same bug, as the same small function is guilty).

#4118 has already fixed. And I've just checked that I could not reproduce the problem 
described with mysql-4.1.6. Thank you!

It is repeatable with current 5.0 and 5.1 BK sources.

See also bug #26109

Affects only SBR not RBR in 5.1

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.

See also bug #37881