160523 13:07:29 [ERROR] Slave SQL: Error 'Duplicate entry '914166' for key
'PRIMARY'' on query. Default database: 'zo_dev_20121216'. Query: 'UPDATE    
   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'
   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 ?


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.

| improve this answer | |
  • Thanks @rathish. That is the complete query. There are only 389848 rows in the table. The auto increment is at 2888784. The id column is the primary key column in the activity table. There is no id with 91466. I've copied and pasted the error from the Mysql error.log file where the replication broke. I do not see that being the wrong query that I'm debugging. – Ravi May 23 '16 at 14:32
  • @ravi, check is there any other unique key column in the activity table. – Rathish May 23 '16 at 14:38
  • No.. none. Only one primary key and nothing else. – Ravi May 23 '16 at 14:39
  • by the way I do not understand how can the row id be 914166 when the number of rows in the table are less than 400000 ? – Ravi May 23 '16 at 14:48
  • 2
    It's not the 914166th row, it is the row with 914166 as the value for the primary key. – Rick James May 31 '16 at 21:01

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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