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 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


Subject Views Written By Posted
Alter table killed, but change was done 1045 Charlie Schaubmair 06/01/2011 01:36AM
Re: Alter table killed, but change was done 475 Rick James 06/02/2011 10:33AM
Re: Alter table killed, but change was done 546 Charlie Schaubmair 06/02/2011 11:10AM
Re: Alter table killed, but change was done 519 Rick James 06/02/2011 07:53PM
Re: Alter table killed, but change was done 400 Charlie Schaubmair 06/03/2011 01:58AM
Re: Alter table killed, but change was done 568 Rick James 06/03/2011 09:38AM
Re: Alter table killed, but change was done 493 Charlie Schaubmair 06/04/2011 01:33AM
Re: Alter table killed, but change was done 836 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.