I am issuing some long-running delete queries to some databases, and I am getting the following error:
Last_Error: Could not execute Write_rows event on table DATABASE.TABLENAME; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.005698, end_log_pos 91495479
I have set "innodb_lock_wait_timeout" to high value such as the query will never hit the timeout.
SELECT @@GLOBAL.innodb_lock_wait_timeout; +-----------------------------------+ | @@GLOBAL.innodb_lock_wait_timeout | +-----------------------------------+ | 1000000 | +-----------------------------------+
Any suggestions for how to avoid this error? I need to delete large amounts of data so I want to issue queries in a loop without supervision. It feels a bit like a mysql bug. MySQL version is 5.6.
EDIT: Here's the exact query. The data is sharded client-side based on the function shown (
MOD(MOD(user_id,65536), shard_count) != shard_id). The majority of the data in the DBs shouldn't be there, so the delete is big, and it's cascading down to tables that are >300GB (where each user might have an inventory of 200 items, for example)
DELETE FROM User WHERE account_type = "type1" AND user_id IN ( SELECT b.user_id FROM ( SELECT a.account_type, a.user_id FROM ( SELECT account_type, user_id FROM User WHERE account_type = "type1" AND MOD(MOD(user_id,65536), 20) != 9 LIMIT 1000000 ) a ) b )
EDIT: As requested, output of
SHOW CREATE TABLE
mysql> SHOW CREATE TABLE User \G; *************************** 1. row *************************** Table: User Create Table: CREATE TABLE `User` ( `account_type` varchar(20) NOT NULL DEFAULT '', `user_id` bigint(20) unsigned NOT NULL, `mod_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `team_id` int(10) unsigned DEFAULT NULL, `shard_key` int(10) unsigned DEFAULT '0', PRIMARY KEY (`account_type`,`user_id`), KEY `PLAY_SHKEY_I` (`shard_key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
EDIT: I believe at this point that it was down to a programming error, because I wasn't calling
commit() from the python client until a really big loop completed, so I think there was a huge ongoing transaction that did not commit ever (because it never made it all the way through the loop)