MySQL Replication : Duplicate entries - SQL_SLAVE_SKIP_COUNTER or delete row?

Question

I have a system which replicates one database to four slave servers. Every now and then, when traffic is high, one or more of the slave servers hits a duplicate insert error and the slave stops running.

When this happens, I have two choices - I can either SET GLOBAL SQL_SLAVE_SKIP_COUNTER or I can delete the offending row in the slave. Both seem to work and my logic says that given something happened to cause this problem, there is a possibility that the data in the slave is corrupted. Given that this can only happen on INSERTs, by deleting the row I guarantee the slave data will match the master once replication resumes. By skipping, if the data for that row is corrupted in the slave, it will remain corrupted.

Am I missing anything?

Further, given that this happens once every couple of months on two specific tables, is there any reason I shouldn't automate a process that triggers when this error is encountered, deletes that row in the slave and re-starts the slave?

EDIT: MySQL 5.5.29 and statement replication I believe.

• ROHIT KHURANA: you need to add slave-skip-errors=1062 in mysql.cnf or mysql.ini then restart mysql. which will skip all duplicate errors.

Answer 1

You don't have to use any trigger, If you want to safely skip duplicate key errors, please add this to my.cnf on slaves

[mysqld]
slave-skip-errors=1062

Then restart mysql

Check if all the slaves have different serverIDs