Bug #75209 Set useLocalTransactionState may result in partially committed transaction
Submitted: 15 Dec 2014 5:57 Modified: 15 Sep 2016 22:38
Reporter: Calvin Sun Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.1.34 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[15 Dec 2014 5:57] Calvin Sun
Description:
When setting useLocalTransactionState=true in MySQL Connector/J, rollback is not sent to the server after an error. Here is our scenario:

* useLocalTransactionState is set to true
* autoCommit is set to false
* a transaction consisting of a few DML statements - first few DML statements successfully executed
* next DML statement fails, e.g. due to a duplicate key 
* rollback is called on the JDBC connection, but rollback is not sent to the server

rollback is sent to server if useLocalTransactionState is set to false.

How to repeat:
First create two tables as following:

CREATE TABLE `abc` (`id` bigint(20) DEFAULT NULL) ENGINE=InnoDB;
CREATE TABLE `zyx` (`id` bigint(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
INSERT INTO abc VALUES(1);
INSERT INTO zyx VALUES(1);

The Java code looks like:

    try {
      Class.forName("com.mysql.jdbc.Driver");
    
      conn = DriverManager.getConnection(url+"?useLocalTransactionState=true", username, password);
      conn.setAutoCommit(false);

      st = conn.createStatement();

      try {
        st.executeUpdate("UPDATE abc SET id = id + 1");
        // This triggers Duplicate-key exception
        st.executeUpdate("INSERT INTO zyx VALUES(1)");
	    conn.commit();
      } catch (Exception e) {
	      conn.rollback();
	  }
	  
      st.close();
      conn.setAutoCommit(true);
      conn.setAutoCommit(false);
      conn.close();
    } catch (Exception e) {
      e.printStackTrace();
    }

In this example, UPDATE should NOT be committed. But rollback() was not sent to the server. The partial transaction is committed when setting autocommit back to true - just to demonstrate the issue.

Suggested fix:
Get the correct transaction state on the server when SQL error is received.
[15 Dec 2014 9:26] MySQL Verification Team
Hello Calvin,

Thank you for the report and test case.

Thanks,
Umesh
[15 Dec 2014 9:28] MySQL Verification Team
// MySQL CLI session 

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `abc` (`id` bigint(20) DEFAULT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `zyx` (`id` bigint(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO abc VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO zyx VALUES(1);
Query OK, 1 row affected (0.00 sec)

//

[ushastry@ushastry]~/bugs/mysql-5.6.21:  javac Bug75209.java
[ushastry@ushastry]~/bugs/mysql-5.6.21: java -classpath '.:/home/ushastry/bugs/mysql-connector-java-5.1.34/mysql-connector-java-5.1.34-bin.jar' Bug75209

// MySQL CLI session

mysql> select * from abc;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select * from zyx;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
[15 Sep 2016 22:38] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 5.1.40 changelog: 

'When the connection property useLocalTransactionState was set to “true” and autocommit was set to “false” on the server, if any exception was thrown, any further calls for rollback() or commit() were not sent to the server. It was because when there was an exception while executing a query, Connector/J lost the information regarding the server's transaction state. This patch fixes this issue by preserving the previous transaction state for the current connection when any exception is thrown.'
[14 Oct 2016 16:10] Daniel So
The fix has been included in Connector/J 6.0.5. The entry for the 5.1.40
changelog has been included into the 6.0.5 changelog.
[2 Jan 2017 19:09] Gray Watson
What's the status of this bug?  It has been closed but I can't tell if it is in 5.1.40 or will be in 5.1.41?
[2 Jan 2017 19:15] Gray Watson
It looks to me that this bug still exists with 5.1.40.  The following still returns without sending the rollback SQL.

        if (getUseLocalTransactionState() && versionMeetsMinimum(5, 0, 0)) {
            if (!this.io.inTransactionOnServer()) {
                return; // effectively a no-op
            }
        }
[27 Feb 2017 14:36] Florian Rampp
It looks like this is solved in 5.1.40: See the third-last bullet point here: https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-40.html

The method rollbackNoChecks did not change but other things did which made the JDBC connector aware that there is a server transaction.
[27 Feb 2017 19:11] Filipe Silva
That's right, this was fixed in Connector/J 5.1.40.

Thanks for your interest in Connector/J and MySQL.