Bug #15467 auto_increment flushes after mysql restart

• Submitted: 4 Dec 2005 9:17
• Modified: 4 Dec 2005 18:21
• Reporter: Ronni Smith
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: mysql 4.14
• OS: Windows (Windows XP/2003)
• CPU Architecture: Any

[4 Dec 2005 9:17] Ronni Smith

Description:
I use default mysql installation from install package

Create table with auto_increment field like

CREATE TABLE `deals` (
  `deal_id` int(11) unsigned NOT NULL auto_increment,
  `user_id` int(11) unsigned NOT NULL default '0',
  `status` tinyint(1) unsigned NOT NULL default '0',
  `type` tinyint(2) unsigned NOT NULL default '0'
);

`deal_id` was 78 (for example). Then we deleted last 10 entries.
At this moment auto increment is 79.

But after server restarting it becomes 69 (the last `deal_id` at table was 68)!!!

So, mysql loose auto increment value after mysql reboot!

How to repeat:
Create table with auto_increment field like

CREATE TABLE `deals` (
  `deal_id` int(11) unsigned NOT NULL auto_increment,
  `user_id` int(11) unsigned NOT NULL default '0',
  `status` tinyint(1) unsigned NOT NULL default '0',
  `type` tinyint(2) unsigned NOT NULL default '0'
);

Fill it with some data
`deal_id` was 78 (for example). Delete last 10 entries. 
Look at auto increment value
Shutdown mysql and start it again anfd look at auto increment again

[4 Dec 2005 11:19] Heikki Tuuri

Hi!

This is the default behavior of InnoDB. InnoDB recalculates the auto-inc counter after a mysqld server restart.

Regards,

Heikki

[4 Dec 2005 18:21] Ronni Smith

I am sorry, but is there a way to not have auto_increment revert back to 1 if the server is restarted?

I cannot found this way at man