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

[4 Dec 2004 23:17] Dean Ellis
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
[14 Dec 2004 23:28] Guilhem Bichot
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.

[14 Dec 2004 23:57] Guilhem Bichot
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.
[14 Dec 2004 23:58] Guilhem Bichot
The problem happens only if there are some replicate-*table rules
[18 Dec 2004 17:47] Guilhem Bichot
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.
[20 Dec 2004 12:40] Andrew Sitnikov
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)
[10 Jan 2005 11:18] [ name withheld ]
when this bug will be fixed ?
[13 Jan 2005 13:58] Jeff Fisher
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.
[26 Jan 2005 19:50] Lenz Grimmer
A preliminary patch (not pushed yet) is at http://lists.mysql.com/internals/20672
[30 Jan 2005 19:50] Antony Curtis
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.
[10 Feb 2005 18:25] Antony Curtis
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
[10 Feb 2005 19:11] Paul Dubois
Mentioned in 4.0.24, 4.1.10, 5.0.3 change notes.