MySQL Bugs: #28505: Update count for an INSERT DUPLICATE KEY not correct when CLIENT_FOUND

Bug #28505	Update count for an INSERT DUPLICATE KEY not correct when CLIENT_FOUND_ROWS set
Submitted:	18 May 2007 2:19	Modified:	19 Jun 2007 2:56
Reporter:	Chris Lamprecht
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	3.1.10, and 5.0.6	OS:	Any
Assigned to:	Evgeny Potemkin	Target Version:
Tags:	connectorj, LAST_INSERT_ID, getgeneratedkeys, jdbc

Description:
Background:  It started with bug 19978, which changed the behavior of the rows affected
count for INSERT...ON DUPLICATE KEY statements where rows were 'touched' but no data was
actually changed.  This had side effects that caused bug 27033, where LAST_INSERT_ID()
would return 0 instead of the matched unique key, when a duplicate key was detected for
an INSERT...ON DUPLICATE KEY statement.  

Those two bugs were fixed, and I verified this in MySQL 5.0.41 using the mysql
command-line shell.  However, the Connector/J JDBC driver now returns an empty ResultSet
for statement.getGeneratedKeys() in this situation when the duplicate key is detected for
an INSERT...ON DUPLICATE KEY statement.  Interestingly, if you issue a "SELECT
LAST_INSERT_ID()" after the INSERT, the correct key value is still returned (this is a
possible workaround, but it's MySQL-specific).

I am including a small Java testcase that reproduces the problem.  If you run it on an
older version of MySQL, such as 5.0.27, statement.getGeneratedKeys() returns the
generated key correctly.  If you run it on 5.0.41 (and probably 5.0.36 as well),
statement.getGeneratedKeys() returns an empty ResultSet, indicating no generated keys. 
The testcase also outputs the value of SELECT LAST_INSERT_ID(), in case that helps.

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

