Description:
MySQL replication fails to update a table on the replicate side with a multi-table update statement. The multi-table update statement succeeds on the primary.
How to repeat:
Setup 2 MySQL Servers with Replication.
Using MySQL command line, run the following statements...
Server 1:
CREATE TABLE A (
id int(11) NOT NULL default '0',
date1 datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO A (id, date1)
VALUES (1, '0000-00-00 00:00:00');
CREATE TABLE B (
id int(11) NOT NULL default '0',
date1 datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id,date1)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO B (id, date1)
VALUES (1, '0000-00-00 00:00:00');
Now run the following command to update date1 in table A:
update A, B
set A.date1 = '2005-07-01 00:00:00'
where A.id = B.id;
Results:
Server 1:
mysql> select * from A;
+----+---------------------+
| id | date1 |
+----+---------------------+
| 1 | 2005-07-01 00:00:00 |
+----+---------------------+
1 row in set (0.03 sec)
Server 2:
mysql> select * from A;
+----+---------------------+
| id | date1 |
+----+---------------------+
| 1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.03 sec)
If I change the update to be a subquery, the update replicates!
update A
set A.date1 = '2005-07-01 00:00:00'
where A.id in (Select B.id from B);
Server 1:
mysql> select * from A;
+----+---------------------+
| id | date1 |
+----+---------------------+
| 1 | 2005-07-01 00:00:00 |
+----+---------------------+
1 row in set (0.03 sec)
Server 2:
mysql> select * from A;
+----+---------------------+
| id | date1 |
+----+---------------------+
| 1 | 2005-07-01 00:00:00 |
+----+---------------------+
1 row in set (0.04 sec)
Suggested fix:
Make Replication work properly.
