update is not changing internal auto increment value

[Aarti Pai]

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);

leads to:

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

For InnDB this fails, for MyISAM, it passes as MyISAM updates the auto increment value.

[Jay Pipes (jaypipes)]

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)

[Jay Pipes (jaypipes)]

Setting to critical...different storage engines should not produce different behaviour in this respect.

[Toru Maesaka (tmaesaka)]

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;
+---+------+
| a | val |
+---+------+
| 2 | 1 |
| 3 | 1 |
+---+------+
2 rows in set (0.00 sec)

[Toru Maesaka (tmaesaka)]

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;
+-----+------+
| a | val |
+-----+------+
| 100 | 1 |
+-----+------+
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;
+-----+------+
| a | val |
+-----+------+
| 2 | 1 |
| 100 | 1 |
+-----+------+
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;
+-----+------+
| a | val |
+-----+------+
| 100 | 1 |
+-----+------+
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;
+-----+------+
| a | val |
+-----+------+
| 100 | 1 |
| 101 | 1 |
+-----+------+
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

[Stewart Smith (stewart)]

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.

[Stewart Smith (stewart)]

Patch that mimicks the MyISAM behaviour in doUpdateRecord() for innobase plugin.

[Stewart Smith (stewart)]

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.