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: | |
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
[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.