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: | |
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
[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.