# is this a bug? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [December 24, 2014, 3:21am UTC](https://forums.percona.com/t/is-this-a-bug/3965 "2014-12-24T03:21:21Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![zhaogongpo](https://avatars.discourse-cdn.com/v4/letter/z/b9e5f3/32.png) [@zhaogongpo](https://forums.percona.com/u/zhaogongpo)\ **Post date:** [December 24, 2014, 3:21am UTC](https://forums.percona.com/t/is-this-a-bug/3965/1 "2014-12-24T03:21:21Z")
i have met a replication error: the slave server crashed when it started again, I execute the command: “show slave status” \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Slave\_IO\_State: Waiting for master to send event Master\_Host: xx.xx.xx.xx Master\_User: repl Master\_Port: xxxx Connect\_Retry: 60 Master\_Log\_File: mysql-bin.000858 Read\_Master\_Log\_Pos: 379908871 Relay\_Log\_File: relay-bin.000620 Relay\_Log\_Pos: 724847546 Relay\_Master\_Log\_File: mysql-bin.000806 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 ‘1544846670’ for key ‘PRIMARY’’ on query. Default database: ‘xxxxxx\_databasename’. Query: 'INSERT INTO xxx\_tabname (…) VALUES ( 1165305, 17120165, 10, 100301, 81003010003, 2005, 1005, 50, 0, NULL, ‘2013-04-28 18:24:48’, ‘2013-07-28 23:59:59’, 1000, 1000, NULL, 1000, NULL, NULL, 0, NULL, Skip\_Counter: 0 Exec\_Master\_Log\_Pos: 724847400 Relay\_Log\_Space: 56238889769 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 ‘1544846670’ for key ‘PRIMARY’’ on query. Default database: ‘xxxxxx\_databasename’. Query: 'INSERT INTO xxx\_tabname (…) VALUES ( 1165305, 17120165, 10, 100301, 81003010003, 2005, 1005, 50, 0, NULL, ‘2013-04-28 18:24:48’, ‘2013-07-28 23:59:59’, 1000, 1000, NULL, 1000, NULL, NULL, 0, NULL, Replicate\_Ignore\_Server\_Ids: Master\_Server\_Id: 4 1 row in set (0.00 sec) but i found that the slave did not contain a row that the id = 1544846670; then I executed ‘start slave’ and show slave status again: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Slave\_IO\_State: Waiting for master to send event Master\_Host: xx.xx.xx.xx Master\_User: repl Master\_Port: xxxx Connect\_Retry: 60 Master\_Log\_File: mysql-bin.000858 Read\_Master\_Log\_Pos: 379935147 Relay\_Log\_File: relay-bin.000620 Relay\_Log\_Pos: 724847546 Relay\_Master\_Log\_File: mysql-bin.000806 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 ‘1544846671’ for key ‘PRIMARY’’ on query. Default database: ‘xxxxxx\_databasename’. Query: 'INSERT INTO xxx\_tabname (…) VALUES ( 1165306, 14765802804742, 10, 100301, 81003010003, 2005, 1000, 50, 0, NULL, ‘2013-04-28 17:45:12’, ‘2014-04-27 23:59:59’, 6000, 5000, NULL, 5000, NULL, NULL, 0, N Skip\_Counter: 0 Exec\_Master\_Log\_Pos: 724847400 Relay\_Log\_Space: 56238916045 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 ‘1544846671’ for key ‘PRIMARY’’ on query. Default database: ‘dxxxxxx\_databasename’. Query: 'INSERT INTO xxx\_tabname (…) VALUES ( 1165306, 14765802804742, 10, 100301, 81003010003, 2005, 1000, 50, 0, NULL, ‘2013-04-28 17:45:12’, ‘2014-04-27 23:59:59’, 6000, 5000, NULL, 5000, NULL, NULL, 0, N Replicate\_Ignore\_Server\_Ids: Master\_Server\_Id: 4 1 row in set (0.00 sec) the error record became the next one; but I did nothing else! except the “start slave” command then I found that the record(id=‘1544846671’) didnot exist too. Now the slave had been recreated. mysql-error.log: 141211 18:56:11 [ERROR] Slave SQL: Error ‘Duplicate entry ‘1544846670’ for key ‘PRIMARY’’ on query. Default database: ‘xxxx\_databasename’. Query: 'INSERT INTO tabname (…) VALUES ( 1165305, 17120165, 10, 100301, 81003010003, 2005, 1005, 50, 0, NULL, ‘2013-04-28 18:24:48’, ‘2013-07-28 23:59:59’, 1000, 1000, NULL, 1000, NULL, NULL, 141211 18:56:11 [Warning] Slave: Duplicate entry ‘1544846670’ for key ‘PRIMARY’ Error\_code: 1062 141211 18:56:11 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.000806’ position 724847400 141212 9:35:01 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000806’ at position 724847400, relay log ‘/var/lib/mysql/relay-bin.000620’ position: 724847546 141212 9:35:01 [ERROR] Slave SQL: Error ‘Duplicate entry ‘1544846671’ for key ‘PRIMARY’’ on query. Default database: ‘xxxx\_databasename’. Query: 'INSERT INTO tabname (…) VALUES ( 1165306, 14765802804742, 10, 100301, 81003010003, 2005, 1000, 50, 0, NULL, ‘2013-04-28 17:45:12’, ‘2014-04-27 23:59:59’, 6000, 5000, NULL, 5000, NULL, N 141212 9:35:01 [Warning] Slave: Duplicate entry ‘1544846671’ for key ‘PRIMARY’ Error\_code: 1062 141212 9:35:01 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.000806’ position 724847400 141212 9:35:56 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000806’ at position 724847400, relay log ‘/var/lib/mysql/relay-bin.000620’ position: 724847546 141212 9:35:56 [ERROR] Slave SQL: Error ‘Duplicate entry ‘1544846672’ for key ‘PRIMARY’’ on query. Default database: ‘xxxx\_databasename’. Query: 'INSERT INTO tabname (…) VALUES ( 1165306, 36659631, 10, 100301, 81003010003, 2005, 1000, 50, 0, NULL, ‘2013-04-28 17:45:15’, ‘2014-04-27 23:59:59’, 6000, 5000, NULL, 5000, NULL, NULL, 141212 9:35:56 [Warning] Slave: Duplicate entry ‘1544846672’ for key ‘PRIMARY’ Error\_code: 1062 141212 9:35:56 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.000806’ position 724847400 ---
**Author:** ![zhaogongpo](https://avatars.discourse-cdn.com/v4/letter/z/b9e5f3/32.png) [@zhaogongpo](https://forums.percona.com/u/zhaogongpo)\ **Post date:** [December 24, 2014, 3:24am UTC](https://forums.percona.com/t/is-this-a-bug/3965/2 "2014-12-24T03:24:36Z")
Server version: 5.5.35-rel33.0-log Percona Server with XtraDB (GPL), Release rel33.0, Revision 611 ---
**Author:** ![psong](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/psong/32/16894_2.png) [@psong](https://forums.percona.com/u/psong)\ **Post date:** [December 25, 2014, 11:04pm UTC](https://forums.percona.com/t/is-this-a-bug/3965/3 "2014-12-25T23:04:57Z")
This is surprising. The INSERT statement is truncated, does it include the PK value or is the PK an auto\_increment column? If you check the rows in the slave for PK 1544846670, 1544846671, are those the rows inserted by the statements that once failed? Can you run SHOW CREATE TABLE on both master and slave? Also SELECT FROM WHERE pk\_column \> 1544846672; ---
**Author:** ![zhaogongpo](https://avatars.discourse-cdn.com/v4/letter/z/b9e5f3/32.png) [@zhaogongpo](https://forums.percona.com/u/zhaogongpo)\ **Post date:** [December 26, 2014, 12:50am UTC](https://forums.percona.com/t/is-this-a-bug/3965/4 "2014-12-26T00:50:58Z")
> [@psong;29102](#): > > This is surprising. The INSERT statement is truncated, does it include the PK value or is the PK an auto\_increment column? If you check the rows in the slave for PK 1544846670, 1544846671, are those the rows inserted by the statements that once failed? Can you run SHOW CREATE TABLE on both master and slave? Also SELECT FROM > > WHERE pk\_column \> 1544846672; > > The primary key is auto\_increment , and PK 1544846670, 1544846671 did not exists in the slave. > but I found that the table’s auto\_increment changed: > > when PK 1544846670 faild , the table’s auto\_increment number of the slave was 1544846671; > but after I executed “start slave”, > the auto\_increment number changed to 1544846672; > > but the PK 1544846670 or PK 1544846671 did not insert into the slave. > > I tried change the auto\_increment number to the corrent filed PK, > but it maked no sense. ---
**Author:** ![psong](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/psong/32/16894_2.png) [@psong](https://forums.percona.com/u/psong)\ **Post date:** [December 26, 2014, 3:34am UTC](https://forums.percona.com/t/is-this-a-bug/3965/5 "2014-12-26T03:34:33Z")
The fact that auto\_increment number increased and a different row showed in the SHOW SLAVE STATUS suggested that the previous row was inserted, but you didn’t see the row in the table. Is there any trigger defined or other jobs that might interfere? I’d suggest to track queries: > show slave status\G > select \* from where pk\_col \> 1544846671; > set global general\_log = 1; > stop slave; > start slave; > show slave status\G > select \* from where pk\_col \> 1544846671; > $ cat /path/to/datadir/hostname.log To stop capturing general queries: > set global general\_log = 0; > stop slave; > start slave; ---
**Author:** ![zhaogongpo](https://avatars.discourse-cdn.com/v4/letter/z/b9e5f3/32.png) [@zhaogongpo](https://forums.percona.com/u/zhaogongpo)\ **Post date:** [December 26, 2014, 5:09am UTC](https://forums.percona.com/t/is-this-a-bug/3965/6 "2014-12-26T05:09:21Z")
> [@psong;29106](#): > > The fact that auto\_increment number increased and a different row showed in the SHOW SLAVE STATUS suggested that the previous row was inserted, but you didn’t see the row in the table. Is there any trigger defined or other jobs that might interfere? I’d suggest to track queries: > > > show slave status\G > > select \* from where pk\_col \> 1544846671; > > set global general\_log = 1; > > stop slave; > > start slave; > > show slave status\G > > select \* from where pk\_col \> 1544846671; > > $ cat /path/to/datadir/hostname.log > > To stop capturing general queries: > > > set global general\_log = 0; > > stop slave; > > start slave; Thank you for reply, but I am sure there is no trigger or other jobs , the database had been recreated with xtrabackup., and the slave returns to normal. I think it maybe a bug.