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?

  • 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 '15 at 17:11
  • We are using MIXED as the format. – Pascal Robert Nov 25 '15 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 '15 at 19:08
  • Latency is unlikely. How did you create the new slave? – Rick James Dec 2 '15 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 '15 at 14:07
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

  • 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 '15 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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