update is not changing internal auto increment value
broken.auto_increment [ fail ]

drizzletest: At line 307: query 'insert into t1 (val) values (1)' failed: 1062: Duplicate entry '2' for key 'PRIMARY'

The result from queries just before the failure was:
< snip >
a b
2 2
3 1
4 3
drop table t1;
create table t1 (rowid int not null auto_increment, val int not null,primary
key (rowid), unique(val));
replace into t1 (val) values ('1'),('2');
replace into t1 (val) values ('1'),('2');
insert into t1 (val) values ('1'),('2');
ERROR 23000: Duplicate entry '1' for key 'val'
select * from t1;
rowid val
3 1
4 2
drop table t1;
create table t1 (a int not null auto_increment primary key, val int);
insert into t1 (val) values (1);
update t1 set a=2 where a=1;
insert into t1 (val) values (1);

Re: update is not changing internal auto increment value
Confirmed, but only for the InnoDB storage engine, which should help narrow down the fix:

jpipes@serialcoder:~/repos/drizzle/new-temporal/tests$ ../client/drizzle --user=root --port=9306
Welcome to the Drizzle client.. Commands end with ; or \g.
Your Drizzle connection id is 1
Server version: 7.0.0 Source distribution

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

drizzle> use test
Database changed
drizzle> create table t1 (a int not null auto_increment primary key, val int)engine=innodb;
Query OK, 0 rows affected (0.01 sec)

drizzle> insert into t1 (val) values (1);
Query OK, 1 row affected (0.00 sec)

drizzle> update t1 set a=2 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

drizzle> insert into t1 (val) values (1);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
drizzle> drop table t1;
Query OK, 0 rows affected (0.01 sec)

drizzle> create table t1 (a int not null auto_increment primary key, val int)engine=myisam;
Query OK, 0 rows affected (0.02 sec)

drizzle> insert into t1 (val) values (1);
Query OK, 1 row affected (0.00 sec)

drizzle> update t1 set a=2 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

drizzle> insert into t1 (val) values (1);
Query OK, 1 row affected (0.00 sec)

Re: update is not changing internal auto increment value
Setting to critical...different storage engines should not produce different behaviour in this respect.

Re: update is not changing internal auto increment value
So here's a little more info about this bug:

The insert query succeeds on the second attempt (after the failure) as seen below.

---------

drizzle> use test
Database changed

drizzle> create table t1 (a int not null auto_increment primary key, val int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

drizzle> insert into t1 (val) values (1);
Query OK, 1 row affected (0.01 sec)

drizzle> update t1 set a=2 where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

drizzle> insert into t1 (val) values (1);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

drizzle> insert into t1 (val) values (1);
Query OK, 1 row affected (0.01 sec)

drizzle> select * from t1;
-------+

-------+

-------+
2 rows in set (0.00 sec)

Re: update is not changing internal auto increment value
So I did some digging around and found the cause.

This looks more serious than I thought, and I've confirmed that this bug is in MySQL as well.

As suspected, InnoDB's internal counter isn't updated when we update a row. So the next time we call INSERT, it fails because dict_table_struct's autoinc value isn't updated.

For this particular case, the INSERT statement after the failure works because the autoinc counter was updated when ha_innobase::get_auto_increment was called in the failed statement.

The next INSERT statement works because the autoinc counter is updated. This SEEMS to have done the correct thing because we only updated the autoinc column by 1 in the bug report but imagine a case where the column is updated by something larger than 1 like the following:

--------------
drizzle> create table t1 (a int not null auto_increment primary key, val int)engine=innodb;
Query OK, 0 rows affected (0.01 sec)

drizzle> insert into t1 (val) values (1);
Query OK, 1 row affected (0.01 sec)

drizzle> update t1 set a=100 where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

drizzle> select * from t1;
---------+

---------+

---------+
1 row in set (0.00 sec)

drizzle> insert into t1 (val) values (1);
Query OK, 1 row affected (0.01 sec)

drizzle> select * from t1;
---------+

---------+

---------+
2 rows in set (0.00 sec)
--------------

What's wrong in the above output is that the next inserted row should have a autoinc value, larger than 100 (relative to the increment and offset value).

This works fine on MyISAM:
--------------

drizzle> create table t2 (a int not null auto_increment primary key, val int)engine=myisam;
Query OK, 0 rows affected (0.00 sec)

drizzle> insert into t2 (val) values (1);
Query OK, 1 row affected (0.00 sec)

drizzle> update t2 set a=100 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

drizzle> select * from t2;
---------+

---------+

---------+
1 row in set (0.00 sec)

drizzle> insert into t2 (val) values (1);
Query OK, 1 row affected (0.00 sec)

drizzle> select * from t2;
---------+

---------+

---------+
2 rows in set (0.00 sec)

I haven't come up with a way to fix this yet but sharing my findings so far.

Cheers,
Toru

Re: update is not changing internal auto increment value
it's updated for the 2nd case as there's code to update the autoinc vale when duplicate key is it... update_autoincrement_value or something like that in handler.

Re: update is not changing internal auto increment value
Patch that mimicks the MyISAM behaviour in doUpdateRecord() for innobase plugin.

Patch that mimicks the MyISAM behaviour in doUpdateRecord() for innobase plugin.
drizzle patch (should be easy to apply to others)
LPexportBug314570_innodb_autoinc_update.patch

Re: update is not changing internal auto increment value
All development of XtraDB has moved under the Percona Server project - https://launchpad.net/percona-server - If this bug can be reproduced against current Percona Server, please file this bug against percona-server (you can simply do so by using the "Also affects project" link above).

Thanks,
Stewart Smith
Director of Server Development
Percona.

Launchpad bug id: 314570

It's now documented as a feature in MySQL manual (e.g. http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html):

For InnoDB tables, be careful if you modify the column containing the auto-increment value in the middle of a sequence of INSERT statements. For example, if you use an UPDATE statement to put a new, larger value in the auto-increment column, a subsequent INSERT could encounter a “Duplicate entry” error. The test whether an auto-increment value is already present occurs if you do a DELETE followed by more INSERT statements, or when you COMMIT the transaction, but not after an UPDATE statement.