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?
Best Answer
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