MySQL Bugs: #28616: UPDATE with JOIN + replicate-ignore-table ignores more than configured

Bug #28616	UPDATE with JOIN + replicate-ignore-table ignores more than configured
Submitted:	23 May 2007 11:51	Modified:	26 Aug 2007 5:00
Reporter:	Johan Idrén	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	4.1.10a,4.1.22	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	bfsm_2007_08_16

Description:
Using replicate-ignore-table to ignore any specific table causes queries using non-existing tables on slave to be ignored.

How to repeat:
Set up replication between 2 mysql 4.1.22 hosts. Add replicate-ignore-table with some value to the slave. For example some.non_existing_table.

Execute following:

USE test;
CREATE TABLE a (id int, value int);
INSERT INTO a (id) VALUES (1),(2),(3);
SET SQL_LOG_BIN=0;
CREATE TABLE b (id int, value int);
INSERT INTO b (id) VALUES (1),(2),(3);
SET SQL_LOG_BIN=1;
UPDATE a JOIN b ON a.id=b.id SET a.value=123;

Update-query will NOT execute on slave. Slave will silently continue replicating with faulty data.
Master:
mysql> select * from a;
+------+-------+
| id   | value |
+------+-------+
|    1 |   123 | 
|    2 |   123 | 
|    3 |   123 | 
+------+-------+
3 rows in set (0.00 sec)

Slave:
mysql> select * from a;
+------+-------+
| id   | value |
+------+-------+
|    1 |  NULL | 
|    2 |  NULL | 
|    3 |  NULL | 
+------+-------+
3 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: repl
                Master_Port: 3315
              Connect_Retry: 60
            Master_Log_File: localhost-bin.000003
        Read_Master_Log_Pos: 1025
             Relay_Log_File: localhost-relay-bin.000001
              Relay_Log_Pos: 896
      Relay_Master_Log_File: localhost-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: some.non_existing_table
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 1025
            Relay_Log_Space: 896
            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: 0
1 row in set (0.00 sec)

Suggested fix:
Make it work like in 5.0, since it works just fine there.

I was mistaken. This affects 5.0 as well. Does not affect 5.1.18 though.

As we have binary logging switched off for

CREATE TABLE b (id int, value int);
INSERT INTO b (id) VALUES (1),(2),(3);

neither the table b creation nor inserts are loged 
thus we get on slave
"Error 'Table 'test.b' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE a JOIN b ON a.id=b.id SET a.value=123', Error_code: 1146"
and a.value is not updated.
Expected result.

I was a bit hasty when testing on 5.0. But this does affect 4.1.22. Removing 5.0.40 from Version field and setting back to Verified.

Attaching new testcase.

setup master & slave with settings needed to reproduce bug

Attachment: bug28616.sh (application/x-sh, text), 1.07 KiB.

sql in practical file, same as in first post

Attachment: bug28616.sql (application/octet-stream, text), 233 bytes.

In test case format:

rpl_trs-slave.opt:

--replicate-ignore-table=does.not_exist

rpl_trs.test:
source include/master-slave.inc;

connection master;

CREATE TABLE t1 (id int, value int);
INSERT INTO t1 (id) VALUES (1),(2),(3);
SET SQL_LOG_BIN=0;
CREATE TABLE t2 (id int, value int);
INSERT INTO t2 (id) VALUES (1),(2),(3);
SET SQL_LOG_BIN=1;
# Should fail on slave, and stop SQL thread
UPDATE t1 JOIN t2 ON t1.id=t2.id SET t1.value=123;
# Should not be executed on slave, because SQL thread is stopped
INSERT INTO t1 (id, value) VALUES (4, 666);

sync_slave_with_master;

# Should not contain 4th row
SELECT * FROM t1;

DROP TABLE t1;

connection master;
DROP TABLE t1;
DROP TABLE t2;

stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
CREATE TABLE t1 (id int, value int);
INSERT INTO t1 (id) VALUES (1),(2),(3);
SET SQL_LOG_BIN=0;
CREATE TABLE t2 (id int, value int);
INSERT INTO t2 (id) VALUES (1),(2),(3);
SET SQL_LOG_BIN=1;
UPDATE t1 JOIN t2 ON t1.id=t2.id SET t1.value=123;
INSERT INTO t1 (id, value) VALUES (4, 666);
SELECT * FROM t1;
id      value
1       NULL
2       NULL
3       NULL
DROP TABLE t1;
DROP TABLE t1;
DROP TABLE t2;

Actual current results include a 4th row in the select statement (4    666).

Confirmed that this bug does not affect 5.0 or 5.1.