Description:
Found by Felix Geisendörfer, author of the Node.js MySQL driver.
Hi,
MySQL has auto_increment for columns to generate IDs. One can insert through a query IDs on its onw. It is allowed to have a signed auto_increment column and thus insert negative IDs. They are stored correctly.
The real problem comes from the protocol.
In sql/protocol.cc we have:
bool Protocol::send_ok(uint server_status, uint statement_warn_count,
ulonglong affected_rows, ulonglong last_insert_id,
const char *message)
the OK packet declares last_insert_id to be unsigned
In libmysql we have:
mysql.h: my_ulonglong insert_id; /* id if insert on table with NEXTNR */
The server sends unsigned value, the client obeys to that and uses the value as unsigned. However, what happens when negative value is inserted? See how to repeat for how to repeat :). After the insert wireshark dumps the following data for the OK packet.
0000 0f 00 00 01 00 01 fe 15 fc ff ff ff ff ff ff 02 ................
0010 00 00 00 ...
packet_length : 0f 00 00 = 15
packet_number : 01 = 1
affected_rows : 01 = 1
last_insert_id: fe 15 fc ff ff ff ff ff ff = 18,446,744,073,709,550,613
Here we see that the last_insert_id is not correct when dealt like unsigned number. 18,446,744,073,709,551,615 (hexadecimal 0xFFFFFFFFFFFFFFFF) is just 1003 positions from 2^64.
In short,
there is no way on the user side, except from guessing, to know whether a big last_insert_id is from the user or is wrongly transmitted. In the case above INTEGER and not BIGINT is used, thus with some research it is easy to find, but not in case BIGINT is used.
How to repeat:
mysql> create table t1(a integer auto_increment primary key);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (-1003);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+-------+
| a |
+-------+
| -1003 |
+-------+
1 row in set (0.00 sec)
Suggested fix:
New flag in the protocol whether the last_insert_id is negative, so the value can be treated correctly and reported correctly to the user.
Also the client APIs (C, PHP etc.) need to be changed - this will be an API break.
Description: Found by Felix Geisendörfer, author of the Node.js MySQL driver. Hi, MySQL has auto_increment for columns to generate IDs. One can insert through a query IDs on its onw. It is allowed to have a signed auto_increment column and thus insert negative IDs. They are stored correctly. The real problem comes from the protocol. In sql/protocol.cc we have: bool Protocol::send_ok(uint server_status, uint statement_warn_count, ulonglong affected_rows, ulonglong last_insert_id, const char *message) the OK packet declares last_insert_id to be unsigned In libmysql we have: mysql.h: my_ulonglong insert_id; /* id if insert on table with NEXTNR */ The server sends unsigned value, the client obeys to that and uses the value as unsigned. However, what happens when negative value is inserted? See how to repeat for how to repeat :). After the insert wireshark dumps the following data for the OK packet. 0000 0f 00 00 01 00 01 fe 15 fc ff ff ff ff ff ff 02 ................ 0010 00 00 00 ... packet_length : 0f 00 00 = 15 packet_number : 01 = 1 affected_rows : 01 = 1 last_insert_id: fe 15 fc ff ff ff ff ff ff = 18,446,744,073,709,550,613 Here we see that the last_insert_id is not correct when dealt like unsigned number. 18,446,744,073,709,551,615 (hexadecimal 0xFFFFFFFFFFFFFFFF) is just 1003 positions from 2^64. In short, there is no way on the user side, except from guessing, to know whether a big last_insert_id is from the user or is wrongly transmitted. In the case above INTEGER and not BIGINT is used, thus with some research it is easy to find, but not in case BIGINT is used. How to repeat: mysql> create table t1(a integer auto_increment primary key); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values (-1003); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +-------+ | a | +-------+ | -1003 | +-------+ 1 row in set (0.00 sec) Suggested fix: New flag in the protocol whether the last_insert_id is negative, so the value can be treated correctly and reported correctly to the user. Also the client APIs (C, PHP etc.) need to be changed - this will be an API break.