607878 - Replication keeps dying with a duplicate key error / REPLACE isn't replication-safe in MySQL 5.0

Status: RESOLVED FIXED

(Webtools :: Bouncer, defect)

Description

[Dave Miller [:justdave]]

Geting this error every day or two on the backup replicator server:

Error 'Duplicate entry '2491293875' for key 1' on query. Default database: 'bouncer'. Query: 'REPLACE mirror_location_mirror_map SET location_id='1700',mirror_id='318',active='0''

It looks like REPLACE isn't exactly replication safe in MySQL 5.0.

Details at http://bugs.mysql.com/bug.php?id=53194

We can replace the query in question (which is in the sentry code) with an INSERT ... ON DUPLICATE KEY UPDATE query, which is replication safe.

Docs on that are at
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Comment 1

[Dave Miller [:justdave]]

Attachment: patch

Comment 2

[Dave Miller [:justdave]]

Sending        sentry/sentry.pl
Transmitting file data .
Committed revision 76416.

Comment 3

[Fred Wenzel [:wenzel]]

I can pull this into github: http://github.com/fwenzel/tuxedo/commit/e639bdb

Perhaps I should give you access so you can push such changes to the main code repository directly?