Tom Tom - 22 days ago 5
MySQL Question

Fixing "Lock wait timeout exceeded; try restarting transaction" for a 'stuck" Mysql table?

From a script I sent a query like this thousands of times to my local database:

update some_table set some_column = some_value


I forgot to add the where part, so the same column was set to the same a value for all the rows in the table and this was done thousands of times and the column was indexed, so the corresponding index was probably updated too lots of times.

I noticed something was wrong, because it took too long, so I killed the script. I even rebooted my computer since then, but something stuck in the table, because simple queries take a very long time to run and when I try dropping the relevant index it fails with this message:

Lock wait timeout exceeded; try restarting transaction


It's an innodb table, so stuck the transaction is probably implicit. How can I fix this table and remove the stuck transaction from it?

Edit: I solved the problem by dropping the table and restoring it from backup.

Tom Tom
Answer

I solved the problem by dropping the table and restoring it from backup.