Description:
During verifying Bug #31971, I figured out a strange behaviour of MySQL 5.1.22 and InnoDB:
I made the following tests with MySQL 5.1.22
mysql> create table a(id serial,txt varchar(100), used integer, primary key(id),
unique(txt)) engine=innodb;
mysql> create table b(id serial,txt varchar(100), used integer, primary key(id),
unique(txt)) engine=myisam;
mysql> insert into a(txt,used) values ('abc',1) on duplicate key update used=used+1;
mysql> insert into b(txt,used) values ('abc',1) on duplicate key update used=used+1;
mysql> insert into a(txt,used) values ('abc',1) on duplicate key update used=used+1;
mysql> insert into b(txt,used) values ('abc',1) on duplicate key update used=used+1;
Both tables have had one row inserted and the used was set to 2.
mysql> select last_insert_id() from a; and after this from b:
Result for Innodb and MyIsam: 1
mysql> insert into a(txt,used) values ('abc',1) on duplicate key update used=used+1;
mysql> insert into b(txt,used) values ('abc',1) on duplicate key update used=used+1;
Both tables have had one row inserted and the used was set to 3.
mysql> select last_insert_id() from a; and after this from b:
Result from MySQL 5.1.22 for Innodb and MyIsam: 1
mysql> insert into a(txt,used) values ('def',1) on duplicate key update used=used+1;
mysql> insert into b(txt,used) values ('def',1) on duplicate key update used=used+1;
mysql> select * from a; and after this from b;
Innodb;
+----+------+------+
| id | txt | used |
+----+------+------+
| 1 | abc | 3 |
| 4 | def | 1 |
+----+------+------+
MyIsam:
+----+------+------+
| id | txt | used |
+----+------+------+
| 1 | abc | 3 |
| 2 | def | 1 |
+----+------+------+
mysql> select last_insert_id() from a; and after this from b;
Result: 2 for both.
Innodb should have 4 as result here or should insert 2 instead of 4 like MySQL 5.0 does.
How to repeat:
Like described
Suggested fix:
Check the count mechanism
