Replication Failure Due to Duplicates

1.     Log onto the slave and check slave status

 

2.     If the error is due to a duplicate, see example below, you can first set skip counter to see if it skips it, if not then just delete the row on the slave and restart replication.  If there are many duplicates, then use the script in part c).

 

 

a) before skipping, just check if it is the exact same row being reinserted that fails,

select * from <table> where  <key field> = <see value in message>,    If all the returned columns match what was being inserted it means the master has sent the records twice.  This is a known intermittant bug in mySQL that was supposed to be fixed in vn 4, but still happens in vn 4 and 5.  The work around is to put the following entry into the my.cnf on the slave to automatically skip these duplicates.

 

[mysqld]

slave-skip-errors = 1062

 

..and restart the server.

 

b) try skipping one row manually as in this example:

 

 

ERROR :

Subject: mySQL error on mk-lluprequal-2

 

080130 11:57:39 [ERROR] Slave: Error 'Duplicate entry '7851915' for key 1' on query. Default database: 'prequal'. Query: 'insert into checker_requests (api_version, package, cli, ip, ts) values ('1.7','btv9','01793762637','195.224.207.200',now())', Error_code: 1062 080130 11:57:39 [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.000042' position 771163277

 

Steps taken :

 

Show slave status \G

Stop slave;

set global sql_slave_skip_counter=1;

start slave;

Show slave status \G

 

and repeat until Show slave status \G shows that there are no more errors.  Also move the error log.

If the error is still there, then try deleting it as follows:

 

b) try skipping as in this example:

 

 

      mysql> show slave status \G

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: mk-fusion-1.uk.intranet

                Master_User: repl

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000038

        Read_Master_Log_Pos: 87465725

             Relay_Log_File: mk-fusion-2-relay-bin.000002

              Relay_Log_Pos: 84940721

      Relay_Master_Log_File: mysql-bin.000038

           Slave_IO_Running: Yes

          Slave_SQL_Running: No

            Replicate_Do_DB:

        Replicate_Ignore_DB:

         Replicate_Do_Table:

     Replicate_Ignore_Table:

    Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

                 Last_Errno: 1062

                 Last_Error: Error 'Duplicate entry 'oxythony77@tiscali.co.uk' for key 1' on query. Default database: 'WALLEDGARDEN'. Query: 'insert into USERS(`DIALLEDNUMBER`,`USERNAME`,`DATE`,`SERVICEID`)  values('NONE','oxythony77@tiscali.co.uk','2008-01-17 17:26:19','2')'

               Skip_Counter: 0

        Exec_Master_Log_Pos: 84983826

            Relay_Log_Space: 87426970

            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

 

Using this example, issue the following commands

·       use WALLEDGARDEN;

·       delete  from USERS where username = 'oxythony77@tiscali.co.uk';          

·       stop slave;                                                                                          

·       start slave;                                                                                           

·        show slave status \G                                                                    

 

            If you get more than 1 duplicate, repeat the delete with the new value and stop / restart the slave again. Continue until show slave status shows no more duplicates

 

 

c) Script to remove many duplicates

 

on msh/bth where sockets are used:

 

#!/bin/sh

 

Slave_SQL_Running=`/slave/mysql-bin/mysql/bin/mysql -uroot -p*** -S /tmp/mysql-slave.sock -e "SHOW SLAVE STATUS\G" |  grep -i Slave_SQL_Running | awk '{ p

rint $2 }'`

 

echo "SLAVE running-->$Slave_SQL_Running"

 

if [ $Slave_SQL_Running != 'Yes' ]

then

        echo "mySQL not running.  Stopping slave...."

        /slave/mysql-bin/mysql/bin/mysql -uroot -p***-S /tmp/mysql-slave.sock -e "STOP SLAVE";

        /slave/mysql-bin/mysql/bin/mysql -uroot -p***-S /tmp/mysql-slave.sock -e "set global sql_slave_skip_counter=1";

        /slave/mysql-bin/mysql/bin/mysql -uroot -p***-S /tmp/mysql-slave.sock -e "START SLAVE";

else

        echo "SLAVE running-->$Slave_SQL_Running"

 

fi

 

 

On most of the other databases:

 

[root@mk-nn-radproxy-5 ~]# vi skip_duplicate.sh

 

#!/bin/sh

 

export PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/games:/usr/local/sbin:/usr/local/bin:/usr/X11R6/bin:/usr/home/hlakhan/bin

 

Slave_SQL_Running=`mysql -uroot -p'Bisohh!b' -e "SHOW SLAVE STATUS\G" |  grep -i Slave_SQL_Running | awk '{ print $2 }'`

 

echo "SLAVE running-->$Slave_SQL_Running"

 

if [ $Slave_SQL_Running != 'Yes' ]

then

        echo "mySQL not running.  Stopping slave...."

        mysql -uroot --p*** -e "STOP SLAVE";

        mysql -uroot --p*** -e "set global sql_slave_skip_counter=1";

        mysql -uroot --p*** -e "START SLAVE";

else

        echo "SLAVE running-->$Slave_SQL_Running"

 

fi