MySQL Forums
Forum List  »  InnoDB

Re: Alter table killed, but change was done
Posted by: Rick James
Date: June 02, 2011 10:33AM

The INSERT...SELECT is the only step that takes a significant amount of time. Presumably you killed it in the middle of that.

MyISAM, not InnoDB?

With MyISAM, the INSERT...SELECT would have copied over some of the rows before being killed. If the kill let the script continue with the next statement, then you have lost the rows that were not copied.

With InnoDB, the INSERT...SELECT would have been rolled back; this would have taken a noticeable amount of time. Again, if the script continued without noticing the about, you would have lost rows. This time, your new table would be empty.

There are scripts from Facebook and Percona that will do ALTERs in a more sophisticated way -- with virtually no locking and virtually no downtime.

Hint: Remove the DROP TABLE from the script. Do the DROP later, after you have verified that the table was copied _completely_.

Options: ReplyQuote

Written By
Re: Alter table killed, but change was done
June 02, 2011 10:33AM

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.