MySQL Replication Duplicate Entry for Primary Key

Question

160523 13:07:29 [ERROR] Slave SQL: Error 'Duplicate entry '914166' for key
'PRIMARY'' on query. Default database: 'zo_dev_20121216'. Query: 'UPDATE    
   activity
SET
   activity_type_id = 9,
   subject = 'Send departure email',
   date_due = '2016-05-26 01:00',
   date_start = '2016-05-23 01:00',
   activity_status_id = 1,
   content = 'Send departure email'
WHERE
   id = 2888555', Error_code: 1062
160523 13:07:29 [Warning] Slave: Duplicate entry '914166' for key 'PRIMARY'     
Error_code: 1062
160523 13:07:29 [ERROR] Error running query, slave SQL thread aborted. Fix     
the problem, and restart the slave SQL thread with "SLAVE START". We stopped   
at log 'mysql-bin.000004' position 14847360

Many people have suggested to use slave-skip-error option etc.. but that will completely avoid the query. How do you actually continue with the query that poses the error ? Also, I could not find a good answer as to why this actully occurs and what the number "914166" indicates ? Can someone please explain ?

Answer 1

What the number 914166 indicates?

The number indicates the row of the table where you are trying to insert data. For example, you are trying to insert some values to 914166th row.

Why this error occurs?

This error occurs because of duplicate value. You are trying to insert/update 914166 to a table activity as a primary key, but the value 914166 already exists in that table activity.

How do you actually continue with the query that poses the error?

You cannot continue with that query, since primary key column is a unique column, it does not accept duplicate values. You can skip this query and continue with the next query by using the following statements:

stop slave;

set global sql_slave_skip_counter = 1;

start slave;

Please note that, skipping errors is not a good option, you should try to fix the issues and start slave as suggested by MySQL.

Work around:

Is that mentioned query is complete statement?

It seems the given query is not a insert query but it is a update statement and it is not updating primary key of the activity table.

Check in master server, whether the mentioned primary key (id) column belongs to activity table or not and check the value (id=914166) exists in that column.

select * from zo_dev_20121216.activity where id = 914166;

If the value exist in that activity table, check in slave server, whether the particular value (id=914166) is exist or not, if it exist in slave server, you can simply skip the query.

select * from zo_dev_20121216.activity where id = 914166;

If you are getting empty result set for the above 2 queries, you can be sure that, the query is incomplete, or you are debugging wrong query.

The point is that, the query is trying to insert/update a value into a activity table which is already exists in that table.

I hope it will help you as a starting point for debugging the error.

Answer 2

Perhaps you can check out this question and answer: MySQL replication: 'Duplicated entry for PRIMARY key'

1 : Skip all the Duplicate Key errors Simply add this to my.cnf on the Slave

[mysqld] slave-skip-errors=1062 skip-slave-start and restart mysql. Then, run START SLAVE;

all the duplicate-key errors will get bypassed. When Seconds_Behind_Master gets >to 0, remove those lines and restart mysql.