MySQL Bugs: #7847: Multi-table updates don't seem to replicate properly.

Bug #7847	Multi-table updates don't seem to replicate properly.
Submitted:	12 Jan 2005 22:25	Modified:	12 Feb 2005 23:05
Reporter:	Jeff Fisher	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:	Linux (Linux 2.4.21)
Assigned to:		CPU Architecture:	Any

Description:
Our replication is setup as such:

Master -> Slave (who is the master for the many slaves, we call it the Replication Master) -> Many Slaves

If I issue this query on the Master:

UPDATE mailaccount M, domains D SET M.passwordmd5 = NULL, M.password = 'newpassword' WHERE D.id = M.domain_id AND D.domain = 'example.com' AND M.username = 'test';

It changes on the Master and in my logfile on my Replication Master I see the query; however, if I issue:

SELECT password,passwordmd5 FROM mailaccount M, domains D where D.id = M.domain_id AND M.username = 'test' and D.domain = 'example.com';

I still see the old data. According to "SHOW SLAVE STATUS" both servers are caught up and nothing is waiting to happen.

If I just do a regular UPDATE like this:

UPDATE mailaccount SET password = 'newpassword', passwordmd5 = NULL WHERE id = 1607221;

It replicates just fine across all the slaves.

Now the weird thing is -- if I issue the multi-table UPDATE on the Replication Master, all the slaves behind it update accordingly. They just don't update properly if the Master executes the query and passes it on.

This is a rather serious issue for me -- if you need anymore information I will be more than happy to provide it for you.

How to repeat:
I can repeat it by using:

UPDATE mailaccount SET password = 'oldpassword', passwordmd5 = NULL WHERE id = 1607221;

to bring all the slaves into sync and then re-issue:

UPDATE mailaccount M, domains D SET M.passwordmd5 = NULL, M.password = 'newpassword' WHERE D.id = M.domain_id AND D.domain = 'example.com' AND M.username = 'test';

on the Master, all the slaves will get the event; however, only the Master will update.

Hi,
could it be the same as
http://bugs.mysql.com/bug.php?id=7011
(replicate*table options in the config file of Master?). Just a guess.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".