Bug #61850 JDBC: return value of INSERT ON DUPLICATE KEY does not match command line client
Submitted: 13 Jul 2011 8:19 Modified: 13 Jul 2011 8:47
Reporter: René Vangsgaard Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1 OS:Linux (Squeeze)
Assigned to: CPU Architecture:Any
Tags: jdbc, ON DUPLICATE KEY UPDATE

[13 Jul 2011 8:19] René Vangsgaard
Description:
When I execute INSERT ON DUPLICATE KEY UPDATE (IODKU) in Mysql command line interface, it returns 2 rows updated, when the INSERT hits a duplicate key constraint.

In JDBC it returns 3 rows updated.

How to repeat:
In a table with a primary key, try to insert a row which violates the unique key constraint, using native mysql client and JDBC.

Example query:
INSERT INTO session (tstamp, session_id, key1, key2, key3 VALUES ('2011-07-13 10:08:01', 2, 21, 22, 23) ON DUPLICATE KEY UPDATE tstamp='2011-07-13 10:08:01'

Suggested fix:
From my limited testing, it seems that I can rely on the value 3 instead of 2.
[13 Jul 2011 8:21] René Vangsgaard
Mysql Connector/J version 5.1.14
[13 Jul 2011 8:28] Tonci Grgin
Hi René and thanks for your report.

The value returned depends on your settings. So, if you don't mind, please attach complete test case and do check BugsDB for exactly the same reports which I remember do exist.
[13 Jul 2011 8:47] René Vangsgaard
Hi Tonci, thank you for your swift reply.

You are correct, I did not do my homework thoroughly enough, as I found a similar bug in the bug database. I probably hit this bug: http://bugs.mysql.com/bug.php?id=46675

Setting the useAffectedRows is not a great solution, as it breaks JDBC compability. As a workaround I will just check if "affected rows >= 2".

I will close this bug.
[13 Jul 2011 8:55] Tonci Grgin
No problem René, glad it's solved.