Bug #10678 AUTO_INCREMENT table option lost on restart

• Submitted: 16 May 2005 22:30
• Modified: 17 May 2005 1:15
• Reporter: Dean Ellis
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.1.13 forward
• Assigned to: Dean Ellis
• CPU Architecture: Any

[16 May 2005 22:30] Dean Ellis

Description:
The fix for bug #7061 (No error message for ALTER with AUTO_INCREMENT) provided support for the CREATE/ALTER TABLE option AUTO_INCREMENT for InnoDB.

This information is lost if the server crashes/restarts before an insert is made to the table (as with deleting from the end of the sequence; after restart InnoDB uses MAX()+1).

How to repeat:
CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY ) ENGINE=InnoDB AUTO_INCREMENT=100;
restart mysqld
INSERT INTO t1 VALUES ( NULL );
SELECT * FROM t1;

Suggested fix:
1) Document the limitation or

2) Persist the auto_increment value (which I suppose would fix the "re-use" of deleted values that occurs with a restart after deleting from the end of the sequence).

[16 May 2005 23:41] Paul DuBois

This behavior is documented here:

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

[17 May 2005 1:15] Dean Ellis

Missed that; thanks Paul.