Bug #66410 autoincrement seed reset on server restart
Submitted: 16 Aug 2012 1:29 Modified: 19 Sep 2012 9:14
Reporter: Hearth Stone Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.5.16 & 5.5.20 OS:Microsoft Windows (7/2003/2008)
Assigned to: CPU Architecture:Any
Tags: autoincrement reset

[16 Aug 2012 1:29] Hearth Stone
Description:
Any table with an auto_increment id will have its seed reset to the max value +1 whenever the server is restarted.

This creates a serious problem in for our audit system, which copies a record to an audit table when it is deleted from the source table. If the records with the greatest id fields are deleted, those id values will be reused after the server is restarted. This creates errors when copying to the audit table, causing the operation to fail.

Current workaround is for us to generate and manage the ID's ourselves manually. Essentially we cannot use the auto increment feature on these tables.

How to repeat:
CREATE TABLE seedtest (
  id int AUTO_INCREMENT,
  PRIMARY KEY (id)
);

insert into seedtest values (null),(null),(null);

SHOW CREATE TABLE seedtest;
#AUTO_INCREMENT = 4

delete from seedtest where id=3;

SHOW CREATE TABLE seedtest;
#AUTO_INCREMENT = 4

#** restart mysql server/service **

SHOW CREATE TABLE seedtest;
#AUTO_INCREMENT = 3 ***

Suggested fix:
I believe the auto_increment seed forms an integral part of the table schema and should not be reset unless an explicit operation (such as ALTER TABLE, or TRUNCATE) is performed.

On server restart, the database should honour the previous max seed values.

At the very least this should be a configurable option for optimisation purposes.
[19 Aug 2012 9:14] Valeriy Kravchuk
Your table is InnoDB one it seems (this engine is used by default in 5.5). For InnoDB tables this is expected and documented behavior, see http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html:

"InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM t FOR UPDATE;

InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table."

You can try to use MyISAM tables or try to track those deleted values somehow...
[20 Sep 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".