MySQL Bugs: #43311: Wrong number of affected rows returned for insert on duplicate key

Bug #43311	Wrong number of affected rows returned for insert on duplicate key
Submitted:	2 Mar 2009 20:23	Modified:	3 Apr 2009 7:38
Reporter:	Dan Wilks	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.77	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.0/en/mysql-affected-rows.html (and other places) states "If you use INSERT ... ON DUPLICATE KEY UPDATE  to insert a row, mysql_affected_rows() returns 1 if the row is inserted as a new row and 2 if an existing row is updated" and I believe versions prior to 5.0.45 had this behavior.  In 5.0.45, 5.0.51, and 5.0.77 if the new row is a duplicate but the update sets all the fields to their current value then the affected rows is 1 rather than the expected 2.  This makes it impossible to differentiate between the row being added and a duplicate row in which nothing happened.

How to repeat:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

public class MysqlDuplicateInsertTest {

    public static void main (String[] args)
    {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection c = DriverManager.getConnection("jdbc-url", "user", "passwd");
            Statement s = c.createStatement();

            ResultSet rs = s.executeQuery("select version()");
            if (rs.next()) {
                String ver = rs.getString(1);
                System.out.println("MySql version: " + ver);
            } else {
                System.out.println ("Unable to determine version.");
            }

            s.executeUpdate("drop table if exists duprowtest");
            s.executeUpdate("create table duprowtest (id int(10) primary key, name varchar(255))");
            s.executeUpdate("insert into duprowtest (id, name) values (1, 'a')");
            int c1 = s.executeUpdate("insert into duprowtest (id, name) values (1, 'a') on duplicate key update name = 'b'");
            System.out.println ("First insert: " + c1);
            int c2 = s.executeUpdate("insert into duprowtest (id, name) values (1, 'a') on duplicate key update name = 'b'");
            System.out.println ("Second insert: " + c2);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Generates The following output

MySql version: 5.0.77-log
First insert: 2
Second insert: 1

Suggested fix:
I don't know if it would be better to revert to the old behavior of returning 2 on updates regardless of if an update actually changes any data or to return 0 in the case of an update not changing anything - although 0 might be ambiguous with the insert simply failing.

Thank you for the report.

mysql_affected_rows is C API function and not function of Connector/J test case you provided for which. Anyway I tested with C program and got expected results (C test case will be attached):

$./bug43311
Affected rows: 2
Affected rows: 0

Second result 0 is correct, because second query really does not update any row.

In case if flag CLIENT_FOUND_ROWS, like Connector/J uses, is set mysql_affected_rows returns correct values too:

$./bug43311
Affected rows: 3
Affected rows: 2

So I reclassify this bug as Connector/J bug for additional testing.

C test case with flag CLIENT_FOUND_ROWS set

Attachment: bug43311.c (text/plain), 985 bytes.

Hm.. Seems I was hurry.

Results with server 5.0:

$java bug43311
MySql version: 5.0.79-debug
First insert: 2
Second insert: 1

$./bug43311
Affected rows: 2
Affected rows: 1

With server 5.1 and 6.0:

$./bug43311
Affected rows: 3
Affected rows: 2

$java  bug43311
MySql version: 6.0.10-alpha-debug
First insert: 3
Second insert: 2

This is a dupliate of bug #42087

Thank you for investigating this problem.  I think you meant to make this a duplicate of bug#39352 rather than bug#42087.

There are also still a few documentation problems that could go a long way to helping others avoid this problem:

The current 5.1 Connector/J docs (http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html) doesn't mention the new (5.1.7) useAffectedRows property.

There are at least three places I found in the documentation really should describe "real" vs "no-op" updates and how CLIENT_FOUND_ROWS interact to affect the return value:

mysql_affected_rows() (http://dev.mysql.com/doc/refman/5.1/en/mysql-affected-rows.html) just says "If you use INSERT ... ON DUPLICATE KEY UPDATE  to insert a row, mysql_affected_rows() returns 1 if the row is inserted as a new row and 2 if an existing row is updated."

The insert on duplicate key section (http://dev.mysql.com/doc/refman/5.1/en/insert.html)just says "If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. 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. See Section 12.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”. "

The referenced 12.2.5.3 (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html) only says "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. "

In any case in the deeper analysis this is a duplicate of bug #42087.

We will fix the documentation together with the fix for bug #42087.