The all too common MySQL ‘Duplicate entry’ Error.
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master-mysql.local Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.004768 Read_Master_Log_Pos: 1022786917 Relay_Log_File: relay-bin.001728 Relay_Log_Pos: 929659721 Relay_Master_Log_File: mysql-bin.004768 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: information_schema,mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry 'xyz' for key 'PRIMARY'' on query. Default database: 'db'. Query: 'INSERT INTO data ( id, version ) VALUES (279598012, 5)' Skip_Counter: 0 Exec_Master_Log_Pos: 929659575 Relay_Log_Space: 1022787256 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error:Error 'Duplicate entry 'xyz' for key 'PRIMARY'' on query. Default database: 'db'. Query: 'INSERT INTO data ( id, version ) VALUES (279598012, 5)' Replicate_Ignore_Server_Ids: Master_Server_Id: 10147115 1 row in set (0.00 sec)
The easy way to deal with this is to verify this it is a replication entry and running the following command.
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
However, if you have what seems to be a large number of duplicate entry error and don’t feel like skipping the entries one by one or you just don’t want replication to stop for this error. You can add the following to the /etc/my.cnf
slave-skip-errors = 1062
And restart the MySQL services. This will skip all the Duplicate entry errors until it’s removed and the MySQL is restarted.
Keep in mind, that this error can indicate other issues with the MySQL service or system, before skipping the error completely.
Leave a Reply
You must be logged in to post a comment.