2

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?

Dan Carley
  • 25,189
  • 5
  • 52
  • 70
vartec
  • 6,137
  • 2
  • 32
  • 49

2 Answers2

2

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.

Dan Carley
  • 25,189
  • 5
  • 52
  • 70
1

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

Update: This was probably true long time ago (with MyISAM), but looks it is no longer the case.

David Poblador i Garcia
  • 626
  • 4
  • 7
  • 1
    I kind of suspected that. Is it officially documented anywhere? – vartec Sep 18 '09 at 09:38
  • Actually it isn't true. Removing auto_increment for big tables takes very long time to acomplish. Been there several times. – Konrad Gałęzowski Sep 21 '17 at 09:24
  • This is simply false. Either elaborate on how to set the flag or show a real example of how to modify a column without rewriting the table. – Reut Sharabani Jan 29 '18 at 13:58
  • It took longer to remove AUTO_INCREMENT than it did to insert 8 million records. It has to be recreating the table, which is sad. I'm running away, as fast as I can, back to SQL Server. – Triynko Apr 17 '19 at 22:40