0

I'm having more and more errors with my MySQL (latest 5.1) replication, in a master-slaves (one master, two slaves). A couple of times in the last couple of days, I'm getting duplicate entry, like this:

Error 'Duplicate entry '4818122-74' for key 'PRIMARY'' on query.     
Default database: 'xxxxx'. 
Query: 'INSERT INTO `xxxxxx` (`xxxx`, `xxxxx`, `xxxx`, `xxxxx`, `xxxx`) VALUES ('4818122', '74', '566.29411764706', '34', '10')'

It happens 99% of the time on the same table. All databases are replicated.

What can cause this?

Improve this question
5
  • first of all - check what bin-log format are You use, than read article about replication problems and compare with You transactions -dev.mysql.com/doc/refman/5.1/en/replication-notes.html
    – a_vlad
    Nov 24, 2015 at 17:11
  • We are using MIXED as the format.
    – Pascal Robert
    Nov 25, 2015 at 22:00
  • Well. We added a new slave last week, and today, we got a primary key conflict on the two older slaves, but not the new one. The difference? The new one is connected to the same switch as the master, while the other two are located in another datacenter. Can latency could create those kind of problems?? Doesn't make sense to me, but we never know.
    – Pascal Robert
    Nov 30, 2015 at 19:08
  • Latency is unlikely. How did you create the new slave?
    – Rick James
    Dec 2, 2015 at 18:26
  • With a copy of the databases files while MySQL was stopped. The other two slaves were done with a complete dump (mysqldump --all-databases). I'm doing to rebuild one of the slaves with the binary files.
    – Pascal Robert
    Dec 3, 2015 at 14:07

1 Answer 1

Reset to default
2

The root cause could be very varying, specially if your slave is not in read_only mode. Clearly, the table has a data drift (means that both master and slave tables have different contents).

I suggest you to use pt-table-checksum in order to check the table consistency.

However, at this point I think that your table lacks of consistency, so I will recommend to dump and restore that table into the slave or, in s straightest way, clone again your slave. If you plan to rebui

Improve this answer
1
  • I can at least confirm that no other process/client is writing to the slave, so the duplicates are coming from the replication. We have two slaves, and both have the same issues, and they were built on different dates with a different dump.
    – Pascal Robert
    Nov 27, 2015 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.