MySQL Bugs: #7011: Multi-table update does not seem to execute on slave

Bug #7011	Multi-table update does not seem to execute on slave
Submitted:	4 Dec 2004 23:17	Modified:	10 Feb 2005 19:11
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.0.23	OS:	Any (all)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
A multi-table update does not appear to update any rows on the slave, but the query will update rows if executed via mysql client instead of by SQL thread.  Have only been able to repeat it on OS X.

How to repeat:
Will attach SQL.

Suggested fix:
n/a

a .test way to repeat the problem (Linux, Mac OS X); see README in the .tar.gz

Attachment: bug7011_simplified.tar.gz (application/x-tar, text), 1019 bytes.

Added a new file in the 'Files' section which shows a way to repeat the bug.
Does not depend on using InnoDB or Mac OS X; happens with MyISAM on Linux as well.

The problem happens only if there are some replicate-*table rules

Assigning it to Antony, as he submitted a patch which fixes the customer's testcase (I verified). Antony, note that the customer is hitting the problem on 4.0 so maybe a push into 4.0 would be worth it.

We also have same problems with 4.1.8

============================================================================================
master
============================================================================================
mysql> show variables like 'version%';
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| version                 | 4.1.8-standard-log             |
| version_comment         | Official MySQL-standard binary |
| version_compile_machine | i686                           |
| version_compile_os      | pc-linux                       |
+-------------------------+--------------------------------+
4 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE `t1` (
    ->   `id` mediumint(8) unsigned NOT NULL default '0',
    ->   `visited` datetime default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql>  
mysql> CREATE TABLE `t2` (
    ->   `id` mediumint(8) unsigned NOT NULL auto_increment,
    ->   `visited` datetime NOT NULL default '0000-00-00 00:00:00',
    ->   PRIMARY KEY  (`id`),
    ->   KEY `visited` (`visited`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;
 
INSERT INTO `t1` VALUES (16, '2004-12-20 14:02:03'); 
Query OK, 0 rows affected (0.02 sec)

mysql>  
mysql> INSERT INTO `t1` VALUES (16, '2004-12-20 14:02:03'); 
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t2` VALUES (16, '2004-12-20 14:05:03');
Query OK, 1 row affected (0.00 sec)

mysql>  
mysql> SELECT * FROM t1;
+----+---------------------+
| id | visited             |
+----+---------------------+
| 16 | 2004-12-20 14:02:03 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT * FROM t2;
+----+---------------------+
| id | visited             |
+----+---------------------+
| 16 | 2004-12-20 14:05:03 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> UPDATE t1, t2 SET t1.visited=t2.visited WHERE t1.id=t2.id;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> SELECT * FROM t1;
+----+---------------------+
| id | visited             |
+----+---------------------+
| 16 | 2004-12-20 14:05:03 |
+----+---------------------+
1 row in set (0.01 sec)

============================================================================================
slave
============================================================================================
mysql> show variables like 'version%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    16219
Current database: rate

+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| version                 | 4.1.8-standard-log             |
| version_comment         | Official MySQL-standard binary |
| version_compile_machine | i686                           |
| version_compile_os      | pc-linux                       |
+-------------------------+--------------------------------+
4 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 212.7.7.38
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.006067
        Read_Master_Log_Pos: 2722945
             Relay_Log_File: irc-relay-bin.006070
              Relay_Log_Pos: 17009517
      Relay_Master_Log_File: mysql-bin.006067
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: rate
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: rate.users_session
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 2722471
            Relay_Log_Space: 17009991
            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)

mysql> SELECT * FROM t1;
+----+---------------------+
| id | visited             |
+----+---------------------+
| 16 | 2004-12-20 14:02:03 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
+----+---------------------+
| id | visited             |
+----+---------------------+
| 16 | 2004-12-20 14:05:03 |
+----+---------------------+
1 row in set (0.00 sec)

when this bug will be fixed ?

Is there any chance we can get a copy of the patch which fixes the testcase? We are experiencing this problem on 4.1.7 and I'm more than willing to port the patch to 4.1.7 or 8.

A preliminary patch (not pushed yet) is at http://lists.mysql.com/internals/20672

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Patch applied to 4.0 repository. merge in progress.

Fix pushed to 4.0, 4.1 and 5.0 trees

4.0 ChangeSet@1.2040, 2005-01-30 10:24:03+00:00
4.1 ChangeSet@1.2153, 2005-01-31 13:18:36+00:00
5.0 ChangeSet@1.1819, 2005-01-31 20:49:37+00:00

Mentioned in 4.0.24, 4.1.10, 5.0.3 change notes.