| 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: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.5 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[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.
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