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.