MySQL Bugs: #66410: autoincrement seed reset on server restart

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:	Windows (7/2003/2008)
Assigned to:		CPU Architecture:	Any
Tags:	autoincrement reset

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.

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...

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".