MySQL Bugs: #75209: Set useLocalTransactionState may result in partially committed transaction

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

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.

Hello Calvin,

Thank you for the report and test case.

Thanks,
Umesh

// 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)

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.'

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.

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?

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
            }
        }

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.

That's right, this was fixed in Connector/J 5.1.40.

Thanks for your interest in Connector/J and MySQL.