MySQL Bugs: #41826: Auto increment

Bug #41826	Auto increment
Submitted:	2 Jan 2009 10:09	Modified:	5 Feb 2009 10:23
Reporter:	shunmuga prabhu	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Bugs in Auto increment

Description:
I have create a table xyz
create table xyz (id int(3) primary key auto_increment,name varchar(30));

After i inserted two records.
insert xyz set name='shun';
insert xyz set name='prabhu';
Two rows are inserted,

1    shun
2    prabhu

After i modified the first row,
update xyz set id=3;
After view the table,(select)

3   shun
2   prabhu

After I inserted using 
insert xyz set name='vishalini';

but mysql returns error code 1062 (Duplicate key 3 for key 1)

If we change the auto_increment value, mysql does not modify the last entry (ie, next value of auto increment value)

How to repeat:
create table xyz (id int(3) primary key auto_increment,name varchar(30));

insert xyz set name='shun';
insert xyz set name='prabhu';

update xyz set id=3;

insert xyz set name='vishalini';
error code 1062 (Duplicate key 3 for key 1)

Suggested fix:
Auto increment is working the next value of last inserted key.
If any insert opertion will do,checking the auto increment value exits or not

Sorry, but UPDATE just does not work:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table xyz (id int(3) primary key auto_increment,name varchar(30));

Query OK, 0 rows affected (0.16 sec)

mysql> insert xyz set name='shun';
Query OK, 1 row affected (0.05 sec)

mysql> insert xyz set name='prabhu';
Query OK, 1 row affected (0.03 sec)

mysql> update xyz set id=3;
ERROR 1062 (23000): Duplicate entry '3' for key 1
mysql> select * from xyz;
+----+--------+
| id | name   |
+----+--------+
|  1 | shun   |
|  2 | prabhu |
+----+--------+
2 rows in set (0.00 sec)

So, looks like something is missed in your test case.

create table xyz (id int(3) primary key auto_increment,name varchar(30));

insert xyz set name='shun';
insert xyz set name='prabhu';

update xyz set id=3 where id=1;

insert xyz set name='vishalini';
error code 1062 (Duplicate key 3 for key 1)

Is your table created as InnoDB? Please, check with SHOW CREATE TABLE. MyISAM table shows the behaviour you expected:

mysql> drop table xyz;
Query OK, 0 rows affected (0.02 sec)

mysql> create table xyz (id int(3) primary key auto_increment,name
    -> varchar(30)) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert xyz set name='shun';
Query OK, 1 row affected (0.00 sec)

mysql> insert xyz set name='prabhu';
Query OK, 1 row affected (0.00 sec)

mysql> update xyz set id=3 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert xyz set name='vishalini';
Query OK, 1 row affected (0.00 sec)

mysql> select * from xyz;
+----+-----------+
| id | name      |
+----+-----------+
|  3 | shun      |
|  2 | prabhu    |
|  4 | vishalini |
+----+-----------+
3 rows in set (0.00 sec)

and InnoDB's (different) behaviour is, actually, described somehow in the manual, http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html:

"After the auto-increment counter has been initialized, if a user does not explicitly specify a value for an AUTO_INCREMENT column, InnoDB increments the counter by one and assigns the new value to the column. If the user inserts a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value."

You had explicitely changed the value for the auto_increment column to one that is NOT bigger that the current counter value. So, internal counter remained the same, 3.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".