Aug 25
Free up space again after killing a long running MySQL "Alter Table" statement
Sometimes, tables grow big and changing the structure of these tables with ALTER TABLE statements can take a loong time. (As MySQL first copies the full table with the changed schema and then interchanges the copy with the primary one)
Or your server might run out of space while executing the alter table command. Usually, MySQL should free up used scrap space after the thread executing the statement has been killed - but in my case it didn't.
By looking into my my.cnf I found where my MySQL daemon stores the actual table data, usually in /var/lib/mysql/<table name>
There, MySQL create a number of hash-prepended temp files, "#sql-" files, something like:
#sql-3681_10a37.frm
#sql-3681_10a37.MYD
#sql-3681_10a37.MYI
Now, is it save to remove these files after MySQL has stopped using them? Well, I found it is. But probably make sure you've got backups of the original table, and make sure the files have stopped growing, and restart MySQL for good measure after you've moves these files out of the way.