Bug #67375 ON DUPLICATE KEY UPDATE produces wrong affectedRows
Submitted: 25 Oct 2012 5:46 Modified: 13 Jan 2014 17:58
Reporter: Nikhil Wankhade Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.5 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[25 Oct 2012 5:46] Nikhil Wankhade
Description:
When i insert with " on duplicate on update var1=value1" clause (UNIQUE key) i am getting wrong result in a return response in JDBC using prepared statement. I am getting return results as follows.....
....
PreparedStatements pst;
......
..
pst.executeUpdate();
..

    RETURNS | DESC.

    1       | when new key and new field both not exists that is new row inserted
    2       | when the key same, and existing row is updated
    1       | when the key is the same, and the field is also same

How to repeat:
When i insert with " on duplicate on update var1=value1" clause (UNIQUE key) i am getting wrong result in a return response in JDBC using prepared statement. I am getting return results as follows.....
....
PreparedStatements pst;
......
..
pst.executeUpdate();
..

    RETURNS | DESC.

    1       | when new key and new field both not exists that is new row inserted
    2       | when the key same, and existing row is updated
    1       | when the key is the same, and the field is also same

Suggested fix:
expected results.....

RETURNS | DESC.
 0 when both the key is the same, and the update statement doesn't change anything
 1 when the key differs, so a row is inserted
 2 when the key is the same, and the row is updated
[25 Oct 2012 8:10] Valeriy Kravchuk
I am not sure this is a server bug. While our manual does not clearly document 0 for the case when there is no value change in existing row (), we see it is 0 when mysql command line client is used:

mysql> select * from tp where c3=2;
+----+------------+------+
| c1 | c2         | c3   |
+----+------------+------+
|  2 | 2012-02-01 |    2 |
+----+------------+------+
1 row in set (0.12 sec)

mysql> update tp set c3=2 where c1=2;
Query OK, 0 rows affected (0.49 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> insert into tp values(2, '2012-02-01', 2) on duplicate key update c3 = va
lues(c3);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into tp values(2, '2012-02-01', 2) on duplicate key update c3 = 2;
Query OK, 0 rows affected (0.02 sec)

So, maybe this is a problem of connector that you use.
[25 Oct 2012 10:27] Nikhil Wankhade
can you specify me on which type of connector and MySql DB version you had used for testing this query.
[25 Oct 2012 10:41] Nikhil Wankhade
my sql command line client here also show o rows affected but the problem is that when the same query is used JDBC API i.e. with PreparedStatement on firing executeUpdate() method it returns 1 but noting is updated there...
[29 Nov 2012 19:30] Sveta Smirnova
Thank you for the report.

Please specify exact version of MySQL server you use and provide repeatable test case, demonstrating the problem like Valeriy did.
[30 Dec 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[7 Jan 2014 12:38] Tushar Tiwari
The problem is in JDBC: 
1. If record exists on insert & no changes occur on duplicate key update: mysql console returns 0; executeUpdate() and getUpdateCount() return 1. This is wrong.
2. If record exists on insert & changes occur on duplicate key update: mysql console returns 2; executeUpdate() and getUpdateCount() return 2. This is right.
3. If record does not exist: mysql console returns 1; executeUpdate() and getUpdateCount() return 1. This is also right.
[7 Jan 2014 12:40] Tushar Tiwari
The version I am using:
mysql: 5.5.34
connector: 5.1.28
[8 Jan 2014 22:30] Matt Ball
I can reproduce this issue exactly as described. The manual does clearly document [1] that 0 is returned for the case when there is no value change in the existing row:

> With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an 
> existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS
> flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing 
> row is set to its current values.

[1]: http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

Server version: 5.5.33-31.1-log Percona Server (GPL), Release rel31.1, Revision 566
Connector/J version: 5.1.26
[8 Jan 2014 23:47] Matt Ball
This bug's category should be Connector/J, not Server, and it affects Linux as well as Windows (probably OS-independent).
[11 Jan 2014 22:54] Matt Ball
Test case to reproduce the bug in #67375

Attachment: BugReport67375.java (application/octet-stream, text), 3.14 KiB.

[11 Jan 2014 22:59] Matt Ball
The test case above also fails with Connector/J 5.1.28.
[13 Jan 2014 17:58] Sveta Smirnova
Thank you for the test case and additional details.

This is not a bug: you should've been using useAffectedRows configuration property as described at http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-configuration-properties.html
[13 Jan 2014 18:08] Matt Ball
Sveta - thanks so much for the quick followup.

The documentation on useAffectedRows is not clear. It says:

> Don't set the CLIENT_FOUND_ROWS flag when connecting to the
> server (not JDBC-compliant, will break most applications that rely
> on "found" rows vs. "affected rows" for DML statements), but does
> cause "correct" update counts from "INSERT ... ON DUPLICATE
> KEY UPDATE" statements to be returned by the server.

Which configuration is not JDBC-compliant? Setting useAffectedRows to true?
[13 Jan 2014 19:17] Sveta Smirnova
> Which configuration is not JDBC-compliant? Setting useAffectedRows to true?

Yes.