Last_Errno: 1062, Last_Error: ErrorDuplicateentry_MySQL

Last_Errno: 1062, Last_Error, it will take time to 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: xxxx0402.china.online.ea.com
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: mysql-bin.000152
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 '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:
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: 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 ')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

2. view the table structure

Mysql> show create table DATABASECHANGELOG;
+ ------------------- + Too many ---------------------------------------------------------------- +
| Table | Create Table |
+ ------------------- + Too many ---------------------------------------------------------------- +
| 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'). it's not my favorite style.
) ENGINE = InnoDB default charset = utf8 |
+ ------------------- + Too many ---------------------------------------------------------------- +
1 row in set (0.00 sec)

3. View existing data

Mysql> select * from DATABASECHANGELOG where AUTHOR = 'rding ';
+ --------- + -------- + Response + --------------------- + -------------------------------- + ----------------- + -------------------------------------- + ------------- +
| ID | AUTHOR | FILENAME | DATEEXECUTED | MD5SUM | DESCRIPTION | COMMENTS | TAG | xxxx |
+ --------- + -------- + Response + --------------------- + -------------------------------- + ----------------- + -------------------------------------- + ------------- +
| 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 | 8463e1cf4ba029e3ace675d3e69a71d2 | Custom SQL | Create new table for email change record | NULL | 1.9.3 |
+ --------- + -------- + Response + --------------------- + -------------------------------- + ----------------- + -------------------------------------- + ------------- +
2 rows in set (0.00 sec)

4 look at binlog, in Relay_Master_Log_File: mysql-bin.000152, go to the master database to find this binlog parsed

Analysis
[Root @ xxxx0402 tmp] # mysqlbinlog mysql-bin.000152> a152.log
Search for a statement containing the 'rding' string. because this is one of the primary key fields, it should be easier to search.
[Root @ xxxx0402 tmp] # grep a152.log 'rding'> rd. log
Grep: 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', 'xxxxx', 'Description', 'comments', 'md5sum', 'id', 'filename ') VALUES (NOW (), 'rding', '1. 9.3 ', 'custom'
SQL ', '', '4ac9fbf5222bc344362ccdecbc072', '192. 100', 'changelogs/myIDENTITY/250/xxxx. XML ')
Insert into 'databasechangelog '('dateexecuted', 'autor', 'xxxxx', 'Description', 'comments', 'md5sum', 'id', 'filename ') VALUES (NOW (), 'rding', '1. 9.3 ', 'custom'
SQL ', 'create new table for email change record', '8463e1cf4ba029e3ace675d3e69a71d2 ', '2017. 123456', 'changelogs/myIDENTITY/250/xxxx. XML ')
There is only one insert SQL statement on the master database.

5 again look at the relay log Relay_Log_File from the library: mysql-relay-bin.000455

[Root @ eanshlt2mydbc004db002 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. log
Insert into 'databasechangelog '('dateexecuted', 'autor', 'xxxxx', 'Description', 'comments', 'md5sum', 'id', 'filename ') VALUES (NOW (), 'rding', '1. 9.3 ', 'custom'
SQL ', '', '4ac9fbf5222bc344362ccdecbc072', '192. 100', 'changelogs/myIDENTITY/250/xxxx. XML ')
Insert into 'databasechangelog '('dateexecuted', 'autor', 'xxxxx', 'Description', 'comments', 'md5sum', 'id', 'filename ') VALUES (NOW (), 'rding', '1. 9.3 ', 'custom'
SQL ', 'create new table for email change record', '8463e1cf4ba029e3ace675d3e69a71d2 ', '2017. 123456', '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 = 'rdding 'and ID = '192. 000000' and FILENAME = 'changelogs/myIDENTITY/250/xxxx. XML'/G;
* *************************** 1. row ***************************
ID: 250.1.1
AUTHOR: rding
FILENAME: changelogs/myIDENTITY/250/xxxx. xml
DATEEXECUTED: 2013-08-12 20:41:22
MD5SUM: 4ac9fbf5222bc344362ccdecbc072
DESCRIPTION: Custom SQL
COMMENTS:
TAG: NULL
Xxxx: 1.9.3
1 row in set (0.00 sec)

ERROR:
No query specified
Mysql>

Above the master database:
Mysql> select * from DATABASECHANGELOG where AUTHOR = 'rdding 'and ID = '192. 000000' and FILENAME = 'changelogs/myIDENTITY/250/xxxx. XML'/G;
* *************************** 1. row ***************************
ID: 250.1.1
AUTHOR: rding
FILENAME: changelogs/myIDENTITY/250/xxxx. xml
DATEEXECUTED: 2013-08-12 19:54:29
MD5SUM: 4ac9fbf5222bc344362ccdecbc072
DESCRIPTION: Custom SQL
COMMENTS:
TAG: NULL
Xxxx: 1.9.3
1 row in set (0.02 sec)
ERROR:
No query specified
Mysql>

See DATEEXECUTED time fields are August 12 input, but unfortunately my db server due to limited disk, only save the recent binlog, and now the first binlog on the master database is the wrong mysql-bin.000152

7. check the binlog of the slave database to see if this record has been inserted recently.

[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 -- 1 root 0 Sep 3 :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 -- 1 root 0 Sep 3 :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 -- 1 root 0 Sep 3 :49 rd3.log
[Root @ eanshlt2mydbc004db002 tmp] #
Null. The third log is not input.

[Root @ eanshlt2mydbc004db002 tmp] # mysqlbinlog mysql-bin.004271> 4.log
ERROR: 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 -- 1 root 0 Sep 3 :50 rd4.log
Null. The fourth log is not input.
An error is reported during parsing. the solution is recorded in
Http://www.bitsCN.com/article/47089.htm

[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 -- 1 root 0 Sep 3 18:07 rd5.log
[Root @ eanshlt2mydbc004db002 tmp] #
Null. The fifth log is not input.

After parsing the five logs from the database, we didn't see this disciplined insert operation. The problem is stuck here. 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/G
Query 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: 3434734
Master_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: 1
1 row in set (0.11 sec)
Mysql>

BitsCN.com