Last_Errno:1062，Last_Error:ErrorDuplicateentry

1从库报错信息：

mysql>showslavestatus\G
***************************1.row***************************
              Slave_IO_State:Waitingformastertosendevent
                 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"Duplicateentry"250.1.1-rding-changelogs/myIDENTITY/250/xxxx.xml"forkey"PRIMARY""onquery.Defaultdatabase:"identity".Query:"INSERTINTO`DATABASECHANGELOG`(`DATEEXECUTED`,`AUTHOR`,`xxxx`,`DESCRIPTION`,`COMMENTS`,`MD5SUM`,`ID`,`FILENAME`)VALUES(NOW(),"rding","1.9.3","CustomSQL","","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"Duplicateentry"250.1.1-rding-changelogs/myIDENTITY/250/xxxx.xml"forkey"PRIMARY""onquery.Defaultdatabase:"identity".Query:"INSERTINTO`DATABASECHANGELOG`(`DATEEXECUTED`,`AUTHOR`,`xxxx`,`DESCRIPTION`,`COMMENTS`,`MD5SUM`,`ID`,`FILENAME`)VALUES(NOW(),"rding","1.9.3","CustomSQL","","4ac9fbf5222bc344362ccdecbc072","250.1.1","changelogs/myIDENTITY/250/xxxx.xml")"
 Replicate_Ignore_Server_Ids:
            Master_Server_Id:1
1rowinset(0.00sec)

2看表结构

mysql>showcreatetableDATABASECHANGELOG;
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table            |CreateTable                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|DATABASECHANGELOG|CREATETABLE`databasechangelog`(
 `ID`varchar(63)NOTNULL,
 `AUTHOR`varchar(63)NOTNULL,
 `FILENAME`varchar(200)NOTNULL,
 `DATEEXECUTED`datetimeNOTNULL,
 `MD5SUM`varchar(32)DEFAULTNULL,
 `DESCRIPTION`varchar(255)DEFAULTNULL,
 `COMMENTS`varchar(255)DEFAULTNULL,
 `TAG`varchar(255)DEFAULTNULL,
 `xxxx`varchar(10)DEFAULTNULL,
 PRIMARYKEY(`ID`,`AUTHOR`,`FILENAME`)坑爹的表设计结构，不是我喜欢的风格
)ENGINE=InnoDBDEFAULTCHARSET=utf8|
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset(0.00sec)

3看已经存在的数据

mysql>select*fromDATABASECHANGELOG whereAUTHOR="rding";
+---------+--------+-----------------------------------------+---------------------+----------------------------------+-------------+------------------------------------------+------+-----------+
|ID     |AUTHOR|FILENAME                               |DATEEXECUTED       |MD5SUM                          |DESCRIPTION|COMMENTS                                |TAG |xxxx|
+---------+--------+-----------------------------------------+---------------------+----------------------------------+-------------+------------------------------------------+------+-----------+
|250.1.1|rding |changelogs/myIDENTITY/250/xxxx.xml|2013-08-1220:41:22|4ac9fbf5222bc344362ccdecbc072   |CustomSQL |                                         |NULL|1.9.3    |
|250.1.2|rding |changelogs/myIDENTITY/250/xxxx.xml|2013-08-1220:41:22|8463e1cf4ba029e3ace675d3e69a71d2|CustomSQL |Createnewtableforemailchangerecord|NULL|1.9.3    |
+---------+--------+-----------------------------------------+---------------------+----------------------------------+-------------+------------------------------------------+------+-----------+
2rowsinset(0.00sec)

4看binlog，在Relay_Master_Log_File:mysql-bin.000152，去主库找这个binlog解析出来

解析
[root@xxxx0402tmp]#mysqlbinlogmysql-bin.000152>a152.log
搜索包含"rding"字符串的语句，因为这个是主键字段之一，所以检索起来应该比较容易。
[root@xxxx0402tmp]#grepa152.log"rding">rd.log
grep:rding:Nosuchfileordirectory
[root@xxxx0402tmp]#grep "rding"a152.log>rd.log
[root@xxxx0402tmp]#ll

