MySql java driver returns 1 when executing ON DUPLICATE KEY UPDATE v=v

Question

I'm executing the same sql command having ON DUPLICATE KEY UPDATE v=v in 2 ways.

Command: INSERT INTO VS (v) VALUES ('someValue') ON DUPLICATE KEY UPDATE v=v;

Two way giving different results:

1. When using a native mysql client and a constraint is met, the number of rows affected is 0. Message: Query OK, 0 rows affected (0.14 sec)
2. When using mysql jdbc driver (mysql-connector-java-8.0.11.jar), and executing the same command using int res = ps.executeUpdate(...), the result is 1.

From the java documentation, the result is the the row count for SQL Data Manipulation Language (DML) statements, but no update, nor inserts were made.

Also, calling ps.getUpdateCount(), after the execute, returns 1 as well.

Why is the result in the mysql java driver 1, where nothing was supposed to be updated? Thanks.

Just for clarity, the table creation looks like this: CREATE TABLE IF NOT EXISTS VS (v VARCHAR(128) NOT NULL, PRIMARY KEY (v))

Answer 1

See e.g. mysql_affected_rows():

For INSERT ... ON DUPLICATE KEY UPDATE statements, 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, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

See JDBC connection property useAffectedRows:

useAffectedRows

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.

Default: false

Since version: 5.1.7

So, if you want the non-standard behavior of update counts to only count affected rows, instead of the standard count of found rows, you need to specify that connetion property.