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

[6 Nov 2022 19:57] Ravi Sanwal
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.
[6 Nov 2022 20:00] Ravi Sanwal
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)
[6 Nov 2022 20:01] Ravi Sanwal
Correction for previous comment.

After running the program:

mysql> select * from xyz;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
[7 Nov 2022 9:37] MySQL Verification Team
Hello Ravi,

Thank you for the report and test case.

regards,
Umesh
[7 Nov 2022 18:13] Ravi Sanwal
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)
[10 Jan 0:06] Filipe Silva
May be a duplicate of Bug#108643.