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

[2 Nov 2005 13:19] Hartmut Holzgraefe
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
[10 May 2006 17:15] Bugs System
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
[12 May 2006 14:49] Bugs System
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
[18 May 2006 13:32] Paul Dubois
Cannot tell what was done here.  What is the nature
of the fix?  Thanks.
[22 May 2006 18:25] Paul Dubois
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.