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:	10 Sep 2008 0:07	Modified:	10 Oct 2008 12:03
Reporter:	Lawrence Kesteloot
Status:	Closed
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:	5.0.x, 5.1.x	OS:	Any
Assigned to:		Target Version:
Triage:	D2 (Serious)

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.