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

[27 Jan 2006 14:07] lucho balev
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.
[27 Jan 2006 14:27] Andrey Hristov
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)
[27 Jan 2006 15:59] lucho balev
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.
[2 Feb 2006 16:54] Mark Matthews
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.
[2 Feb 2006 17:55] Bugs System
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
[10 Mar 2006 20:28] Mark Matthews
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.