MySQL Bugs: #14191: multi-table update fails to replicate using replicate-wild-do-table

Bug #14191	multi-table update fails to replicate using replicate-wild-do-table
Submitted:	20 Oct 2005 19:40	Modified:	20 Oct 2005 23:14
Reporter:	Bryan Hess	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.8 slave; 4.1.11 master	OS:	Linux (RedHat EL3 slave; Solaris 9 mast)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Multi-table updates are not replicated to the slave.

How to repeat:
To recreate the error, in database foo do the following:

create table a ( x int );
create table b ( x int );
insert into a values (1), (2);
insert into b values (2), (3);
update a join b using (x) set a.x=100;
select x from a where x=100;

Then, see that the master and slave are out of sync:

On the Master:
mysql> select x from a where x=100;
+------+
| x    |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

On Slave:

mysql> select x from a where x=100;
Empty set (0.00 sec)

Forgot to say:

database foo is replicated with the slave config containing:

replicate-wild-do-table=foo.%
replicate-wild-ignore-table=mysql.%

I was unable for to repeat with 2 servers 4.1.16 built from source:

miguel@hegel:~/dbs/4.1> bin/mysql --defaults-file=/home/miguel/dbs/4.1/var/my.cnf -uroot --prompt="master >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

master >show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| hegel-bin.000006 |       79 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)

master >use foo
Database changed
master >create table a ( x int );
Query OK, 0 rows affected (0.05 sec)

master >create table b ( x int );
Query OK, 0 rows affected (0.07 sec)

master >insert into a values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

master >insert into b values (2), (3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

master >update a join b using (x) set a.x=100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master >select x from a where x=100;
+------+
| x    |
+------+
|  100 |
+------+
1 row in set (0.01 sec)

master >
-----------------------------------------------------------------
miguel@hegel:~/dbs/4.1s> bin/mysql --defaults-file=/home/miguel/dbs/4.1s/var/my.cnf -uroot --prompt="slave >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

slave >CHANGE MASTER TO
    -> MASTER_HOST='localhost',
    -> MASTER_USER='miguel',
    -> MASTER_PASSWORD='nana',
    -> MASTER_LOG_FILE='hegel-bin.000006',
    -> MASTER_LOG_POS=79;
Query OK, 0 rows affected (0.18 sec)

slave >start slave;
Query OK, 0 rows affected (0.00 sec)

slave >show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: miguel
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: hegel-bin.000006
        Read_Master_Log_Pos: 79
             Relay_Log_File: hegel-relay-bin.000001
              Relay_Log_Pos: 47
      Relay_Master_Log_File: hegel-bin.000006
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: foo.%
Replicate_Wild_Ignore_Table: mysql.%
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 79
            Relay_Log_Space: 47
            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)

slave >use foo
Database changed
slave >select x from a where x=100;
+------+
| x    |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

slave >

Sorry I forgot to paste:

master >SHOW BINLOG EVENTS IN 'hegel-bin.000006';
+------------------+-----+------------+-----------+--------------+--------------------------------------------------+
| Log_name         | Pos | Event_type | Server_id | Orig_log_pos | Info                                             |
+------------------+-----+------------+-----------+--------------+--------------------------------------------------+
| hegel-bin.000006 |   4 | Start      |         1 |            4 | Server ver: 4.1.16-debug-log, Binlog ver: 3      |
| hegel-bin.000006 |  79 | Query      |         1 |           79 | use `foo`; create table a ( x int )              |
| hegel-bin.000006 | 137 | Query      |         1 |          137 | use `foo`; create table b ( x int )              |
| hegel-bin.000006 | 195 | Query      |         1 |          195 | use `foo`; insert into a values (1), (2)         |
| hegel-bin.000006 | 258 | Query      |         1 |          258 | use `foo`; insert into b values (2), (3)         |
| hegel-bin.000006 | 321 | Query      |         1 |          321 | use `foo`; update a join b using (x) set a.x=100 |
+------------------+-----+------------+-----------+--------------+--------------------------------------------------+
6 rows in set (0.00 sec)