mysql主从库同步错误：1062 Error 'Duplicate entry '1438019' for key 'PRIMARY'' on query

mysql主从库在同步时会发生1062 Last_SQL_Error: Error ‘Duplicate entry ‘的问题：

显然这个问题是因为插入重复主键导致从库不工作了，错误消息如下：

mysql>show slave status\G;
***************************1.row ***************************
Slave_IO_State:Waitingformaster to send event
Master_Host:192.168.10.100
Master_User:slave_user
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.000773
Read_Master_Log_Pos:63325
Relay_Log_File:server122-relay-bin.000002
Relay_Log_Pos:165661
Relay_Master_Log_File:mysql-bin.000771
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 '1438019' for key 'PRIMARY''on query.Defaultdatabase:'otrs'.Query:'INSERT INTO ticket (tn, title, create_time_unix, queue_id, ticket_lock_id, user_id, group_id, ticket_priority_id, ticket_state_id, ticket_answered, escalation_start_time, timeout, valid_id, create_time, create_by, change_time, change_by) VALUES ('2012061310001851', 'Yourorder ORD201205A000016 was bounced back', 1339585744, 44, 1, 43, 1, 3, 4, 0, 1339585744, 0, 1, current_timestamp, 43, current_timestamp, 43)'
Skip_Counter:0
Exec_Master_Log_Pos:41969067
Relay_Log_Space:625695
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 '1438019' for key 'PRIMARY''on query.Defaultdatabase:'otrs'.Query:'INSERT INTO ticket (tn, title, create_time_unix, queue_id, ticket_lock_id, user_id, group_id, ticket_priority_id, ticket_state_id, ticket_answered, escalation_start_time, timeout, valid_id, create_time, create_by, change_time, change_by) VALUES ('2012061310001851', 'Yourorder ORD201205A000016 was bounced back', 1339585744, 44, 1, 43, 1, 3, 4, 0, 1339585744, 0, 1, current_timestamp, 43, current_timestamp, 43)'
1row inset(0.00sec)

ERROR:
Noquery specified

 

解决的办法是在从库上执行:

mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;

 

上面的方法可以解决问题，还有一种解决问题的办法是通过修改mysql的配置文件，让从库的同步线程忽略这个错误，方法：

修改mysql配置文件 /etc/my.cnf 在 [mysqld]下加一行 slave_skip_errors = 1062 ,保存.重启mysql. mysql slave可以正常同步了.

http://outofmemory.cn/code-snippet/3177/mysql-zhucong-library-clock-error-：-1062-Error-Duplicate-entry-1438019-for-key-PRIMARY-on-query

 

更多技术文章请看我的网站：http://outofmemory.cn/