public class GetGeneratedKeysTestcase {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String dbUrl = "jdbc:mysql://localhost/test";
        String dbUser = "root";
        String dbPassword = "";
        Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);

        // create the table first, with a AUTO_INCREMENT PRIMARY KEY
        Statement statement = conn.createStatement();
        statement.execute("CREATE TABLE genkeytest (" +
                "id int(10) PRIMARY KEY AUTO_INCREMENT NOT NULL, " +
                "name char(20) NOT NULL, " +
                "UNIQUE INDEX name (name))");
        statement.close();

        try {
            // issue the INSERT...ON DUPLICATE KEY UPDATE statement twice:
            // the first will insert a new row, the second will get the duplicate key
condition

            for (int i = 0; i < 2; i++) {
                System.out.println("Running insert #"+i);
                statement = conn.createStatement();
                int changed = statement.executeUpdate("INSERT INTO genkeytest (id, name)
" +
                        "VALUES (NULL, 'test1') ON DUPLICATE KEY UPDATE id =
last_insert_id(id)");
                // the following value changed in MySQL v5.0.36, see
http://bugs.mysql.com/bug.php?id=19978 
                System.out.println(" executeUpdate() returned: "+changed);

                // see what statement.getGeneratedKeys() returns
                ResultSet keysResultSet = statement.getGeneratedKeys();
                if (keysResultSet.next()) {
                    // THIS IS THE EXPECTED BEHAVIOR * * * *
                    System.out.println("    statement.getGeneratedKeys.getInt(1)
returned: "+keysResultSet.getInt(1));
                } else {
                    // THIS IS THE INCORRECT BEHAVIOP * * * *
                    System.out.println("    No generated keys returned by
statement.getGeneratedKeys()");
                }
                keysResultSet.close();
                statement.close();

                // see what LAST_INSERT_ID() returned
                statement = conn.createStatement();
                ResultSet lastInsertIdResults = statement.executeQuery("SELECT
LAST_INSERT_ID()");
                if (lastInsertIdResults.next()) {
                    // This is what actually happens in MySQL 5.0.41, and is correct.
                    System.out.println("    SELECT LAST_INSERT_ID() returned:
"+lastInsertIdResults.getInt(1));
                } else {
                    System.out.println("    SELECT LAST_INSERT_ID() didn't return any
results");
                }
                lastInsertIdResults.close();
                statement.close();

                System.out.println();
            }
        } finally {
            // clean up, remove the table.
            statement = conn.createStatement();
            statement.execute("DROP TABLE IF EXISTS genkeytest");
            statement.close();
        }
    }
}

Suggested fix:
Make the Connector/J JDBC driver or MySQL behave like it did before, so
statement.getGeneratedKeys() returns the matched keys for an INSERT...ON DUPLICATE KEY
UPDATE statement when it has a duplicate key condition.

I don't know the implementation details of this fix, as I'm not familiar with the innards
of the Connector/J driver.

Thank you for looking into this, let me know if I can clarify anything.

Sveta,

I'd take a look on the wire to see if MySQL is returning the auto-increment value as part
of the return for the DML statement, as that's what Connector/J uses for
Statement.getGeneratedKeys().

It sounds like there's a disconnect between LAST_INSERT_ID() and what's returned directly
by the server to the client for the DML (you could test this with a C testcase and
mysql_insert_id(), or you could just look at the update reply packet with Wireshark).

Thank you for the report.

According to JDBC manual
(http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#getGeneratedKeys()) :
"public ResultSet getGeneratedKeys() throws SQLException    Retrieves any auto-generated
keys created as a result of executing this Statement object. If this Statement object did
not generate any keys, an empty ResultSet object is returned"

But when I run presented test case in mysql command line client, I get information for
last statement about data has not been changed:

$mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 268
Server version: 5.0.41 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE genkeytest (id int(10) PRIMARY KEY AUTO_INCREMENT NOT NULL, name
char(20) NOT NULL, UNIQUE INDEX name (name));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO genkeytest (id, name) VALUES (NULL, 'test1') ON DUPLICATE KEY UPDATE
id = last_insert_id(id);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 | 
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO genkeytest (id, name) VALUES (NULL, 'test1') ON DUPLICATE KEY UPDATE
id = last_insert_id(id);
Query OK, 0 rows affected (0.00 sec)

Why do you think getGeneratedKeys should return not empty ResultSet for the last query?

Sveta,

The issue is that whatever LAST_INSERT_ID() returns, the _same_ value should be returned
by the server to the client as part of the DML statement.

Also, JDBC (and ODBC), set flags for the server to return _matched_ rows, not affected
rows. The fact that MySQL decided not to physically update rows is not relevent as far as
I can tell, there was a row _matched_ to be updated.

test case

Attachment: bug28505_3.java (text/plain), 2.61 KiB.

Thank you for the report.

Verified as described.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28239

ChangeSet@1.2517, 2007-06-06 23:46:51+04:00, evgen@moonbone.local +2 -0
  Bug#28505: mysql_affected_rows() may return wrong result if CLIENT_FOUND_ROWS
  flag is set.
  
  When the CLIENT_FOUND_ROWS flag is set then the server should return
  found number of rows independently whether they were updated or not.
  But this wasn't the case for the INSERT statement which always returned
  number of rows that were actually changed thus providing wrong info to
  a user.
  
  Now the select_insert::send_eof method and the mysql_insert function
  are sending the number of touched rows if the CLIENT_FOUND_ROWS flag is set.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28241

ChangeSet@1.2517, 2007-06-07 00:30:00+04:00, evgen@moonbone.local +2 -0
  Bug#28505: mysql_affected_rows() may return wrong result if CLIENT_FOUND_ROWS
  flag is set.
  
  When the CLIENT_FOUND_ROWS flag is set then the server should return
  found number of rows independently whether they were updated or not.
  But this wasn't the case for the INSERT statement which always returned
  number of rows that were actually changed thus providing wrong info to
  the user.
  
  Now the select_insert::send_eof method and the mysql_insert function
  are sending the number of touched rows if the CLIENT_FOUND_ROWS flag is set.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.