Bug #64287 auto_increment value lost on server restart
Submitted: 9 Feb 2012 23:16 Modified: 10 Feb 2012 5:03
Reporter: Boyd Hemphill Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1.41 OS:Linux (Ubuntu 10.04)
Assigned to:
Tags: auto_increment, innodb, restart
Triage: Needs Triage: D5 (Feature request)

[9 Feb 2012 23:16] Boyd Hemphill
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.
[10 Feb 2012 5:03] Valerii Kravchuk
We all know this happens, this behavior is documented in the manual, http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html. 

But, indeed, even back in 2003 there were plans to change this to more expected behavior. Let's try to escalate bug #199 now.