- 浏览: 579400 次
文章分类
最新评论
Last_Errno: 1062,Last_Error: Error 'Duplicate entry '...' for key 'PRIMARY'' on query. 的详细分析过程
线上环境我从来没有碰到过1062的问题,测试环境开发环境不停的出现类似问题,以往为了赶时间都是skip或者直接重新做,这会有时间,就好好去查查问题所在。
1 从库报错信息:
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 看表结构
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`) 坑爹的表设计结构,不是我喜欢的风格
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3 看已经存在的数据
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,去主库找这个binlog解析出来
解析
[root@xxxx0402 tmp]# mysqlbinlog mysql-bin.000152 > a152.log
搜索包含'rding'字符串的语句,因为这个是主键字段之一,所以检索起来应该比较容易。
[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')
主库上面只有一条insert sql语句。
5 再去看从库的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边的都是一样子的。这个错误怎么判断?
6 去看下主从关于这个数据记录的录入时间。
从库上面:
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>
主库上面 :
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时间字段都是8月12日录入的,可惜我的db server由于磁盘有限,只保存了近期的binlog,而且现在主库上面最早的binlog就是出错的那个mysql-bin.000152
7 最后一招,去看从库的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
空的,第一个日志没有录入操作
[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]#
空的,第二个日志没有录入操作
[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]#
空的,第三个日志没有录入操作
[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
空的,第四个日志没有录入操作
这里解析报错了,解决方案,记录在如此
http://blog.csdn.net/mchdba/article/details/11011229
[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]#
空的,第五个日志没有录入操作
解析了从库的5个日志,都没有看到这条纪律的insert操作,问题到此卡住了,原因何在?这边开发的兄弟们已经在催了,我只要skip之后从库重新做了。
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>
相关推荐
Last_Errno: 1062,Last_Error: Error Duplicate entry ... for key PRIMARY
Last_SQL_Error: Error ‘Table ‘spider.tb_city_population_rank’ doesn’t exist’ on query. Default database: ‘spider’. Query: ‘alter table tb_city_population_rank discard tablespace’ Last_SQL_...
Mysql主从同步的Last_IO_Errno:1236错误是什么原因呢,我们要如何来解决这个问题呢?下面和小编一起来看看关于此问题的记录与解决办法。 [removed]ec(2);[removed] 从服务器错误代码: Last_IO_Errno: 1236 Last...
* If localtime returns NULL, return an error. */ if ( _localtime64_s(&tbtemp;, &tmptm1;) != 0 ) goto err_mktime; /* * Now must compensate for DST. The ANSI rules are to use the * passed-in tm_...
今天在测试mysql是否同步时,检查数据库发现一台MySQL Slave未和主机同步,查看Slave状态: mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 1062 …. Seconds_Behind_Master:...
OpenSSL SSL_read: SSL_ERROR_SYSCALL, errno 54 这个错误是因为网络太慢,下载virtualbox.box失败,可以换个网络再试一下。 如果网络实在太慢的话,可以试试下面的方法,我们可以手动下载virtualb
前几天帮同事解决一个案例,在主从复制环境下,从库上的MySQL版本号是5.5.5,... Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for t
官方离线安装包,亲测可用
树莓派4B使用 Adafruit_PCA9685 报错IOError: [Errno 121] Remote I/O error解决办法 首先,确保已经下载了python2或python3的Adafruit_PCA9685库,然后要明白,报该错原因是:Adafruit_PCA9685无法找到外部的硬件,...
linux 系统中的gpm rpm包,gpm-libs-1.20.7-6.el7.x86_64.rpm
使用packstack安装redhat openstack10,报错:Error appeared during Puppet run: x.x.x.x_controller.pp ,经检查日志,原因是python-greenlet包未安装,且版本需要大于.0.3.1 安装此包,就ok啦。
yum install libssh2-devel的时候提示如下的信息,安装... libssh2-devel-1.4.2-2.el6_7.1.x86_64: failure: Packages/libssh2-devel-1.4.2-2.el6_7.1.x86_64.rpm from c6-media: [Errno 256] No more mirrors to try.
Error Downloading Packages: OpenIPMI-devel-2.0.16-14.el6.x86_64: failure: Packages/OpenIPMI-devel-2.0.16-14.el6.x86_64.rpm from c6-media: [Errno 256] No more mirrors to try. libssh2-devel-1.4.2-2....
用户Linux系统的net-tools-2.0-0.25.20131004git.el7.x86_64.rpm文件
官方离线安装包,测试可用。使用rpm -ivh [rpm完整包名] 进行安装
官方离线安装包,亲测可用。使用rpm -ivh [rpm完整包名] 进行安装
leetcode 答案 【toc】 git_collect_sub 个人收集的有用项目,使用git ...SSL_ERROR_SYSCALL, errno 54 fatal: the remote end hung up unexpectedly fatal: early EOF fatal: index-pack failed git