MySQL Forums
Forum List  »  Performance

Removing NOT NULL constraint takes a long time. Why?
Posted by: Ben Dowling
Date: October 28, 2009 08:54AM

Hi,

I have an InnoDB table with around 130 million rows. I recently had to change one of the columns from:

confirmed TINYINT UNSIGNED DEFAULT 0 NOT NULL

to:

confirmed TINYINT UNSIGNED DEFAULT 1

Essentially I was just removing the NOT NULL constraint. To do so I ran the query:

ALTER TABLE table CHANGE COLUMN confirmed confirmed TINYINT DEFAULT 0

The alter query is still running, and has been doing so for almost 30 hours now. I'm amazed that the query is taking so long. Could someone with knowledge of MySQL/InnoDB internals enlighten me as to what is happening to make what seems like a simple relaxing on constraints to take so long?

Thanks in advance,

Ben

Options: ReplyQuote


Subject
Views
Written By
Posted
Removing NOT NULL constraint takes a long time. Why?
9823
October 28, 2009 08:54AM


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.