Bug #10459 Cannot modify a column as 'Auto Increment' if it contains a value 0.
Submitted: 9 May 2005 7:24 Modified: 12 May 2005 20:30
Reporter: Disha Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.4 Beta OS:Windows (Windows 200)
Assigned to: CPU Architecture:Any

[9 May 2005 7:24] Disha
Description:
If we try to set a column as auto increment having a value as 0 then error is displayed.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to // and use the test database.
3. Create a table as follows:
   create table t1(f1 int(4), f2 char(100), primary key(f1))//

4. Now insert few values in the table and verify that the column f1 is auto incremented as follows:
   insert into t1 values(0,'a')//
   insert into t1 values(1,'b')//
   insert into t1 values(2,'c')//
   select * from t1//

5. Now alter the column f1 and add auto_increment to the column as follows:
   alter table t1 modify f1 int(4) auto_increment//

Expected Results: 
1. The column should get modified as auto_increment column.

Actual Results: 
1. Error is displayed as:
ERROR 1062 (23000): Duplicate entry '1' for key 1
[9 May 2005 8:11] Heikki Tuuri
Disha,

note that an AUTO-INC column normally should not contain the value 0. I think the value 0 is allowed in some cases, starting from 4.1.xx, but it is not a good idea to use it. The normal auto-inc sequence starts 1, 2, 3, ...

Regards,

Heikki
[9 May 2005 9:57] Disha
If the behavior in this case is correct then the error that is displayed should be correct.

The error displayed indicates problem with value 1 where as the problematic value is 0.
[9 May 2005 13:29] Sergei Golubchik
The error is correct. When you add auto_increment attribute to a column, MySQL tries to generate auto-increment values for it. It generates 1 for the first row and fails to insert a second row - because of unique constraint violation
[10 May 2005 5:21] Disha
Please note that if we do not add the value ZERO in the column and try the same scenario, then it works without problems. 

Consider the following query that works correctly.

   create table t1(f1 int(4), f2 char(100), primary key(f1))//
   insert into t1 values(3,'a')//
   insert into t1 values(1,'b')//
   insert into t1 values(2,'c')//
   alter table t1 modify f1 int(4) auto_increment//

This works correctly, though this also has the value 1 in the column. As a result it DOES NOT seem that the conflict occurs because of value 1.
[12 May 2005 20:30] Sergei Golubchik
Consider the following:

create table t1(f1 int(4) auto_increment primary key, f2 char(100));
insert into t1 values(0,'a');
insert into t1 values(1,'b');
^^^ at that point you'll get "duplicate key" error
[13 May 2005 13:47] Omer Barnir
9 May 15:29] Sergei Golubchik

The error is correct. When you add auto_increment attribute to a column, MySQL
tries to generate auto-increment values for it. It generates 1 for the first row
and fails to insert a second row - because of unique constraint violation

[10 May 7:21] Disha

Please note that if we do not add the value ZERO in the column and try the same
scenario, then it works without problems. 

Consider the following query that works correctly.

   create table t1(f1 int(4), f2 char(100), primary key(f1))//
   insert into t1 values(3,'a')//
   insert into t1 values(1,'b')//
   insert into t1 values(2,'c')//
   alter table t1 modify f1 int(4) auto_increment//

This works correctly, though this also has the value 1 in the column. As a
result it DOES NOT seem that the conflict occurs because of value 1.
[4 Sep 2012 17:14] Jordan C. Whittingham
Thanks very much Disha. I appreciate your demo of proper Mysql syntax.