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.