Bug #41841 AUTO_INCREMENT cannot be reset after inserting negative number
Submitted: 3 Jan 2009 14:19 Modified: 3 Jan 2009 18:19
Reporter: R V Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1.30 OS:Microsoft Windows (XP SP3)
Assigned to:
Tags: auto_increment

[3 Jan 2009 14:19] R V
Description:
When a table is created with an INT AUTO_INCREMENT field and a negative value is inserted, the AUTO_INCREMENT value is set to 1844674407370955 and cannot be changed. No error message is displayed.
Trying to insert new records results in "Failed to read auto-increment value from storage engine".
This example is derived from a general SQL installation script that used to work in previous versions of MySQL 5, but now fails.

How to repeat:
CREATE TABLE test1 (
     id int NOT NULL PRIMARY KEY AUTO_INCREMENT
);
INSERT INTO test1(id) VALUES (1);

ALTER TABLE test1 AUTO_INCREMENT = 5; /* works as expected */

CREATE TABLE test2 (
     id int NOT NULL PRIMARY KEY AUTO_INCREMENT
);
INSERT INTO test2(id) VALUES (-1);

ALTER TABLE test2 AUTO_INCREMENT = 5; /* does not do anything */
[3 Jan 2009 14:26] R V
Someone encountered the "Failed to read auto-increment value from storage engine" error in the same situation, both on 6.0.6-alpha and 5.1.x (not detailed).
[3 Jan 2009 14:53] R V
Correction: number should have been 18446744073709551615 but got stripped off.
[3 Jan 2009 17:29] Ken Jacobs
This appears to be the same as bug http://bugs.mysql.com/bug.php?id=36411.   A patch for this bug was sent to MySQL in October, but it does not appear from the doc (http://dev.mysql.com/doc/refman/5.1/en/news-5-1-31.html) that it has been applied to even to the up-coming 5.1.31.
[3 Jan 2009 18:19] Miguel Solorzano
Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=36411