Last_Errno: 1062, Last_Error: Error 'duplicate entry '...' fo

Last_Errno: 1062, Last_Error: Error 'duplicate entry '... 'for key'primary'' on query. in the detailed analysis process, I have never encountered 1062 problems in the online environment. the development environment of the test environment keeps having similar problems. In the past, in order to catch up with it, it was a skip or directly re-do it. This will take some time, check the problem.

1. Slave database error message: mysql> show slave status \ G ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: export Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000154 Read_Master_Log_Pos: 56680675 Relay_Log_File: mysql-relay-bin.000455 Relay_Log_Pos: 33013454 Relay_Master_Log_File: m Ysql-bin.000152 Slave_IO_Running: Yes Slave_ SQL _Running: No Replicate_Do_DB: rows: Replicate_Do_Table: rows: Last_Errno: 1062 Last_Error: Error 'duplicate entry '250. 1.1-rding-changelogs/myIDENTITY/250/xxxx. xml 'for key'primary'' on query. default database: 'Identity '. query: 'insert INTO 'databasechangelog '(' Dateexecuted', 'autor', 'xxx', 'description', 'comments', 'md5sum', 'id', 'filename') VALUES (NOW (), 'rding', '1. 9.3 ', 'customsql', '', '4ac9fbf5222bc344362ccdecbc072', '2017. 1.1 ', 'changelogs/myIDENTITY/250/xxxx. xml ') 'skip_counter: 0 Exec_Master_Log_Pos: 33013308 Relay_Log_Space: 33020134 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File SL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: Role: No Role: 0 Last_IO_Error: Role: 1062 Last_ SQL _Error: Error 'duplicate entry '250. 1.1-rding-changelogs/myIDENTITY/250/xxxx. xml 'for key'primary'' on query. default database: 'Identity '. query: 'insert INTO 'databasechangelog '('dateexecuted', 'author', 'xxxx', 'Description', 'comments', 'md5sum', 'id', 'filename') VALUES (NOW (), 'rding', '1. 9.3 ', 'customsql', '', '4ac9fbf5222bc344362ccdecbc072', '2017. 1.1 ', 'changelogs/myIDENTITY/250/xxxx. xml ') 'replicate_ignore_server_ids: Master_Server_Id: 11 row in set (0.00 sec) 2. view the table structure mysql> show create table DATABASECHANGELOG; + ----------------- + response -------------------------------------------------------------------- Creating External tables + | Table | Create Table | + ----------------- + Too many bytes ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Criteria + | DATABASECHANGELOG | create table 'databasechangelog '('id' varchar (63) not null, 'author' varchar (63) not null, 'filename' varchar (200) not null, 'dateexecuted' datetime not null, 'md5sum' varchar (32) default null, 'description' varchar (255) default null, 'comments' varchar (255) default null, 'tag' varchar (255) default null, 'xxxx 'Varchar (10) default null, primary key ('id', 'author', 'filename') design structure of the table, not my favorite style) ENGINE = InnoDB default charset = utf8 | + ----------------- + zhuyun ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Choose --------------------------------------------------------- + 1 row in set (0.00 sec) 3. Check the existing data mysql> select * from DATABASECHANGELOG where AUTHOR = 'rding '; + --------- + -------- + ------------------------------------------- + --------------------- + -------------------------------- + ------------- + Response + ------ + ----------- + | ID | AUTHOR | FILENAME | DATEEXECUTED | MD5SUM | DESCRIPTION | COMMENTS | TAG | xxxx | + --------- + -------- + tags + ----------------- + signature + --------------- ---------------------------------------- + ------ + ----------- + | 250.1.1 | rding | changelogs/myIDENTITY /250/xxxx. xml | 20:41:22 | 4ac9fbf5222bc344362ccdecbc072 | Custom SQL | NULL | 1.9.3 | 250.1.2 | rding | changelogs/myIDENTITY/250/xxxx. xml | 20:41:22 | metric | Custom SQL | Create new table for email change record | NULL | 1.9.3 | + --------- + -------- + metric + --------------------- + --------------------------------- -+ ------------- + ------------------------------------------ + ------ + ----------- + 2 rows in set (0.00 sec) 4 View binlog, in Relay_Master_Log_File: mysql-bin.000152, go to the main database to find this binlog Parsing Out of the [root @ xxxx0402 tmp] # mysqlbinlog mysql-bin.000152> a152.log search contains the 'rding 'string of the statement, because this is one of the primary key fields, therefore, it should be easier to search. [Root @ xxxx0402 tmp] # grep a152.log 'rding'> rd. loggrep: rding: No such file or directory [root @ xxxx0402 tmp] # grep 'rding' a152.log> rd. log [root @ xxxx0402 tmp] # ll [root @ xxxx0402 tmp] # more rd. log insert into 'databasechangelog '('dateexecuted', 'autor', 'xxx', 'description', 'comments', 'md5sum', 'id', 'filename ') VALUES (NOW (), 'rding', '1. 9.3 ', 'customsql', '', '4ac9fbf5222bc344362ccdecbc072 ', '2017. 1.1 ', 'changelogs/myIDENTITY/250/xxxx. xml ') insert into 'databasechangelog' ('dateexecuted', 'autor', 'xxx', 'description', 'comments', 'md5sum', 'id ', 'filename') VALUES (NOW (), 'rding ', '1. 9.3 ', 'customsql', 'create new table for email change record', '8463e1cf4ba029e3ace675d3e69a71d2', '2017. 1.2 ', 'changelogs/myIDENTITY/250/xxxx. xml') There is only one insert SQL statement on the master database. 5. Check the relay log Relay_Log_File of the slave database: mysql-relay-bin.000455 [root @ login data] # cp mysql-relay-bin.000455/tmp [root @ eanshlt2mydbc004db002 data] # cd/tmp [root @ eanshlt2mydbc004db002 tmp] # mysqlbinlog mysql-relay-bin.000455> relay. log [root @ eanshlt2mydbc004db002 tmp] # [root @ eanshlt2mydbc004db002 tmp] # grep 'rding' relay. log> rd. log [root @ eanshlt2mydbc004db002 tmp] # more rd. logINSERT INTO 'databasechangelog '('dateexecuted', 'autor', 'xxx', 'description', 'comments', 'md5sum', 'id', 'filename ') VALUES (NOW (), 'rding', '1. 9.3 ', 'customsql', '', '4ac9fbf5222bc344362ccdecbc072', '2017. 1.1 ', 'changelogs/myIDENTITY/250/xxxx. xml ') insert into 'databasechangelog' ('dateexecuted', 'autor', 'xxx', 'description', 'comments', 'md5sum', 'id ', 'filename') VALUES (NOW (), 'rding ', '1. 9.3 ', 'customsql', 'create new table for email change record', '8463e1cf4ba029e3ace675d3e69a71d2', '2017. 1.2 ', 'changelogs/myIDENTITY/250/xxxx. xml ')

 

