MySQL Replication Error

Question

I am getting an Error on Slave as:

could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 60121977

As I am directly getting updates from master so why this happens.

How can we handle this in an effective manner so that there is no data loss on the slave.I do not want to set up the whole replication again for maintaining the data integrity.

Thanks..!

Answer 1

Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. The query should appear in the output of SHOW SLAVE STATUS\G

For example, in your error it says Duplicate entry '174465' for key 'PRIMARY'. This indicates that you should look up the value 174465 in the table you are either doing an INSERT or UPDATE. If the row does exist, can you have to decide if the query halted execution will change the row's contents. If the query will simply reproduce the exact same contents, and you believe that will be the case, you can perform one of two options:

OPTION 1

Skip the error, wait 5 seconds, and view the Slave Status. Here the 5 steps for Skipping an Error

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

When you view the Slave Status, here is what to expect

• If Seconds_Behind_Master is NULL

  • Replication is Broken : Look for Tell-Tale Signs
  • If Error Number is 1062 again, Repeat the 5 steps for Skipping an Error

• If Seconds_Behind_Master is a Number

  • Replication is running
  • When Seconds_Behind_Master > 0, Replication is Catching Up.
  • When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

OPTION 2

Remove the row to allow replication to continue

Delete the row from the table on the Slave and do the following 4 Steps for Skipping an Error:

STOP SLAVE;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

At the risk of sounding redundant...

When you view the Slave Status, here is what to expect

• If Seconds_Behind_Master is NULL

  • Replication is Broken : Look for Tell-Tale Signs
  • If Error Number is 1062 again, delete the row Repeat the 4 steps for Skipping an Error

• If Seconds_Behind_Master is a Number

  • Replication is running
  • When Seconds_Behind_Master > 0, Replication is Catching Up.
  • When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

What if there are just too many duplicate key issues? Here are some of my earlier posts concerning how to use MAATKIT's mk-table-checksum, mk-table-sync, pt-table-checksum, pt-table-sync:

• In a MySQL Master/Slave replication, what would happen if I write to the Slave?
• Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working
• MySQL replication problem: slave has become out of sync

Answer 2

I have made a script as below to resolve this issue:

Create a SQL script which will stop slave and set SQL_SLAVE_SKIP_COUNTER to then start the slave.

>>cat mysql_skip.sql
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
SELECT SLEEP(1);

then :

>>cat mysql_skip.sh

dup_status=`mysql -uroot -p************** --execute="show slave status"|grep HA_ERR_FOUND_DUPP_KEY|wc -l`
while [ $dup_status -ne 0 ]
do
        mysql -uroot -p************** < mysql_skip.sql
        dup_status=`mysql -uroot -p************** --execute="show slave status"|grep HA_ERR_FOUND_DUPP_KEY|wc -l`
        mysql -uroot -p************** --execute="show slave status"|grep HA_ERR_FOUND_DUPP_KEY
        echo $dup_status
done

This way I tackled the slave replication clash. This may help all.

Answer 3

In my situation slave replication coordinates were broken and I fixed this by resetting my slave and connect my slave to master by new coordinates without any data loss. First, check if your slave gtid_slave_pos is differing from other slaves or master gtid_current_pos:

    # on master
    SELECT @@GLOBAL.gtid_current_pos;

    +---------------------------+ 
    | @@GLOBAL.gtid_current_pos |
    +---------------------------+
    | 0-1-2                     |
    +---------------------------+

    # on slave
    SELECT @@GLOBAL.gtid_slave_pos;

    +-------------------------+
    | @@GLOBAL.gtid_slave_pos |
    +-------------------------+
    | 0-1-4                   |
    +-------------------------+

Then:

    RESET SLAVE;
    SET GLOBAL gtid_slave_pos = "0-1-2";
    CHANGE MASTER TO 
        MASTER_HOST="c1dbserver1", 
        MASTER_PORT=3310, 
        MASTER_USER="repl",  
        MASTER_PASSWORD="password", 
        MASTER_USE_GTID=slave_pos;
    START SLAVE;

You can find more details here https://mariadb.com/kb/en/library/using-mariadb-replication-with-mariadb-galera-cluster-configuring-mariadb-r/#start-replication-on-the-new-slave

Answer 4

The following script will keep skipping the global counter till replication error gets fixed. Replication error happens generally when the time of dump from master differs from the position of master taken differs.

Try this script:

#!/bin/bash
logfile=~/slave-watcher.log
while true; do
    status=$(mysql --execute="show slave status\G"|grep "Seconds_Behind_Master:"|awk '{print $2}')
    if [ $status == "NULL" ]; then
        mysql --execute="show slave status\G" | grep "Last_SQL_Error:" | tee -a $logfile
        mysql --execute="set global sql_slave_skip_counter=1; start slave;"
    fi
    sleep 1
done

Note: To add password, modify script like this:

mysql -p your_password --execute