Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I've got table with following schema:

CREATE TABLE `tblsomething` (
  `something_id` int(11) NOT NULL AUTO_INCREMENT,
   …
 PRIMARY KEY (`something_id`)
) ENGINE=InnoDB AUTO_INCREMENT=144620955

I need to drop the auto increment. So I'm thinking to do:

ALTER TABLE tblsomething MODIFY something_id int NOT NULL;

But this part of MySQL doc worries me:

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.

So is dropping auto increment actually one of these cases? Will it lock up my table?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Removing (or setting) AUTO_INCREMENT flag does not rebuild the entire table. You can easily test that using a table with a large number of rows (setting that flag is almost instantaneous).

share|improve this answer
    
I kind of suspected that. Is it officially documented anywhere? –  vartec Sep 18 '09 at 9:38

Certain ALTER TABLE statements will always produce a dreaded rebuild as your describe.

In the event of removing AUTO_INCREMENT fields the only way to prevent this is some unsupported hackery. This involves modifying a copy of the table's .frm file. It works because schema information is held separately from the data and index information and the modifications don't produce any inconsistencies between the three.

You can find a discussion about it at mysqlperformanceblog.org and in the High Performance MySQL book. It's important to stress that it is unsupported though. I'd recommend that you test both methods (plain ALTER TABLE and .frm editing) on a copy of the data first. See how long both processes take and check the consistency after.


Edit: Sorry, I re-read what was written and ALTER COLUMN doesn't apply to what you're doing. I've updated the text above.

share|improve this answer
    
So actually I should do "ALTER TABLE .. ALTER COLUMN .. DROP DEFAULT" rather than modify? –  vartec Sep 18 '09 at 9:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.