Last_errno:1062,last_error:error Duplicate Entry_mysql

Source: Internet
Author: User
Online environment I have never encountered 1062 of the problem, the test environment development environment is constantly similar problems, in the past in order to catch the time is skip or directly redo, this will have time, just to check the problem





1 error message from library:

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 ', ' AUTHOR ', ' xxxx ', ' DESCRIPTION ', ' COMMENTS ', ' md5sum ', ' ID ', ' FI Lename ') VALUES (now (), ' rding ', ' 1.9.3 ', ' Custom SQL ', ', ' 4ac9fbf5222bc344362ccdecbc072 ', ' 250.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 ', ' AUTHOR ', ' xxxx ', ' DESCRIPTION ', ' COMMENTS ', ' md5sum ', ' ID ', ' FI Lename ') VALUES (now (), ' rding ', ' 1.9.3 ', ' Custom SQL ', ', ' 4ac9fbf5222bc344362ccdecbc072 ', ' 250.1.1 ', ' changelogs/ Myidentity/250/xxxx.xml ') '


Replicate_ignore_server_ids:


Master_server_id:1


1 row in Set (0.00 sec)

2 View Table Structure

Mysql> Show CREATE TABLE Databasechangelog;


+-------------------+------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------+


| table             | Create table                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |


+-------------------+------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------+


| Databasechangelog | CREATE TABLE ' Databasechangelog ' (


' ID ' varchar () not NULL,


' AUTHOR ' varchar not NULL,


' FILENAME ' varchar not NULL,


' dateexecuted ' datetime not NULL,


' md5sum ' varchar DEFAULT NULL,


' DESCRIPTION ' varchar (255) DEFAULT NULL,


' COMMENTS ' varchar (255) DEFAULT NULL,


' TAG ' varchar (255) DEFAULT NULL,


' xxxx ' varchar (a) DEFAULT NULL,


PRIMARY key (' ID ', ' AUTHOR ', ' FILENAME ') the table design structure of the pit Dad, not my favorite style


) Engine=innodb DEFAULT Charset=utf8 |


+-------------------+------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------+


1 row in Set (0.00 sec)





3 Look at the data that already exists

Mysql> SELECT * from databasechangelog where AUTHOR = ' rding ';


+---------+--------+-----------------------------------------+---------------------+--------------------------- -------+-------------+------------------------------------------+------+-----------+


| ID | AUTHOR | FILENAME | dateexecuted | md5sum | DESCRIPTION | COMMENTS | TAG | xxxx |


+---------+--------+-----------------------------------------+---------------------+--------------------------- -------+-------------+------------------------------------------+------+-----------+


| 250.1.1 | rding | Changelogs/myidentity/250/xxxx.xml | 2013-08-12 20:41:22 | 4ac9fbf5222bc344362ccdecbc072 |                                          Custom SQL | | NULL | 1.9.3 |


| 250.1.2 | rding | Changelogs/myidentity/250/xxxx.xml | 2013-08-12 20:41:22 | 8463e1cf4ba029e3ace675d3e69a71d2 | Custom SQL | Create new table for email change record | NULL | 1.9.3 |


+---------+--------+-----------------------------------------+---------------------+--------------------------- -------+-------------+------------------------------------------+------+-----------+


2 rows in Set (0.00 sec)

4 See Binlog, in relay_master_log_file:mysql-bin.000152, go to the main library to find this Binlog parse out

Analytical
[root@xxxx0402 tmp]# mysqlbinlog mysql-bin.000152 > A152.log
Search for statements containing ' rding ' strings because this is one of the primary key fields, so it should be easier to retrieve them.
[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 ', ' AUTHOR ', ' xxxx ', ' DESCRIPTION ', ' COMMENTS ', ' md5sum ', ' ID ', ' FILENAME ' VALUES (now (), ' rding ', ' 1.9.3 ', ' Custom
SQL ', ', ' 4ac9fbf5222bc344362ccdecbc072 ', ' 250.1.1 ', ' changelogs/myidentity/250/xxxx.xml '
INSERT into ' databasechangelog ' (' dateexecuted ', ' AUTHOR ', ' xxxx ', ' DESCRIPTION ', ' COMMENTS ', ' md5sum ', ' ID ', ' FILENAME ' VALUES (now (), ' rding ', ' 1.9.3 ', ' Custom
SQL ', ' Create new table for email change record ', ' 8463e1cf4ba029e3ace675d3e69a71d2 ', ' 250.1.2 ', ' changelogs/myidentity/ 250/xxxx.xml ')
There is only one insert SQL statement above the main library.

5 to see the relay log logs from the library relay_log_file: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 ', ' AUTHOR ', ' xxxx ', ' DESCRIPTION ', ' COMMENTS ', ' md5sum ', ' ID ', ' FILENAME ' VALUES (now (), ' rding ', ' 1.9.3 ', ' Custom
SQL ', ', ' 4ac9fbf5222bc344362ccdecbc072 ', ' 250.1.1 ', ' changelogs/myidentity/250/xxxx.xml '
INSERT into ' databasechangelog ' (' dateexecuted ', ' AUTHOR ', ' xxxx ', ' DESCRIPTION ', ' COMMENTS ', ' md5sum ', ' ID ', ' FILENAME ' VALUES (now (), ' rding ', ' 1.9.3 ', ' Custom
SQL ', ' Create new table for email change record ', ' 8463e1cf4ba029e3ace675d3e69a71d2 ', ' 250.1.2 ', ' changelogs/myidentity/ 250/xxxx.xml ')

Strange, 2 sides of the same son. How do you judge this mistake?

6 to see the data recorded by the master and the time of entry.

From the top of the library:
Mysql> SELECT * from Databasechangelog where author= ' rding ' and id= ' 250.1.1 ' 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>

Main Library above:
Mysql> SELECT * from Databasechangelog where author= ' rding ' and id= ' 250.1.1 ' 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 my DB server due to limited disk, only save the recent binlog, and now the main library above the first binlog is the wrong one mysql-bin.000152

7 Last move, look at the Binlog from the library, see if someone recently inserts 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 0 Sep 3 17:47 rd1.log
Empty, the first log has no input operation

[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 0 Sep 3 17:48 rd2.log
[Root@eanshlt2mydbc004db002 tmp]#
Empty, the second log has no input operation

[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 0 Se p  3 17:49 rd3.log
[root@eanshlt2mydbc004db002 tmp]#
is empty, and the third log has no input operation

[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--r--1 Root 0 Sep 3 17:50 rd4.log
Empty, fourth log no entry operation
Here parse the error, the solution, recorded in this
Http://www.jb51.net/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--r--1 Root 0 Sep 3 18:07 rd5.log
[Root@eanshlt2mydbc004db002 tmp]#
Empty, fifth log no entry operation

Parsed from the library 5 logs, did not see this discipline insert operation, the problem to this stuck, why? The brothers who developed this side have been urging, I just skip after the library to redo.

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>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.