[Warning] Slave: Duplicate entry '11444' for key 'PRIMARY' Error_code: 1062

Sometimes my MariaDB cluster has some strange issues, my Nagios monitoring alerts me with the following message:

***** Nagios *****

Notification Type: PROBLEM
Host: db02
Service: MySQL Replication
State: CRITICAL
Slave IO: Yes Slave SQL: No Seconds Behind Master: (null)

When I look for the error in the error log (in my case: /var/log/mysql/error.log) I see the following error:

150823 18:34:10 [ERROR] Slave SQL: Error 'Duplicate entry '11444' for key 'PRIMARY'' on query. Default database: '................. , Internal MariaDB error code: 1062
150823 18:34:10 [Warning] Slave: Duplicate entry '11444' for key 'PRIMARY' Error_code: 1062
150823 18:34:10 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mariadb-bin.000091' position 621405

It seems my Drupal CMS wants to insert some information but inserts it twice.... ?

My slave database has already the the latest data and the master wants to replicate the latest data to the slave, so this error arises.

You can fix this message in 3 ways:

  1. Delete the existing record from the slave and continue the replication
  2. Ignore/skip this error and continue with the replication
  3. Ignore the replication of some tables

With the first option delete the record/entry from the slave server, usually there is a key/primary key in the error, so deleting is simple.

For now, the second option is my choice:

1. Login to the server with the mysql client and with enough user rights
2. Stop the replication:

STOP SLAVE;

3. Skip the current error:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

4. Start the slave replication again:

START SLAVE;

5. Check the replication status:

SHOW SLAVE STATUS\G

 

The third option is described here: