MySQL last_sql_errno: 1062 —- classic error, primary key conflict

Time:2020-3-22

1、 Basic information

1. Centos7.4

2.MySQL 5.7.21

3. Gtid based replication

2、 Exception description

The slave node is mistakenly used as the master node to insert a piece of data. The same data has been inserted once in both the master and slave nodes, resulting in primary key conflict and SQL thread exception under slave.

The recommended setting is read-only from the Library:

Set global read only = 1; — set to read-only for normal MySQL database users
Set global super read only = 1; — set read-only for super MySQL database users, such as root

 

My.cnf file settings are permanently read-only

read_only=1
super_read_only=1;

 

3、 Process

1. Check the error information – the error is roughly as follows

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.1
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_binlog.000023
          Read_Master_Log_Pos: 2266930
               Relay_Log_File: devmidsrv01kf-relay-bin.000003
                Relay_Log_Pos: 423
        Relay_Master_Log_File: mysql_binlog.000016
             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: Could not execute Write_rows event on table testdb1.students; Duplicate entry ‘8’ for key ‘PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql_binlog.000016, end_log_pos 340380520
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 340380190
              Relay_Log_Space: 6541566399
              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: Could not execute Write_rows event on table testdb1.students; Duplicate entry ‘8’ for key ‘PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql_binlog.000016, end_log_pos 340380520
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 757
                  Master_UUID: 7bd36140-a92d-11e9-ba1f-005056a4099b
             Master_Info_File: /data/mysql/3306/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 191205 16:19:02
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 7bd36140-a92d-11e9-ba1f-005056a4099b:835084-2324874
            Executed_Gtid_Set: 7bd36140-a92d-11e9-ba1f-005056a4099b:1-835083,
bcad977d-a934-11e9-a589-005056a483f4:1-29
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

 

2. The key is to confirm the gtid to be skipped

Find the executed gtid, and add 1

7bd36140-a92d-11e9-ba1f-005056a4099b:1-835083

 

3. skip gtid

stop slave;

SET @@SESSION.GTID_NEXT= ‘7bd36140-a92d-11e9-ba1f-005056a4099b:1-835084’;

BEGIN; COMMIT;

SET SESSION GTID_NEXT = AUTOMATIC;

START SLAVE;

 

4. Check

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.1
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_binlog.000023
          Read_Master_Log_Pos: 2266930
               Relay_Log_File: devmidsrv01kf-relay-bin.000003
                Relay_Log_Pos: 423
        Relay_Master_Log_File: mysql_binlog.000016
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

 

Recommended Today

Case analysis of the use of REXML in Ruby program to parse XML format data

REXMLIs a library written by Sean Russell. It’s notRubyThe only XML library, but it is very popular and written in pure Ruby (nqxml is also written in ruby, but xmlparser encapsulates the jade library written in C). In his rexml overview, Russell commented:I have this problem: I don’t like confusing APIs. There are several XML […]