ALTER TABLE, SELECT AND INNODB

Let's assume you have a 512MB table, and you decide to alter the table to add an index to make queries faster.

How long would you expect this alter to take? Hours? Days?

Even with 7200 RPM-slow disks the alter should of finished in less then 1/2 hour.

I ran across an alter that was running for 4 days-on 512MB datasize. The reason why it ran so long is because there was a SELECT that was running preventing mySQL from performing "rename table", the last leg of the ALTER TABLE process.

Killing that SELECT released the shared lock allowing the alter to finish.


Do not KILL THE ALTER when stuck in this SHARED LOCK STATE; Do NOT then remove the temporary tablespace file "#sql-320f_106f99a2.*".

What will happen if you do remove the #sql* file by hand?

Well for one INNODB will crash the mysql instance saying it could not find the temporary table space. It failed to open it. Then on recovery the original table gets unlinked from the filesystem and you just lost all data for that tablespace.

Why?

Here are roughly the order of events for an alter:

Lock all writes from said table
Make a temporary table #sql - file
Copy all data from the old file to the new file
Do a quick consistency check between the two files
unlink the old file
rename the temp file into the old file name

Each step operates on the data dictionary pointers for the two tables. Issuing a filesystem rm command for the step right before unlink, will cause INNODB to crash and on recovery unlink the old file and of course fail on the rename.