Comment 4 for bug 314570

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