Bug #37286 Setting AUTO_INCREMENT later on a column updates record 0 with INSERT_ID

• Submitted: 9 Jun 2008 15:11
• Modified: 9 Jun 2008 15:18
• Reporter: Laszlo Keresztfalvi
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.45-Debian_1ubuntu3.3-log
• OS: Linux (ubuntu 7.10)
• CPU Architecture: Any
• Tags: 5.0, auto_increment, INSERT_ID

[9 Jun 2008 15:11] Laszlo Keresztfalvi

Description:
Setting AUTO_INCREMENT later on a column already having data will update the record with value 0 to the current INSERT_ID or fail when one already exists with that value. Why?

How to repeat:
mysql> create table t (id bigint primary key);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values (0);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+----+
| id |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

mysql> alter table t modify column id bigint auto_increment;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Suggested fix:
None, if a record with INSERT_ID already exists, since altering the column fails (ERROR 1062 (23000): Duplicate entry '1' for key 1) and the record may not be updated to a different ID.

Otherwise, update the record with INSERT_ID back to 0. Next record will come with the next id.

[9 Jun 2008 15:18] Laszlo Keresztfalvi

Sorry, later i've found that this was already set to not-a-bug.. I just left state on 'active' instead of 'all' :(