MySQL Forums
Forum List  »  InnoDB

Alter table killed, but change was done
Posted by: Charlie Schaubmair
Date: June 01, 2011 01:36AM

Hello,

this is very strange for me, because yesterday evening a colleague made an alter on a 50 GB table.
This would have taken about 13 hours.

He just made it with an "normal" alter script like this:
ALTER TABLE table1 modify myColumn varchar(200);

Normally this should be done like this:
CREATE TABLE table1_tmp like table1;
ALTER TABLE table1_tmp modify myColumn varchar(200);
SET UNIQUE_CHECKS=0;
INSERT INTO table1_tmp SELECT * FROM table1;
SET UNIQUE_CHECKS=1;
RENAME TABLE table1 TO table1_old;
RENAME TABLE table1_tmp TO table1;
DROP table1_old;

Of course if there are any triggers, then these has to created also again...



OK, but back to the phenomenon:
The ALTER-Script was running about 3 hours and MySQL generated a tmp-table for it - the normal way if a table is so big.
Because it would have taken so long and the table was of course locked, I killed the alter-script-process.

Everything was working again an the tmp table was gone!

Today I noticed that in this table1 the column "myColumn" is now modified to VARCHAR(200).

How is this possible?
Should I be scared ;-) ?

Options: ReplyQuote


Subject
Views
Written By
Posted
Alter table killed, but change was done
1595
June 01, 2011 01:36AM


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.