Last_Errno:?1062，Last_Error:?Error?Duplicate?entry
線(xiàn)上環(huán)境我從來(lái)沒(méi)有碰到過(guò)1062的問(wèn)題，測(cè)試環(huán)境開(kāi)發(fā)環(huán)境不停的出現(xiàn)類(lèi)似問(wèn)題，以往為了趕時(shí)間都是skip或者直接重新做，這會(huì)有時(shí)間，就好好去查查問(wèn)題所在
1 從庫(kù)報(bào)錯(cuò)信息：
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`, `FILENAME`) 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`, `FILENAME`) 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 看表結(jié)構(gòu)
mysql> show create table DATABASECHANGELOG;
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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`)表設(shè)計(jì)結(jié)構(gòu)，不是我喜歡的風(fēng)格
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3 看已經(jīng)存在的數(shù)據(jù)
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 看binlog，在Relay_Master_Log_File: mysql-bin.000152，去主庫(kù)找這個(gè)binlog解析出來(lái)
解析
[root@xxxx0402 tmp]# mysqlbinlog mysql-bin.000152 > a152.log
搜索包含'rding'字符串的語(yǔ)句，因?yàn)檫@個(gè)是主鍵字段之一，所以檢索起來(lái)應(yīng)該比較容易。
[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')
主庫(kù)上面只有一條insert sql語(yǔ)句。
5 再去看從庫(kù)的relay log日志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')
奇怪了，2邊的都是一樣子的。這個(gè)錯(cuò)誤怎么判斷？
6 去看下主從關(guān)于這個(gè)數(shù)據(jù)記錄的錄入時(shí)間。
從庫(kù)上面：
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>
主庫(kù)上面 ：
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>
看DATEEXECUTED時(shí)間字段都是8月12日錄入的，可惜我的db server由于磁盤(pán)有限，只保存了近期的binlog，而且現(xiàn)在主庫(kù)上面最早的binlog就是出錯(cuò)的那個(gè)mysql-bin.000152
7 最后一招，去看從庫(kù)的binlog，看是否近期有人insert了這條記錄
[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
空的，第一個(gè)日志沒(méi)有錄入操作
[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]#
空的，第二個(gè)日志沒(méi)有錄入操作
[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]#
空的，第三個(gè)日志沒(méi)有錄入操作
[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 root 0 Sep 3 17:50 rd4.log
空的，第四個(gè)日志沒(méi)有錄入操作
這里解析報(bào)錯(cuò)了，解決方案，記錄在如此
//www.dbjr.com.cn/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 root 0 Sep 3 18:07 rd5.log
[root@eanshlt2mydbc004db002 tmp]#
空的，第五個(gè)日志沒(méi)有錄入操作
解析了從庫(kù)的5個(gè)日志，都沒(méi)有看到這條紀(jì)律的insert操作，問(wèn)題到此卡住了，原因何在？這邊開(kāi)發(fā)的兄弟們已經(jīng)在催了，我只要skip之后從庫(kù)重新做了。
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>
相關(guān)文章
MySQL索引失效十種場(chǎng)景與優(yōu)化方案
這篇文章主要介紹了MySQL索引失效十種場(chǎng)景與優(yōu)化方案，文中有詳細(xì)的代碼示例供參考閱讀，感興趣的朋友可以看一下2023-05-05
這篇文章主要介紹了MySQL多表查詢(xún)相關(guān)知識(shí),今天我們學(xué)習(xí)要對(duì)多張表進(jìn)行相關(guān)操作，相比較于單一的表來(lái)說(shuō)，多張表操作相對(duì)復(fù)雜一些，本文給大家介紹的非常詳細(xì)，需要的朋友可以參考下2023-06-06
這篇文章主要為大家整理了mysql常用命令匯總,連接Mysql、修改mysql密碼、數(shù)據(jù)庫(kù)的創(chuàng)建刪除等，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-11-11
這篇文章主要介紹了如何將MySQL去重操作優(yōu)化到極致,本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2019-08-08
mysql5.x升級(jí)到mysql5.7后導(dǎo)入之前數(shù)據(jù)庫(kù)date出錯(cuò)的快速解決方法
這篇文章主要介紹了mysql5.x升級(jí)到mysql5.7后導(dǎo)入之前數(shù)據(jù)庫(kù)date出錯(cuò)的快速解決方法,非常不錯(cuò)，具有參考借鑒價(jià)值,需要的朋友可以參考下2016-09-09
MYSQL的select 學(xué)習(xí)筆記...2007-02-02
這篇文章主要介紹了在CentOS上安裝phpMyAdmin的教程,phpMyAdmin是一款借助PHP腳本來(lái)操作MySQL的工具,非常具有人氣,需要的朋友可以參考下2015-06-06
深入了解MySQL中分區(qū)表的原理與企業(yè)級(jí)實(shí)戰(zhàn)
本文詳細(xì)講解什么是分區(qū)表，分區(qū)表增刪改查的工作原理以及分區(qū)表的實(shí)戰(zhàn)，分區(qū)表的場(chǎng)景有哪些，哪些場(chǎng)景不建議用分區(qū)表，并列舉出六點(diǎn)使用分區(qū)表的誤區(qū)，需要的可以參考一下2022-11-11
CentOS下編寫(xiě)shell腳本來(lái)監(jiān)控MySQL主從復(fù)制的教程
這篇文章主要介紹了在CentOS系統(tǒng)下編寫(xiě)shell腳本來(lái)監(jiān)控主從復(fù)制的教程,文中舉了兩個(gè)發(fā)現(xiàn)故障后再次執(zhí)行復(fù)制命令的例子,需要的朋友可以參考下2015-12-12
淺談mysql 樹(shù)形結(jié)構(gòu)表設(shè)計(jì)與優(yōu)化
在諸多的管理類(lèi)，辦公類(lèi)等系統(tǒng)中，樹(shù)形結(jié)構(gòu)展示隨處可見(jiàn)，本文主要介紹了mysql 樹(shù)形結(jié)構(gòu)表設(shè)計(jì)與優(yōu)化，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-09-09