[root@xxxx0402tmp]#morerd.log
INSERTINTO`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")
INSERTINTO`DATABASECHANGELOG`(`DATEEXECUTED`,`AUTHOR`,`xxxx`,`DESCRIPTION`,`COMMENTS`,`MD5SUM`,`ID`,`FILENAME`)VALUES(NOW(),"rding","1.9.3","Custom
 SQL","Createnewtableforemailchangerecord","8463e1cf4ba029e3ace675d3e69a71d2","250.1.2","changelogs/myIDENTITY/250/xxxx.xml")
主库上面只有一条insertsql语句。

5再去看从库的relaylog日志Relay_Log_File:mysql-relay-bin.000455

[root@eanshlt2mydbc004db002data]#cpmysql-relay-bin.000455/tmp
[root@eanshlt2mydbc004db002data]#cd/tmp
[root@eanshlt2mydbc004db002tmp]#mysqlbinlogmysql-relay-bin.000455>relay.log
[root@eanshlt2mydbc004db002tmp]#
[root@eanshlt2mydbc004db002tmp]#grep "rding"relay.log>rd.log
[root@eanshlt2mydbc004db002tmp]#morerd.log
INSERTINTO`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")
INSERTINTO`DATABASECHANGELOG`(`DATEEXECUTED`,`AUTHOR`,`xxxx`,`DESCRIPTION`,`COMMENTS`,`MD5SUM`,`ID`,`FILENAME`)VALUES(NOW(),"rding","1.9.3","Custom
 SQL","Createnewtableforemailchangerecord","8463e1cf4ba029e3ace675d3e69a71d2","250.1.2","changelogs/myIDENTITY/250/xxxx.xml")

奇怪了，2边的都是一样子的。这个错误怎么判断？

6去看下主从关于这个数据记录的录入时间。

从库上面：
mysql>select*fromDATABASECHANGELOGwhereAUTHOR="rding"andID="250.1.1"andFILENAME="changelogs/myIDENTITY/250/xxxx.xml"\G;
***************************1.row***************************
         ID:250.1.1
     AUTHOR:rding
   FILENAME:changelogs/myIDENTITY/250/xxxx.xml
DATEEXECUTED:2013-08-1220:41:22
     MD5SUM:4ac9fbf5222bc344362ccdecbc072
 DESCRIPTION:CustomSQL
   COMMENTS:
        TAG:NULL
  xxxx:1.9.3
1rowinset(0.00sec)

ERROR:
Noqueryspecified
mysql>

主库上面：
mysql>select*fromDATABASECHANGELOGwhereAUTHOR="rding"andID="250.1.1"andFILENAME="changelogs/myIDENTITY/250/xxxx.xml"\G;
***************************1.row***************************
         ID:250.1.1
     AUTHOR:rding
   FILENAME:changelogs/myIDENTITY/250/xxxx.xml
DATEEXECUTED:2013-08-1219:54:29
     MD5SUM:4ac9fbf5222bc344362ccdecbc072
 DESCRIPTION:CustomSQL
   COMMENTS:
        TAG:NULL
  xxxx:1.9.3
1rowinset(0.02sec)
ERROR:
Noqueryspecified
mysql>

看DATEEXECUTED时间字段都是8月12日录入的，可惜我的dbserver由于磁盘有限，只保存了近期的binlog，而且现在主库上面最早的binlog就是出错的那个mysql-bin.000152

7最后一招，去看从库的binlog，看是否近期有人insert了这条记录

[root@eanshlt2mydbc004db002data]#cpmysql-bin.004*/tmp/
[root@eanshlt2mydbc004db002tmp]#mysqlbinlogmysql-bin.004268>1.log
[root@eanshlt2mydbc004db002tmp]#grep "rding"1.log>rd1.log
[root@eanshlt2mydbc004db002tmp]#llrd1.log
-rw-r--r--1rootroot0Sep 317:47rd1.log
空的，第一个日志没有录入操作

[root@eanshlt2mydbc004db002tmp]#mysqlbinlogmysql-bin.004269>2.log
[root@eanshlt2mydbc004db002tmp]#grep "rding"2.log>rd2.log
[root@eanshlt2mydbc004db002tmp]#llrd2.log
-rw-r--r--1rootroot0Sep 317:48rd2.log
[root@eanshlt2mydbc004db002tmp]#
空的，第二个日志没有录入操作

[root@eanshlt2mydbc004db002tmp]#mysqlbinlogmysql-bin.004270>3.log
[root@eanshlt2mydbc004db002tmp]#grep "rding" 3.log>rd3.log
[root@eanshlt2mydbc004db002tmp]#llrd3.log
-rw-r--r--1rootroot0Sep 317:49rd3.log
[root@eanshlt2mydbc004db002tmp]#
空的，第三个日志没有录入操作

解析了从库的5个日志，都没有看到这条纪律的insert操作，问题到此卡住了，原因何在？这边开发的兄弟们已经在催了，我只要skip之后从库重新做了。

mysql>stopslave;
setglobalsql_slave_skip_counter=1;
startslave;
showslavestatus\G
QueryOK,0rowsaffected(0.09sec)

mysql>setglobalsql_slave_skip_counter=1;
QueryOK,0rowsaffected(0.00sec)

mysql>startslave;
QueryOK,0rowsaffected(0.00sec)
mysql>showslavestatus\G
***************************1.row***************************
              Slave_IO_State:Waitingformastertosendevent
                 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
1rowinset(0.11sec)
mysql>