Slave lock wait timeout and duplicate entry errors
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Incomplete
|
Undecided
|
Unassigned | ||
5.6 |
Incomplete
|
Undecided
|
Unassigned |
Bug Description
Hi, we've recently upgraded our database to 5.6.17 and notice what may be a bug.
Server version: 5.6.17-
Summary: We have some reporting queries running on a slave that cause an occasional "Lock wait timeout exceeded; try restarting transaction" (even before upgrading to 5.6) .We are in the process of moving reporting to a different architecture to avoid such timeouts. However, the problem is - while in 5.5, the slave stopped after trying slave_transacti
GENERAL LOG:
**************
Comment: "Lock wait timeout exceeded, try restarting transaction" occurs on the following UPDATE statement:
140703 20:18:06 183288 Query UPDATE table3
Comment: "Transaction is retried":
Comment: SQL thread stops with "Duplicate entry":
ERROR LOG:
***********
2014-07-03 20:18:06 15493 [Warning] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'test'. Query: 'UPDATE table3
2014-07-03 20:18:06 15493 [ERROR] Slave SQL: Error 'Duplicate entry '97102448' for key 'PRIMARY'' on query. Default database: 'thinkgeek'. Query: 'INSERT INTO `table2`( queue_id, data, created )
2014-07-03 20:18:06 15493 [Warning] Slave: Duplicate entry '97102448' for key 'PRIMARY' Error_code: 1062
2014-07-03 20:18:06 15493 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'bin-log.003698' position 689390092
DDL for table2:
+------
| Field | Type | Null | Key | Default | Extra |
+------
| queue_element_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| queue_id | int(11) | NO | MUL | NULL | |
| data | text | YES | | NULL | |
| lock_time | int(10) unsigned | YES | | NULL | |
| requeue_count | int(3) unsigned | YES | MUL | 0 | |
| created | int(10) unsigned | NO | | 0 | |
+------
6 rows in set (0.00 sec)
It seems like when a timeout occurs and the last statement in transaction is rolled back (as stated here http://
We never had --innodb_
I wonder if both table1 and table2 in the above are InnoDB? Check http:// bugs.mysql. com/bug. php?id= 42165
Please, send your my.cnf file content also. I wonder if MTS is enabled. Check http:// bugs.mysql. com/bug. php?id= 68465