MySQL Bugs: #41841: AUTO_INCREMENT cannot be reset after inserting negative number

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:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment

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 */

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

Correction: number should have been 18446744073709551615 but got stripped off.

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.

Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=36411