It's strange that the two sides are all the same. How can this error be determined? 6. Check the time when the Master/Slave node enters the data record.

From the database: mysql> select * from DATABASECHANGELOG where AUTHOR = 'rding 'and ID = '2017. 1.1 'and FILENAME = 'changelogs/myIDENTITY/250/xxxx. xml' \ G; *************************** 1. row ************************** ID: 250.1.1 AUTHOR: rding FILENAME: changelogs/myIDENTITY/250/xxxx. xmlDATEEXECUTED: 20:41:22 MD5SUM: 4ac9fbf5222bc344362ccdecbc072 DESCRIPTION: Custom SQL COMMENTS: TAG: NULL xxxx: 1.9.31 row in set (0.00 sec) ERROR: No query specifiedmysql> master database above: mysql> select * from DATABASECHANGELOG where AUTHOR = 'rding 'and ID = '20180101. 1.1 'and FILENAME = 'changelogs/myIDENTITY/250/xxxx. xml' \ G; *************************** 1. row ************************** ID: 250.1.1 AUTHOR: rding FILENAME: changelogs/myIDENTITY/250/xxxx. xmlDATEEXECUTED: 19:54:29 MD5SUM: 4ac9fbf5222bc344362ccdecbc072 DESCRIPTION: Custom SQL COMMENTS: TAG: NULL xxxx: 1.9.31 row in set (0.02 sec) ERROR: No query specifiedmysql>

 

Check that all the DATEEXECUTED time fields were entered on April 9, August 12. Unfortunately, my db server only saves recent binlogs due to limited disk capacity, and now the first binlog on the master database is the last trick of the wrong mysql-bin.000152 7, to see the binlog from the database, to see if someone recently inserted this record

[root@eanshlt2mydbc004db002 data]# cp mysql-bin.004* /tmp/[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004268 > 1.log[root@eanshlt2mydbc004db002 tmp]# grep  'rding' 1.log > rd1.log[root@eanshlt2mydbc004db002 tmp]# ll rd1.log-rw-r--r-- 1 root root 0 Sep  3 17:47 rd1.log

 

Null. The first log is not input.

[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004269 > 2.log[root@eanshlt2mydbc004db002 tmp]# grep  'rding' 2.log > rd2.log[root@eanshlt2mydbc004db002 tmp]# ll rd2.log-rw-r--r-- 1 root root 0 Sep  3 17:48 rd2.log[root@eanshlt2mydbc004db002 tmp]# 

 

Null. The second log is not input.

[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004270 > 3.log[root@eanshlt2mydbc004db002 tmp]# grep  'rding'  3.log > rd3.log[root@eanshlt2mydbc004db002 tmp]# ll rd3.log-rw-r--r-- 1 root root 0 Sep  3 17:49 rd3.log[root@eanshlt2mydbc004db002 tmp]# 

 

Null. The third log is not input.

[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004271 > 4.logERROR: Error in Log_event::read_log_event(): 'read error', data_len: 438, event_type: 2[root@eanshlt2mydbc004db002 tmp]# grep  'rding' 4.log > rd4.log[root@eanshlt2mydbc004db002 tmp]# ll rd4.log-rw-r--r-- 1 root root 0 Sep  3 17:50 rd4.log

 

Null. If the fourth log is not input, an error is reported during parsing. The solution is recorded as follows:

[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004272 > 5.log[root@eanshlt2mydbc004db002 tmp]# grep  'rding' 5.log > rd5.log[root@eanshlt2mydbc004db002 tmp]# ll rd5.log-rw-r--r-- 1 root root 0 Sep  3 18:07 rd5.log[root@eanshlt2mydbc004db002 tmp]# 

 

Null. If the fifth log is not input, five logs from the slave database are parsed, and no insert operation of this discipline is seen, the problem is stuck. Why? The developers here are already urging me to redo it from the database as long as the skip address is used.

mysql> stop slave;set global sql_slave_skip_counter=1;start slave;show slave status\GQuery OK, 0 rows affected (0.09 sec)mysql> set global sql_slave_skip_counter=1;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: xxxx0402.china.online.ea.com                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000184          Read_Master_Log_Pos: 27865900               Relay_Log_File: mysql-relay-bin.000495                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql-bin.000171             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 107              Relay_Log_Space: 8000              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: 3434734Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 11 row in set (0.11 sec)mysql>