Bug #39352 INSERT ON DUPLICATE returns 1 instead of 0 when nothing is changed
Submitted: 9 Sep 2008 22:07 Modified: 10 Oct 2008 10:03
Reporter: Lawrence Kesteloot Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.0.x, 5.1.x OS:Any
Assigned to: CPU Architecture:Any

[9 Sep 2008 22:07] Lawrence Kesteloot
Description:
According to this page:

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

"With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated."

When using JDBC, if the insert fails (because of duplicate key) and the update does nothing (because the new data is identical to the existing data), the affected-rows value is 1, which implies that the data was inserted. The command line client correctly returns zero. This may be because of the CLIENT_FOUND_ROWS flag. (There are no auto-generated keys in my table, if that makes a difference.) I've reproduced this on 64-bit Linux (5.0.45) and on Windows (5.0.67) with Connector/J 5.0.8.

With this bug I cannot distinguish between an insert and a no-op update using JDBC. This distinction is important for my application.

How to repeat:
I will attach a test program.
[9 Sep 2008 22:08] Lawrence Kesteloot
Shows bug with affected-rows.

Attachment: on_duplicate.java (text/java), 1.54 KiB.

[9 Sep 2008 22:23] Justin Lebar
Verified the same behavior with Connector/J 5.1.6.

Perhaps to avoid breaking functionality that other programs rely on, we could be given the option to turn client_rows_found on/off for a given connection.
[10 Sep 2008 10:33] Tonci Grgin
Hi Lawrence and thanks for your report.

Verified just as described with test case attached on Mysql server 5.0.68PB and latest c/J 5.1 sources. Mysql cl client works as expected.

mysql> DROP TABLE IF EXISTS bug39352;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE  TABLE bug39352 (id INT PRIMARY KEY, data VARCHAR(100));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO bug39352 (id,data) values (1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO bug39352 (id, data) VALUES(2, 'bb') ON DUPLICATE KEY UPDATE data=values(data);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO bug39352 (id, data) VALUES(2, 'bbb') ON DUPLICATE KEY UPDATE data=values(data);
Query OK, 2 rows affected (0.00 sec)

mysql> INSERT INTO bug39352 (id, data) VALUES(2, 'bbb') ON DUPLICATE KEY UPDATE data=values(data);
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS bug39352;
Query OK, 0 rows affected (0.00 sec)
[10 Sep 2008 10:33] Tonci Grgin
Test case

Attachment: TestBug39352.java (text/java), 2.07 KiB.

[29 Sep 2008 14:58] Mark Matthews
This is because of CLIENT_FOUND_ROWS, we need to make an option for the driver to not use this flag, even though it's non-JDBC-compliant to do so.

Alternatively, the server itself could be *smarter* about how it returns update counts for ON DUPLICATE KEY UPDATE rather than all of the non-distinguishable "magic" values it does now.
[29 Sep 2008 15:32] Mark Matthews
We've added a connection property "useAffectedRows" that allows the driver to connect without setting "CLIENT_FOUND_ROWS", which should be a work-around to this issue, however it can only be set at connect time due to mysqld design, so if your application requires "found rows" functionality in other places, you'll have to maintain two sets of connections.

The fix is in the bzr repo, and will be released as part of 5.1.7.
[29 Sep 2008 17:38] Lawrence Kesteloot
I'm okay with this solution as long as the documentation is updated to say that Connector/J will return 1 for no-op unless useAffectedRows is set. Your other proposed solution (of having the server be smart in its return value so that it's always correct) would be preferable, of course.
[10 Oct 2008 10:03] Tony Bedford
Noted that the Connector/J Connection Properties file should be updated automatically from source code to include the new 'useAffectedRows' option, but will contain something along the lines of:

            <row>
              <entry>useAffectedRows</entry>
              <entry>This allows the driver to connect without setting the
                <literal>CLIENT_FOUND_ROWS</literal> flag. Set this
                value to true to ensure that
                <literal>affected-rows</literal> contains the correct
                value when using <literal>INSERT ON DUPLICATE KEY
                UPDATE</literal>. This option provides a workaround for
                BUG#39352.</entry>
              <entry>false</entry>
              <entry>5.1.7</entry>
            </row>

Also, added entry to 5.1.7 changelog:

When an INSERT ON DUPLICATE KEY UPDATE was performed, and the key already existed, the affected-rows value was returned as 1 instead of 0.
[13 Jan 2009 16:02] Tonci Grgin
Bug#42087 was marked as duplicate of this report.
[12 May 2011 6:42] Valeriy Kravchuk
Bug #61134 was marked as a duplicate of this one.