I’m writing this down to warn you and to burn it into my memory.
For one of my projects I am using MySQL with InnoDB tables. Which are great, by the way. So I have this really huge table – about 8 GBytes with few million rows – that I want to empty. Without much thinking I start DELETE FROM mytable. And I wait. And wait. And wait. Then I go for a coffee. I come back and wait. Then I wait for some more.
Eventually, in about one hour, I got bored. So, I terminate the query. That was without much thinking too. Can you guess why?
Right. MySQL uses transactions for InnoDB tables. Interrupted query triggered a rollback operation. So, not only I just spent an hour waiting for the stuff to get deleted, but it won’t be deleted afterall.
That made me angry. So, I decided that I should restart MySQL service. I thought that killing MySQL process will make it forget about the whole rollback thing. It didn’t work.
In fact, it made the situation even worth. MySQL service refused to start until the rollback would finish. Or maybe it was repairing table, I am not sure. But it seemed more like a rollback. So for the next thirty minutes, it was fixing stuff, while none of other projects could connect to the database. Other projects were mostly websites that I host on my home server. Very nice.
Of course, while it was working, I was searching for the solution on the Web, mostly in MySQL manuals and forums. I came across a couple of other requests that were exactly like mine, but there were no answers. I then went to #mysql channel on IRC, where the guys told me that they feel sorry for me (how kind), but there was no solution, except to wait. Dang!
After the service came up, I simply removed the table (DROP TABLE mytable) and re-created it empty (CREATE TABLE mytable (...)).  I also found another way of emptying the table, which is TRUNCATE TABLE mytable, although it would behave exactly the same as DELETE FROM TABLE mytable on my version of MySQL (4.something).