I am getting an Error on Slave as:
could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 60121977
As I am directly getting updates from master so why this happens.
How can we handle this in an effective manner so that there is no data loss on the slave.I do not want to set up the whole replication again for maintaining the data integrity.
Thanks..!
Best Answer
Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. The query should appear in the output of
SHOW SLAVE STATUS\G
For example, in your error it says
Duplicate entry '174465' for key 'PRIMARY'
. This indicates that you should look up the value174465
in the table you are either doing an INSERT or UPDATE. If the row does exist, can you have to decide if the query halted execution will change the row's contents. If the query will simply reproduce the exact same contents, and you believe that will be the case, you can perform one of two options:OPTION 1
Skip the error, wait 5 seconds, and view the Slave Status. Here the 5 steps for Skipping an Error
When you view the Slave Status, here is what to expect
If Seconds_Behind_Master is NULL
If Seconds_Behind_Master is a Number
OPTION 2
Remove the row to allow replication to continue
Delete the row from the table on the Slave and do the following 4 Steps for Skipping an Error:
At the risk of sounding redundant...
When you view the Slave Status, here is what to expect
If Seconds_Behind_Master is NULL
If Seconds_Behind_Master is a Number
What if there are just too many duplicate key issues? Here are some of my earlier posts concerning how to use MAATKIT's mk-table-checksum, mk-table-sync, pt-table-checksum, pt-table-sync: