Bug #15445 Strange result from Statement.getGeneratedKeys
Submitted: 2 Dec 2005 19:45 Modified: 5 Dec 2005 16:23
Reporter: Bogdan Degtyariov Email Updates:
Status: Unsupported Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version:3.1.12 OS:Linux (linux)
Assigned to: CPU Architecture:Any

[2 Dec 2005 19:45] Bogdan Degtyariov
Statement.getGeneratedKeys function returns a strange result. Attached file demonstrates the problem very well. Despite the fact that no new records were inserted it returns the keys for non-existing records.

How to repeat:
Just run Main.java program
[2 Dec 2005 19:45] Bogdan Degtyariov
Test case

Attachment: Main.java (text/java), 2.15 KiB.

[5 Dec 2005 10:00] Aleksey Kishkin
tested against mysql 4.1.16 and mysql-connector-java-3.1.12, got:


Set up

Test 1
Key generated =1
Key generated =2

Table contents
Key 1 Val five
Key 2 Val six

Test 2
Key generated =2
Key generated =3
Key generated =4
Key generated =5

Table contents
Key 1 Val one
Key 2 Val two

Test 3
Key generated =20
Key generated =21

Table contents
Key 1 Val one
Key 2 Val two
Key 10 Val ten
Key 20 Val twenty

Clean up

looks like test 2 and test 3 returns wrong results.
[5 Dec 2005 16:23] Mark Matthews
I see what's going on here. The issue is that the code is putting in values for the auto-increment. This isn't supported with .getGeneratedKeys() as the server itself doesn't return information that allows _any_ client to figure out what keys were generated for this case. It only returns the last auto-generated key, which means the client has to assume that they monatomically decrease from there, given the number of rows "touched".

With the given scenario (code wants to "touch" the autoincrement values themselves in _some_ cases), and what the server returns for information today, we can not support this functionality in any client, much less the JDBC driver.
[6 Dec 2005 11:27] David Newcomb
If the current version of MySQL client can only return 1 value, then maybe you could make the connector/J just return 1 value. This would be better than spurious results which don't match up with anything.
I am currently spliting my sql from all REPLACEs to some REPLACEs and some INSERTs. I am excepting keys from the inserts but not from the REPLACEs.
If I know they count backwards then I am ok.
[6 Dec 2005 15:49] Mark Matthews
The issue is that the feature works as-intended when you're using auto-increments the way most people use them, which is you let the database assign them, and you don't populate them with your own values.

There's quite a few users using that functionality. The best we can do is document that the feature doesn't work when you supply values for some of the auto-increment values in a REPLACE statement.
[4 Jan 2006 9:20] David Newcomb
The whole point of using a REPLACE statement (instead of using an INSERT statement) _is_ the fact you already know what the auto_increment value is)! Using REPLACE is more convineient than just doing INSERTS and UPDATES. I still think it should just return 1 correct value instead of spurious (possibly) incorrect results.