Tip: MySQL Replication Error: Duplicate Entry for Key

Rai    Linux     database, error, fix, Linux, mysql, replication, solution

I have encountered this error, which to my surprise occurred on a slave database.

error 'Duplicate entry '2355476' for key 1' on query ' INSERT INTO logs (DateStamp) VALUES (NOW())

You might be wondering how a slave database could have a duplicate entry while the master and other slaves are running just fine?

Some say that there could be an entry where it was written in the slave and then written again so the duplicate error occurred. Others say that if myisamchk was run in the master, it removed some entries and tries to create those entries again. The slave then attempts to write the same entry, but since myisamchk was not executed on the slave, the entry exists hence the duplication error.

But luckily, found myself a solution to fix the replication error:

mysql> set sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

This will skip the error and continue on with the replication. Do not forget to start the slave process afterwards.