MySQL Bugs: #37927: ALTER TABLE ... AUTO_INCREMENT=... copies table, should only change attribute

Bug #37927	ALTER TABLE ... AUTO_INCREMENT=... copies table, should only change attribute
Submitted:	7 Jul 2008 13:27	Modified:	7 Jul 2008 13:39
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.0, 5.1	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
An ALTER TABLE ... AUTO_INCREMENT=... only needs to change the auto increment position information on the table, it does not change the actual schema or data.
Still a copy of the table with the new setting applied is created and all data copied over from the original to the new table. So an operation that should be O(1) becomes O(n) at least ... :(

How to repeat:
Create a table with an auto increment column, insert lots of rows, then try to change the AUTO_INCREMENT attribute only using

  ALTER TABLE ... AUTO_INCREMENT=some_value_larger_than_current

This will take some time depending on table size and when checking the database directory during the operation temporary table files show that the table is indeed copied over even though there is no structure and data change at all.

Suggested fix:
Just change the auto increment position meta information instead of doing the full CREATE/COPY/DROP/RENAME cycle.

Thank you for the feature request bug.

Workaround: If you insert a record, specifying the autoincrement column to be a higher value than autoincrement is set to, it will update this without copying the table.

Bug #52647 was marked as a duplicate of this one.

As an explanation of why this bug/feature is quite important: Django unit testing framework does a database flush between different test classes. In most cases this take fractions of a second, but if you have several hundred tests a couple dozen applications and over 50 tables, then the time spent in ALTER TABLE ... AUTO_INCREMENT=1; call that Django does between test sets for all tables very quickly addds up.

In a project I am working at right now running the unittests without the AUTO_INCREMENT reset takes only 3 minutes, but with default Django behavior the same tests take nearly 30 minutes to run! And this is with EMPTY tables (they are TRUNCATED right before the ALTER TABLE command)!

There is no such problem in SQLite, Postgres or Oracle. And for some reason we don't see this in Windows version of MySQL Server either. This is a really annoying bug for Linux MySQL developers doing serious Django work!

The same (previous comment) also is true for Yii php framework. 
I guess this can also be important for any other unit testing environment. Reset database before running a test is a common practice and part of this process is reset of the AUTO_INCREMENT value.