| Bug #36608 | Providing AI column value returns that value as LAST_INSERT_ID() over the wire | ||
|---|---|---|---|
| Submitted: | 9 May 2008 2:53 | Modified: | 1 Oct 2008 12:16 |
| Reporter: | Kazuo Kashima | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | auto_increment, Connector/J, getgeneratedkeys, jdbc | ||
[12 Jun 2008 12:53]
Tonci Grgin
Hi Kazuo and thanks for your report.
I must say I agree with you... Even more as server returns 0 for SELECT LAST_INSERT_ID() when value of auto-increment column is provided:
this.rs = this.stmt.executeQuery("SELECT LAST_INSERT_ID()");
if (rs.next()) {
System.out.println("Generated key = " + this.rs.getInt(1));
}
Verified just as described on WinXP Pro SP2 localhost running mysql-5-0-64-pb1103-win32 with java.vm.version 1.5.0_12-b04 and latest c/J 5.1 sources.
[1 Oct 2008 10:17]
Tonci Grgin
After review, I find this to be *unexpected* behaviour according to http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id Let me try to explain, when no key is generated last_insert_id() should be 0 but there is a bug in server protocol visible on the wire. Let's examine wireshark dump (pay attention to frame 33 Last INSERT ID value!): No. Time Source Destination Protocol Info 30 0.126120 10.0.0.87 10.0.0.85 MySQL Request Query Frame 30 (176 bytes on wire, 176 bytes captured) Ethernet II, Src: Nvidia_01:78:e9 (00:04:4b:01:78:e9), Dst: Foxconn_27:8e:0e (00:15:58:27:8e:0e) Internet Protocol, Src: 10.0.0.87 (10.0.0.87), Dst: 10.0.0.85 (10.0.0.85) Transmission Control Protocol, Src Port: vlsi-lm (1500), Dst Port: mysql (3306), Seq: 1182, Ack: 5655, Len: 122 MySQL Protocol Packet Length: 118 Packet Number: 0 Command Command: Query (3) Statement: CREATE TABLE testBug36608 (autoIncId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, uniqueTextKey VARCHAR(255) UNIQUE KEY) No. Time Source Destination Protocol Info 31 0.181577 10.0.0.85 10.0.0.87 MySQL Response OK Frame 31 (65 bytes on wire, 65 bytes captured) Ethernet II, Src: Foxconn_27:8e:0e (00:15:58:27:8e:0e), Dst: Nvidia_01:78:e9 (00:04:4b:01:78:e9) Internet Protocol, Src: 10.0.0.85 (10.0.0.85), Dst: 10.0.0.87 (10.0.0.87) Transmission Control Protocol, Src Port: mysql (3306), Dst Port: vlsi-lm (1500), Seq: 5655, Ack: 1304, Len: 11 MySQL Protocol Packet Length: 7 Packet Number: 1 Affected Rows: 0 Server Status: 0x0002 Warnings: 0 No. Time Source Destination Protocol Info 32 0.185218 10.0.0.87 10.0.0.85 MySQL Request Query Frame 32 (125 bytes on wire, 125 bytes captured) Ethernet II, Src: Nvidia_01:78:e9 (00:04:4b:01:78:e9), Dst: Foxconn_27:8e:0e (00:15:58:27:8e:0e) Internet Protocol, Src: 10.0.0.87 (10.0.0.87), Dst: 10.0.0.85 (10.0.0.85) Transmission Control Protocol, Src Port: vlsi-lm (1500), Dst Port: mysql (3306), Seq: 1304, Ack: 5666, Len: 71 MySQL Protocol Packet Length: 67 Packet Number: 0 Command Command: Query (3) Statement: insert into testBug36608 (autoIncId, uniqueTextKey) values (1,'c') No. Time Source Destination Protocol Info 33 0.186120 10.0.0.85 10.0.0.87 MySQL Response OK Frame 33 (65 bytes on wire, 65 bytes captured) Ethernet II, Src: Foxconn_27:8e:0e (00:15:58:27:8e:0e), Dst: Nvidia_01:78:e9 (00:04:4b:01:78:e9) Internet Protocol, Src: 10.0.0.85 (10.0.0.85), Dst: 10.0.0.87 (10.0.0.87) Transmission Control Protocol, Src Port: mysql (3306), Dst Port: vlsi-lm (1500), Seq: 5666, Ack: 1375, Len: 11 MySQL Protocol Packet Length: 7 Packet Number: 1 Affected Rows: 1 Last INSERT ID: 1 Server Status: 0x0002 Warnings: 0 So, this is a server bug or, at least, a bug in documentation. Classifying it as such. Synopsis changed to: "Providing AI column value in insert statement returns that value as LAST_INSERT_ID() over the wire which is clearly in contradiction with manual http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id"
[1 Oct 2008 12:16]
Tonci Grgin
I stand corrected by Joro... !Bg. "The value of LAST_INSERT_ID() is not changed if you set the AUTO_INCREMENT column of a row to a non-“magic” value (that is, a value that is not NULL and not 0)." I will ping Mark now and see if anything could be done in c/J to bypass this behaviour.
[1 Oct 2008 12:50]
Mark Matthews
> I have a table with an AUTO_INCREMENT field. After an SQL inserts a row, getGeneratedKeys > always returns the value of AUTO_INCREMENT field of last insertion even when an SQL has > specified a value to it. This is as-intended. The server doesn't return enough information for the driver to tell if the key was *generated* or supplied, and short of putting a full SQL parser in the JDBC driver there's no way to fix it client-side. Unfortunately it is up to the application developer to know whether or not they supplied the value, which to me does not sound onerous.
Description: I have a table with an AUTO_INCREMENT field. After an SQL inserts a row, getGeneratedKeys always returns the value of AUTO_INCREMENT field of last insertion even when an SQL has specified a value to it. I'm not sure how it should work, but JavaDoc says "getGeneratedKeys() Retrieves any auto-generated keys created as a result of executing this Statement object.", so I expect the method to return a value only when a value is generated. How to repeat: CREATE TABLE `test_autogeneratedkeys` ( `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `F1` VARCHAR(45) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE = InnoDB; Statement st = con.createStatement(); st.executeUpdate("INSERT INTO test_autogeneratedkeys (ID, F1) VALUES (200, 'abc')"); ResultSet rs = st.getGeneratedKeys(); if (rs.next()) { // The code comes here. System.out.println("Generated key = " + rs.getInt(1)); } else { // I expect this. System.out.println("No generated keys."); }