A Lightweight SQL Database for Cloud Infrastructure and Web Applications

update is not changing internal auto increment value

Reported by Aarti Pai on 2009-01-07
8
Affects Status Importance Assigned to Milestone
Drizzle
Critical
Stewart Smith
Cherry
Critical
Stewart Smith
Maria
New
Low
Unassigned
MySQL Server
Unknown
Unknown
Percona-XtraDB
Invalid
Undecided
Unassigned

Bug Description

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) wrote :

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) wrote :

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

Changed in drizzle:
importance: Undecided → Critical
status: New → Confirmed
Toru Maesaka (tmaesaka) wrote :

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)

Lee Bieber (kalebral) on 2009-04-03
Changed in drizzle:
assignee: nobody → mordred
milestone: none → aloha
Toru Maesaka (tmaesaka) wrote :

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) wrote :

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.

Lee Bieber (kalebral) on 2009-08-18
Changed in drizzle:
assignee: Monty Taylor (mordred) → Stewart Smith (stewart-flamingspork)
milestone: aloha → bell
Lee Bieber (kalebral) on 2010-01-26
Changed in drizzle:
milestone: bell → cherry
Brian Aker (brianaker) on 2010-05-21
Changed in drizzle:
status: Confirmed → Fix Released
status: Fix Released → Confirmed
Lee Bieber (kalebral) on 2010-05-21
Changed in drizzle:
milestone: 2010-05-24 → none
Stewart Smith (stewart) on 2010-06-02
description: updated
Stewart Smith (stewart) wrote :

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

Stewart Smith (stewart) on 2010-06-02
Changed in drizzle:
status: Confirmed → Fix Committed
Sergei (sergii) on 2010-06-02
Changed in maria:
importance: Undecided → Low
Stewart Smith (stewart) on 2010-06-04
Changed in drizzle:
status: Fix Committed → Fix Released
Stewart Smith (stewart) wrote :

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.

Changed in percona-xtradb:
status: New → Invalid
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.