MySQL Bugs: #14573: Error on adding auto-increment attribute to a column containing '0' values

Bug #14573	Error on adding auto-increment attribute to a column containing '0' values
Submitted:	2 Nov 2005 13:19	Modified:	22 May 2006 18:25
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0 and up	OS:	Any (any)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Using ALTER TABLE to add an auto_increment attribute to a column that already contains 0 and 1 as values leads to:

  ERROR 1062 (23000): Duplicate entry '1' for key 1
 
In 4.1 and up this can be avoided using 

  sql_mode="NO_AUTO_VALUE_ON_ZERO";

but the error message is still rather confusing, and it is questionable whether adding
auto_increment on an existing column should change column contents at all

How to repeat:
CREATE TABLE tt (t1 INT(10) PRIMARY KEY, t2 INT(10));
INSERT INTO tt VALUES(0, 0);
INSERT INTO tt VALUES(1, 1);
ALTER TABLE tt CHANGE t1 t1 INT(10) auto_increment;

Suggested fix:
Possible fixes are:

- give a more meaningfull error message (not just *what* happened but *why* it happened)
- don't change values at all on an ALTER TABLE ... CHANGE
- detect the highest value currently in use first and continue to count up from there

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6209

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6303

Cannot tell what was done here.  What is the nature
of the fix?  Thanks.

Noted in 5.1.11 changelog.

Display better error message for <literal>ALTER
TABLE</literal> operations that will result in duplicate keys
due to <literal>AUTO_INCREMENT</literal> resequencing.