Skip navigation links

MySQL Forums :: InnoDB :: Alter table killed, but change was done


Advanced Search

Re: Alter table killed, but change was done
Posted by: Rick James ()
Date: June 04, 2011 09:46AM

Thanks. (I was thrown by the script.)

Between steps 2 and 4 did you do SHOW PROCESSLIST? I would expect to see "Killed" briefly, then the table reverting to the original schema. However, steps 4 and 5 imply that something else happened. (Yeah, that's just a rephrasing of what you have been telling us.)

You have not said whether this table is InnoDB or MyISAM, but I think the actions are similar. NDB Cluster might be different.

Step 3: "tmp table was immediately deleted" -- that sounds like the Unix 'feature' wherein the files are 'unlinked' from the directory you are looking at, so you think they are gone. But, mysqld still has them 'open', so the space is not freed up until they are closed. This would also fit with the error 28s -- mysqld was continuing to write to the (now invisible) file, until it ran out of disk space.

I would have expected the ALTER to do something very similar to your not-used script:
1. Lock
2. Create a new, empty, tmp, table with the appropriate alterations.
3. Do INSERT...SELECT to copy all the data
4. Rename the tmp table into place
6. Unlock
5. DROP the old table.
Step 2 is the only time-consuming step, and the only step that could cause error 28. But, killing step 2 should have prevented the rest of the steps from being performed. Hopefully (but I am not so sure) it would free up the tmp files.

I can't think of any relevance, but what was the definition of myColumn before the ALTER?

I am giving you a long-winded answer with the bottom line of: I don't know why the table was modified anyway.

Options: ReplyQuote


Subject Views Written By Posted
Alter table killed, but change was done 982 Charlie Schaubmair 06/01/2011 01:36AM
Re: Alter table killed, but change was done 430 Rick James 06/02/2011 10:33AM
Re: Alter table killed, but change was done 509 Charlie Schaubmair 06/02/2011 11:10AM
Re: Alter table killed, but change was done 498 Rick James 06/02/2011 07:53PM
Re: Alter table killed, but change was done 376 Charlie Schaubmair 06/03/2011 01:58AM
Re: Alter table killed, but change was done 550 Rick James 06/03/2011 09:38AM
Re: Alter table killed, but change was done 474 Charlie Schaubmair 06/04/2011 01:33AM
Re: Alter table killed, but change was done 797 Rick James 06/04/2011 09:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.