I replicate my mysql master to a slave. They works very well but once in a while, duplicate entry would happen and I don't know how to resolve it ending up copy the master over and start replicate again which costs me a lot of time.

This is the show slave status\G :

Relay_Log_File: mysql-relay-bin.000139
Relay_Log_Pos: 12476407
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '573026' for key 'PRIMARY' .....

I use mysqlbinlog to show contents of mysql-relay-bin.000139 :

# at 12476407
.... SET TIMESTAMP=1636678470/*!*/;
# at 12476445
#211112  8:54:30 server id 1  end_log_pos 12476475  Query   thread_id=9 exec_time=0  
SET TIMESTAMP=1636678470/*!*/;
INSERT INTO ..... (ID, ......) VALUES (573026, .....)
# at 12476664
.... SET TIMESTAMP=1636678470/*!*/;
# at 12476734
.... SET TIMESTAMP=1636678472/*!*/;
# at 12476772
#211112  8:54:32 server id 1  end_log_pos 12476801  Query   thread_id=9 exec_time=0  
SET TIMESTAMP=1636678472/*!*/;
INSERT INTO .... (ID, ....) VALUES (573027, ....)

Then I check select * from .... where id=57326; and the record exists so it is a real duplication though I don't know why. After searching the web I tried to skip that duplicated record from the relay by:

stop slave;
set global sql_slave_skip_counter = 1;
start slave;

It did skipped the problem record. But then the replicate stops at the next INSERT:

Relay_Log_File: mysql-relay-bin.000139
Relay_Log_Pos: 12476734
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '573027' for key 'PRIMA ....

This time select * from .... where id=573027; gets nothing, so it is not really duplication. Appreciate any ideas how I can resolve this without copying the master over again. Thank you in advance.

  • How exactly did you create the replica? Are you inserting onto the slave using another application? If unsure, have you set read_only on the slave to prevent this?
    – danblack
    Nov 18 '21 at 4:05
  • Is the Replica set to readonly=ON; it should be.
    – Rick James
    Nov 18 '21 at 5:44
  • No, I only use mysql to sync master's updates to slave. No, the slave is not readonly. But I am sure no other software is updating the slave.
    – senderj
    Nov 18 '21 at 8:37
  • One thing may be relevant. The slave machine encountered BOSD recently but I have no way to check if the time coincident with the replication failure. Ever if it is so, the skip method should be able to skip the first duplication, and cannot explain the reason of the second duplication.
    – senderj
    Nov 18 '21 at 8:39
  • Row based or statement based replication is used?
    – Kondybas
    Nov 18 '21 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.