MySQL Bugs: #39352: INSERT ON DUPLICATE returns 1 instead of 0 when nothing is changed

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

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.

Shows bug with affected-rows.

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

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.

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)

Test case

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

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.

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.

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.

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.

Bug#42087 was marked as duplicate of this report.

Bug #61134 was marked as a duplicate of this one.