Re: Alter table killed, but change was done
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_.
Subject
Views
Written By
Posted
1597
June 01, 2011 01:36AM
Re: Alter table killed, but change was done
796
June 02, 2011 10:33AM
845
June 02, 2011 11:10AM
808
June 02, 2011 07:53PM
595
June 03, 2011 01:58AM
772
June 03, 2011 09:38AM
781
June 04, 2011 01:33AM
1339
June 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.