Jan

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.

3 Comments to “Tip: MySQL Replication Error: Duplicate Entry for Key”

Ratheesh January 7, 2010 at 6:57 pm
I’m having the similar problem but its very often. I was more curios what would be root cause and how to resolve it once in for all. A stable solution that can solve the problem forever

any ideas or anybody ??
Uma July 27, 2010 at 2:54 pm
But any idea why in first place does this occur?? We face this error many times, we know the solution to skip the tran. But we need the reason as to why do we face this on slave, when all is fine on the master?
mandm August 3, 2010 at 10:38 pm
i had to do this

set global sql_slave_skip_counter = 1;

since it is a global variable

but what does this setting actually do internally to the entire replication process