6

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.

Improve this question
2
  • Question updated with more details, thanks
    – Mysql Admin
    Commented May 5, 2014 at 23:55
  • you need to add slave-skip-errors=1062 in mysql.cnf or mysql.ini then restart mysql. which will skip all duplicate errors.
    – ROHIT KHURANA
    Commented Jan 5, 2021 at 11:05

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

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

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