MySQL Bugs: #8402: auto_increment column reuse previously generated number

Bug #8402	auto_increment column reuse previously generated number
Submitted:	9 Feb 2005 21:07	Modified:	9 Feb 2005 21:54
Reporter:	Sheeraz Fazal	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.0.21	OS:	Windows (Windows 2003 Server)
Assigned to:		CPU Architecture:	Any

Description:
After a restart of server. Innodb reuse previously generated auto_increment values.

How to repeat:
I have created an auto_increment column in INNODB table.
I have inserted 10 rows in the table. and auto_increment column has values 
1 to 10.

Now if i delete last row 10. And insert another row, next auto number will be 11. 

Instead if i delete last row which has auto number 10. And i restart my server. Theoratically after restart if i insert another row next number should be 11. But actually it generated 10 instead of 11. What i have experience is that after restart innodb takes the maximum of the auto_increment and next time it will generate max of auto_increment +1. 

Suggested fix:
innodb table should not lose the track of next number for auto_increment column after restart.

This is the expected behavior of auto_increment columns with InnoDB.

http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html