MySQL Bugs: #32437: INSERT ... ON DUPLICATE KEY UPDATE replication 5.0.27 master & 5.0.45 slave

Bug #32437	INSERT ... ON DUPLICATE KEY UPDATE replication 5.0.27 master & 5.0.45 slave
Submitted:	16 Nov 2007 14:41	Modified:	22 Nov 2007 8:36
Reporter:	Martin Houle	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux (CentOS)
Assigned to:		CPU Architecture:	Any

Description:

Hi,

I have a dual master replication setup with v.5.0.27, one slave v5.0.27 and another slave v.5.0.45. All works fine, with the exception that INSERT ... ON DUPLICATE KEY UPDATE will literaly KILL the replication on the v5.0.45 slave. If I avoid the error with slave-skip-errors, the table will not be updated.

I have made many tests with same table structure and also new table with, and the ONLY query that doesn't seem to pass through replication is INSERT ... ON DUPLICATE KEY UPDATE.

In all cases, all queries are applied to the tables on the 3 replicated v5.0.27 servers, but it breaks replication on my 5.0.45 slave with this error:

Last_Errno: 1105
Last_Error: Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; 
check error log on slave for more info' on query. Default database: 'streaming'. 
Query: 'INSERT INTO mount_infomusic (mounntid, title) VALUES (6421,'WCCO radio jingle') ON DUPLICATE KEY UPDATE lasttitle2=lasttitle1, lasttitle1=title'

My table is like this:
CREATE TABLE `mount_infomusic` (
  `id` int(12) NOT NULL auto_increment,
  `mountid` int(12) NOT NULL default '0',
  `title` varchar(100) NOT NULL,
  `lasttitle1` varchar(100) NOT NULL,
  `lasttitle2` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `mountid` (`mountid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=282 ;

I have been looking around to find this issue, which has been described in other bugs, but I don't have additionnal symptoms often tied to v.5.0.27. This is the only issue present throughout my replication setup and I really need to fix it.

Is there a way to fix this, or will I have to downgrade my 5.0.45 server to 5.0.27? Right now, upgrading all servers to 5.0.45 is near impossible.

Thanks for any help you can provide.

Martin H.
StreamTheWorld Senior Systems Administrator
www.streamtheworld.com

How to repeat:
-1 master v5.0.27, one slave v5.0.45, one table with primary key & auto_increment.
-INSERT ON DUPLICATE KEY UPDATE
-show slave status = broken

seems that most people are lucky enough that the replication still works, but the table are not updated.

I just wish it wouldn't break my replication, if there's something I can do, please share! 

Thanks again

Ok, finally, I got it resolved. We found that other tables in our replication setup were updating from the same king of queries and were replicating without problems.

Further testing showed that having a PRIMARY KEY AUTO_INCREMENT column will cause the problem, so we are resolving to unique index for the moment. Additionnaly, after deleting out primary key column & index, the problem was persistent, we had to drop the table and recreate it for the replication to work. There must've been something stuck somewhere.

Hope this helps someone at some point.

Martin H.

Thank you for the report.

You pasted:

>Query: 'INSERT INTO mount_infomusic (mounntid, title) 
<snip>
>CREATE TABLE `mount_infomusic` (
>  `id` int(12) NOT NULL auto_increment,
>  `mountid` int(12) NOT NULL default '0',

But mountid is not mounntid. So error is expected. This also confirms what recreation of table fixed the problem.