Bug #4165 Auto-increment unaffected by deleted records after restart
Submitted: 16 Jun 2004 14:08 Modified: 16 Jun 2004 15:12
Reporter: Yair dfwerfw Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.17 OS:Windows (Windows XP Pro)
Assigned to: CPU Architecture:Any

[16 Jun 2004 14:08] Yair dfwerfw 
Description:
The auto-increment value of a deleted record will only not be reused until the server is restarted, then it will be reused.
In other words, if you insert 10 rows into an InnoDB table and then delete them, the AUTO_INCREMENT value will stay at 11 until the server is shutdown at which point it will revert to 1.

How to repeat:
Insert records, delete last inserted record, insert record ==> id of deleted record IS NOT reused

Insert records, delete last inserted record, shutdown server, restart server,
insert record ==> id of deleted record IS reused
[16 Jun 2004 15:12] Marko Mäkelä 
This behavior has been documented in the user's manual: http://dev.mysql.com/doc/mysql/en/InnoDB_auto-increment_column.html

The problem arises from the fact that InnoDB does not keep the auto_increment counter on disk, but only in main memory.
[16 Jun 2004 16:23] Yair dfwerfw 
Couls you handle this as a feature request please