MySQL Bugs: #15445: Strange result from Statement.getGeneratedKeys

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:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.12	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
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

Test case

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

tested against mysql 4.1.16 and mysql-connector-java-3.1.12, got:

Start

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.

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.

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.

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.

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.