Description:
When setting the auto_increment values with an alter table statement, then restarting the MySQL server, the auto_increment value reverts back to the largest value in the column.
I am filing this even though it can be found here:
http://bugs.mysql.com/bug.php?id=199
In the below, auth_user is an innodb table. Note the 10th column is the auto_increment value.
boyd@stream:~ 20:47:15 >mysql -sse "show table status like 'auth_user'"
auth_user InnoDB 10 Compact 6 2730 16384 0 16384 0 7 2012-02-09 20:40:44 NULL NULL utf8_unicode_ci NULL
boyd@stream:~ 20:48:00 >mysql -sse "alter table auth_user auto_increment = 1000"
boyd@stream:~ 20:48:10 >mysql -sse "show table status like 'auth_user'"
auth_user InnoDB 10 Compact 6 2730 16384 0 16384 0 1000 2012-02-09 20:48:10 NULL NULL utf8_unicode_ci NULL
boyd@stream:~ 20:48:13 >sudo restart mysql
mysql start/running, process 333
boyd@stream:~ 20:48:27 >mysql -sse "show table status like 'auth_user'"
auth_user InnoDB 10 Compact 6 2730 16384 0 16384 0 7 2012-02-09 20:48:10 NULL NULL utf8_unicode_ci NULL
This is not a problem for MyISAM tables as `foo` is a MyISAM table:
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foo | CREATE TABLE `foo` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`junk` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Writing history-file /home/boyd/.mysql_history
Bye
boyd@stream:~ 23:04:42 >mysql fm_tools -sse "alter table foo auto_increment = 1000"
boyd@stream:~ 23:05:02 >mysql -sse "show table status like 'foo'"
boyd@stream:~ 23:05:15 >mysql fm_tools -sse "show table status like 'foo'"
foo MyISAM 10 Dynamic 2 20 40 281474976710655 2048 0 1000 2012-02-09 23:05:02 2012-02-09 23:05:02 NULL utf8_unicode_ci NULL
boyd@stream:~ 23:05:25 >sudo restart mysql
[sudo] password for boyd:
mysql start/running, process 20705
boyd@stream:~ 23:05:40 >mysql fm_tools -sse "show table status like 'foo'"
foo MyISAM 10 Dynamic 2 20 40 281474976710655 2048 0 1000 2012-02-09 23:05:02 2012-02-09 23:05:02 NULL utf8_unicode_ci NULL
boyd@stream:~ 23:05:43 >
How to repeat:
take any innodb table (e.g `auth_user`)
In the below, auth_user is an innodb table. Note the 10th column is the auto_increment value.
boyd@stream:~ 20:47:15 >mysql -sse "show table status like 'auth_user'"
auth_user InnoDB 10 Compact 6 2730 16384 0 16384 0 7 2012-02-09 20:40:44 NULL NULL utf8_unicode_ci NULL
boyd@stream:~ 20:48:00 >mysql -sse "alter table auth_user auto_increment = 1000"
boyd@stream:~ 20:48:10 >mysql -sse "show table status like 'auth_user'"
auth_user InnoDB 10 Compact 6 2730 16384 0 16384 0 1000 2012-02-09 20:48:10 NULL NULL utf8_unicode_ci NULL
boyd@stream:~ 20:48:13 >sudo restart mysql
mysql start/running, process 333
boyd@stream:~ 20:48:27 >mysql -sse "show table status like 'auth_user'"
auth_user InnoDB 10 Compact 6 2730 16384 0 16384 0 7 2012-02-09 20:48:10 NULL NULL utf8_unicode_ci NULL
Suggested fix:
Once an alter table statement is issued, table metadata should flush to disk to persist past a restart or crash.
