Bug #28412 insert_id in OK Packet not working with multiple rows INSERT
Submitted: 14 May 2007 11:35 Modified: 14 May 2007 11:47
Reporter: Geert Vanderkelen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.0.41 OS:Any
Assigned to: CPU Architecture:Any
Tags: auto_increment, protocol
Triage: Triaged: D5 (Feature request)

[14 May 2007 11:35] Geert Vanderkelen
Description:
This is probably nitpicking, but I scratched my head over it in the weekend just to find out that the mysql CLI was doing the same as my own implementation of the MySQL Protocol.

When you do a CMD_QUERY on a table with an auto_increment field, like this:
  INSERT INTO t1 VALUES ('Geert'),('Jan'),('Michel')

The OK Result packet's insert_id field is not 3, but 1. So it returns not the last one, but actually the first one.

It is documented that is should return the 'last INSERT id', though, probably not for multiple row INSERT statements.

The same is true for INSERT INTO .. SELECT ..

(Checked 4.1.22, 5.0.41 and 5.1.16)

How to repeat:
I'm using Wireshark to check the packets, but one can easily check it with tcpdump too (note, just 1 INSERT is fine):

mysql> create table t1 (id int key auto_increment, name varchar(20));
mysql> insert into t1 (name) values ('Geert'),('Jan'),('Michel');
mysql> delete from t1;
mysql> insert into t1 (name) values ('Geert'),('Jan'),('Michel');
mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  4 | Geert  |
|  5 | Jan    |
|  6 | Michel |
+----+--------+

The OK Packet after the INSERT is:

0000  00 00 00 00 00 00 00 00  00 00 00 00 08 00 45 08   ........ ......E.
0010  00 66 9f cb 40 00 40 06  9c bc 7f 00 00 01 7f 00   .f..@.@. ........
0020  00 01 0c ea ec 02 f8 90  90 af f7 b6 31 6a 80 18   ........ ....1j..
0030  20 00 fe 5a 00 00 01 01  08 0a 00 00 81 bb 00 00    ..Z.... ........
0040  81 bb 2e 00 00 01 00 03  04 02 00 00 00 26 52 65   ........ .....&Re
0050  63 6f 72 64 73 3a 20 33  20 20 44 75 70 6c 69 63   cords: 3   Duplic
0060  61 74 65 73 3a 20 30 20  20 57 61 72 6e 69 6e 67   ates: 0   Warning
0070  73 3a 20 30                                        s: 0             

The integer value at 0x48 says insert_id is '4', but in fact it should, or is expected by me to be '6'.

Suggested fix:
I think this is a problem in the protocol and should be changed to give the actual last one. This would prevent the client to actually go do an extra CMD_QUERY with SELECT LAST_INSERT_ID().

Or we can document that it doesn't work with multiple row INSERT statements, but that doesn't feel right.
[14 May 2007 11:45] Geert Vanderkelen
OK, discussing this, it's a documented problem/bug:

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

So, it's also true for the OK Packet in the protocol. I'll add that to the Forge.

Putting this as a feature request in hopes it might change?
[14 May 2007 11:47] Valeriy Kravchuk
Thank you for a reasonable feature request. Indeed, the behaviour you want may be really useful in many cases.