Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

MySQL 5.6.10 on Windows 7.

I'm cleaning up and paring down a large INNODB table. Its .ibd file is originally 22 gigs. While I was in the middle of an ALTER, dropping some columns, the mysqld.exe process died.

Upon restarting and starting up mysqld, I noticed a lot of disk activity (~10mb/s) from mysqld, that has been going on for around 15 minutes so far. Also, in my data folder, there are files with names like "#sql-7b8_3.frm".

Could someone shed light on what's going on?

share|improve this question
    
Is the server back up and accepting connections again, or not so much? What do you see in the mysql error log, from when the "process died" and from now? It could be in crash recovery, it could be rolling back incomplete transactions, but it's a bit hard to say based on the information provided so far. –  Michael - sqlbot Feb 11 at 21:23
    
The server came up and was accepting connections, but queries ran very slow due to all the disk activity. Unfortunately, error logging was off so I couldn't see if or what caused the crash. Error logging is off by default on a windows install. I won't overlook that again. I'm pretty sure it was crash recovery. Sorry, I'm not able to provide more light on it. Do you know if the "#sql-xxxx.frm" files serve some standard function? –  Hal50000 Feb 12 at 16:45

1 Answer 1

up vote 0 down vote accepted

After a bit or research, it looks like the '#sql-xxxx.frm' files are just temp files created during ALTER statements, and possibly other MySQL behavior such as OPTIMIZE. If the mysqld service crashes, those files tend to get stranded. There's a few other stack exchange threads asking how to get rid of them, and to be honest, none of them worked for me. There's one rather clunky one suggested by the error logs, but instead I just deleted the temp files and dropped the original table I was working on. Then I reimported the table from an earlier dump.

During the course of an ALTER, '#sql-xxxx.frm' files are created. If the alter runs smoothly and completes, the '#sql-xxxx.frm' file will be deleted on its own.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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