MySQL Bugs: #109013: useServerPrepStmts and useLocalTransactionState could cause rollback failure

Bug #109013	useServerPrepStmts and useLocalTransactionState could cause rollback failure
Submitted:	6 Nov 2022 19:57	Modified:	23 Mar 2023 16:12
Reporter:	Ravi Sanwal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.29, 8.0.31	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using both useServerPrepStmtsv and useLocalTransactionState together on a connection causes rollback to fail.

Here is a use-case that readily fails.
- start a transaction (connection has auto-commit=false).
- insert a row successfully in a table.
- insert another row with the same key (should fail)
- rollback the transaction(conn.rollback())

Observe that the first row stays in the table.

How to repeat:
// Here is a sample program that would demonstrate the problem.
// Prep:
// CREATE TABLE `xyz` (`id` int NOT NULL, PRIMARY KEY(`id`)) ENGINE=InnoDB;

import java.sql.*;

/**
 * Created by ravis on 11/3/22
 *
 * CREATE TABLE `xyz` (`id` int NOT NULL, PRIMARY KEY(`id`)) ENGINE=InnoDB;
 */
public class MySQLBug {

    public static void main(final String[] args) {

        try {

            for (int i = 0; i < 5; i++) {
                final Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/somedb?useSSL=false&useServerPrepStmts=true&useLocalTransactionState=true", "user","passwd");
                conn.setAutoCommit(false);
                //doItBatch(conn); // one way 
                doItOneByOne(conn); // another way
                conn.setAutoCommit(true); // past this, the actual bad data is committed
                conn.close();
            }
        } catch (final Exception e) {
            e.printStackTrace();
        }
    }

    private static void doItBatch(final Connection conn) throws SQLException {

        try (final PreparedStatement st = conn.prepareStatement("insert into xyz values(?)")) {
            st.setInt(1, 1);
            st.addBatch();
            st.setInt(1, 1);
            st.addBatch();

            st.executeBatch();
            conn.commit(); // unreachable
        } catch (final Exception e) {
            e.printStackTrace();
            conn.rollback(); // this functionally fails
        }
    }

    private static void doItOneByOne(final Connection conn) throws SQLException {

        try (final PreparedStatement st = conn.prepareStatement("insert into xyz values(?)")) {
            st.clearParameters();
            st.setInt(1, 1);
            st.executeUpdate();

            st.clearParameters();
            st.setInt(1, 1);
            st.executeUpdate();

            conn.commit(); // unreachable
        } catch (final Exception e) {
            e.printStackTrace();
            conn.rollback(); // this functionally fails
        }
    }
}

Suggested fix:
No idea.

Before running the sample program:

mysql> select * from xyz;
Empty set (0.00 sec)

Observation after the running the sample program

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

Correction for previous comment.

After running the program:

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

Hello Ravi,

Thank you for the report and test case.

regards,
Umesh

Edit: use-case (adds `set auto-commit on the connection to true`

Here is a use-case that readily fails.
- start a transaction (connection has auto-commit=false).
- insert a row successfully in a table.
- insert another row with the same key (should fail)
- rollback the transaction(conn.rollback())
- set the connection to auto-commit=true (conn.setAutoCommit(true)

May be a duplicate of Bug#108643.

Posted by developer:
 
Added the following entry to the Connector/J 8.0.33 changelog: 

"When the connection property useLocalTransactionState was set to true, after a number of insert statements were executed, the transaction could not be rolled back. It was because Connector/J lost check of the transaction statuses of the statements in the case, and this patch corrects the problem."

Posted by developer:
 
Updated the changelog entry to the following: 

"When the connection property useLocalTransactionState was set to true, after a number of insert statements were executed and the last one hit a unique constraint check, the transaction could not be rolled back. It was because Connector/J lost track of the transaction statuses of the statements in the case, and this patch corrects the problem."