MySQL Bugs: #16841: Updateable ResultSet does not always return correctly the last insert id

Bug #16841	Updateable ResultSet does not always return correctly the last insert id
Submitted:	27 Jan 2006 14:07	Modified:	10 Mar 2006 20:28
Reporter:	lucho balev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.12	OS:	Linux (Linux Mandrake 9.2)
Assigned to:		CPU Architecture:	Any

Description:
I made a simplified example of a real-life situation below.

1) Let we have consider the following table:

CREATE TABLE Visits 
(
CID int( 20 ) NOT NULL default '0',
OID int( 20 ) NOT NULL AUTO_INCREMENT ,
PatientID int( 20 ) default NULL ,
PRIMARY KEY ( CID , OID ) ,
KEY OID ( OID ) ,
KEY Path ( CID, PatientID)
) ENGINE = MYISAM ;

OID is autoincrement and is part of the primary key. Also it is a key itself.

2) We insert rows, the code snapshot is below:

String sSQLQuery = "SELECT * FROM Visits WHERE 1 = 0";
Statement stmt = 
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet rSet = stmt.executeQuery(sSQLQuery);

rSet.moveToInsertRow();

rSet.updateInt("CID", 1);
rSet.updateInt("PatientID", 1);

rSet.insertRow();

3) Further we go to the last row and get the last inserted id.

rSet.last();
System.out.println("Last insert ID: " + rSet.getInt("OID"));

4) We get:
Last insert ID: 0

Question - why this happens, since OID is auto increment? 

How to repeat:
It can aways be reproduced.

Suggested fix:
No suggestions.

The example works correctly both on command line :
mysql> insert into Visits (CID, PatientID) values (1,1);
Query OK, 1 row affected (0.04 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)

mysql> insert into Visits (CID, PatientID) values (1,1);
Query OK, 1 row affected (0.03 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

And also by using libmysql (the example uses mysqli):
andrey@lmy004:/work/mysql-5.1-new/sql> php -r '$c=mysqli_connect("localhost","root","","test");$c->query("insert into Visits (CID, PatientID) values (1,1)");var_dump(mysqli_insert_id($c));'
int(3)
andrey@lmy004:/work/mysql-5.1-new/sql> php -r '$c=mysqli_connect("localhost","root","","test");$c->query("insert into Visits (CID, PatientID) values (1,1)");var_dump(mysqli_insert_id($c));'
int(4)

and also with PS:

mysql> use test;
Database changed
mysql> prepare XXX from "insert into Visits (CID, PatientID) values (1,1);";
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> execute XXX;
Query OK, 1 row affected (0.01 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

Andrey's comment is OK. last_insert_id() works always correctly. I suppose that this is a problem with the driver itself. The problem is in the complex primary key maybe, when I dropped the two other keys it still did not work.

Besides the:

Statement.getGeneratedKeys() 

method works correctly in this situation.

The driver assumes this only works with a single primary key that is auto-increment:

if ((numPrimaryKeys == 1) && this.fields[i].isPrimaryKey()
					&& (autoIncrementId > 0)) {
				newRow[i] = String.valueOf(autoIncrementId).getBytes();
			}

I'll see if we can come up with some better logic, depending on what the MySQL server actually returns for field-level metadata.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2079

Fix will be available in 5.0.1 and 3.1.13. You can try a nightly snapshot from http://downloads.mysql.com/snapshots.php#connector-j if you want to test the fix before it's officially released. Thanks for the bug report.