What happens if I kill the huge MySQL InnoDb DELETE Query?

Question

I am currently running a DELETE query, which takes a lot longer than expected (already 10 hours!). I would like to kill it using phpmyadmin processes, however I am worried about what might happen. Will it roll back automatically it takes a lot of time? The current status of the request indicates "update".

Answer 1

It depends on what stage your request is right now. But usually the rollback takes about the same time, sometimes even more than the original operation.

In accordance with point 2 of this document , this is really not recommended.

In addition, be sure to check your version of MySQL, as it has a VERY nasty bug with the rollback of delete / update requests in some versions of this article.

Restarting / killing the MySQL process will not help, as rollback will resume after the restart.

Answer 2

Rule of thumb:

Just drop it yourself and don’t even think about restarting the database, because it will resume after rebooting, but it’s worse that your database will not be available in the meantime.

Rolling back huge data (i.e., millions of rows) will be significantly slower than the commit operation, and even slower if InnoDB concurrent transactions occur in the same database.