MySQL Bugs: #46405: Incorrect behaviour of Auto Increment field after server restart.

Bug #46405	Incorrect behaviour of Auto Increment field after server restart.
Submitted:	27 Jul 2009 13:53	Modified:	27 Aug 2009 14:07
Reporter:	Jon Swain	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.51b	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

There is a bug in reivsion 5.0.51b which causes an auto increment field to behave incorrectly following a restart.

Following a restart deleting the row with the highest autoid then inserting another row in to the table, will cause the inserted row to have the same AutoID as the deleted one when it should be the AutoID of the delete row + 1.

How to repeat:
To recreate create the following table

CREATE TABLE `test` (
  `AutoID` bigint(20) NOT NULL auto_increment,
  `Name` varchar(20) default NULL,
  PRIMARY KEY  (`AutoID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

next insert several rows the the table

INSERT INTO test SET Name='One';
INSERT INTO test SET Name='Two';
INSERT INTO test SET Name='Three';

you will have the following 

+--------+-----------+
| AutoID | Name      |
+--------+-----------+
|      1 | One       |
|      2 | Two       |
|      3 | Three     |
+--------+-----------+

Now restart the MySql service....

following the restart do the following

DELETE FROM test WHERE AutoID=3;
INSERT INTO test SET Name='Four';

you will have the following 

+--------+-----------+
| AutoID | Name      |
+--------+-----------+
|      1 | One       |
|      2 | Two       |
|      3 | Four      |
+--------+-----------+

Wheras i would expect 

+--------+-----------+
| AutoID | Name      |
+--------+-----------+
|      1 | One       |
|      2 | Two       |
|      4 | Four      |
+--------+-----------+

Thank you for the bug report. Could you please try with latest release and comment the result. Thanks in advance.

I can't repeat this with recent 5.1.38 for example:

...
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.38-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELETE FROM test WHERE AutoID=3;
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO test SET Name='Four';
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------+------+
| AutoID | Name |
+--------+------+
|      1 | One  | 
|      2 | Two  | 
|      4 | Four | 
+--------+------+
3 rows in set (0.00 sec)

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