3

I had a very long query running that was causing everything to get stuck. (was running for around 24 hours before being killed) INSERT INTO tbl_new SELECT * FROM tbl_old

So I killed it. Now I don't really need tbl_new and it was empty before. The query is now stuck at query end stage which I assume means he's doing a rollback. Any way for me to avoid it?

If I DROP TABLE tbl_new will that stop the rollback

1 Answer 1

1

Yes in InnoDB killing the session is causing the rollback and the the rollback can take even more time than the actual insert command issued on the table.

Refer the following:

Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times as long to perform as the corresponding insert. Killing the database process does not help because the rollback starts again on server startup.

So you can use the InnoDb Recovery:

You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

Hope this solves your problem.

2
  • So if I just run the DROP table while the rollback is happening it will wait this the rollback is complete?
    – Noam
    Aug 30, 2016 at 11:54
  • Yes it will wait until the rollback is complete because the rollback is itself using the table and you are trying to DROP the table on which an operation is already under progress. DROP will not stop the rollback. So you can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback. Aug 30, 2016 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.