MySQL Bugs: #1785: AUTO_INCREMENT fields don't work on InnoDB

Bug #1785	AUTO_INCREMENT fields don't work on InnoDB
Submitted:	9 Nov 2003 11:03	Modified:	9 Nov 2003 19:41
Reporter:	Jeremy Perkins	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.0.16	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

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

It does this on Windows but I haven't yet tried it on Linux.

How to repeat:
CREATE TABLE AAA
(
	IndexNumber INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	RecordValue INT
)
TYPE = InnoDB;

INSERT INTO AAA(RecordValue)
VALUES (1);
DELETE FROM AAA
WHERE IndexNumber = 1;

INSERT INTO AAA(RecordValue)
VALUES (2);
DELETE FROM AAA
WHERE IndexNumber = 2;

INSERT INTO AAA(RecordValue)
VALUES (3);
DELETE FROM AAA
WHERE IndexNumber = 3;

etc.........

This works fine and as documented and as you would expect (ie. value stored in "IndexNumber" field continually increasing) until you restart the server.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is a consequence of the way AUTO_INCREMENT is incremented in InnoDB.
See:

http://www.mysql.com/doc/en/InnoDB_auto-increment_column.html

OK, now I read section 7.5.12.5 of the manual (which has only recently been added and so does not appear in the book) I can see that this is a design "feature" rather than a bug.

However, please be aware that you need to change 6.5.3 of the manual which currently incorrectly states that if you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will not be reused for an InnoDB table.

I would also say this is potentially a major "Gotcha!" that in some cases makes AUTO_INCREMENT columns unusable in InnoDB. In my case it certainly does, and in fact because MyISAM tables aren't transaction-safe I'm now not sure that I can use MySQL atall.

I beg your pardon - section 7.5.12.5 is in the book! However despite that I still stand by the rest of what I wrote!