MySQL Bugs: #116318: useServerPrepStmts useLocalTransactionState cause first execute rollback failure

Bug #116318	useServerPrepStmts useLocalTransactionState cause first execute rollback failure
Submitted:	9 Oct 2024 3:34	Modified:	19 Dec 2024 1:38
Reporter:	kang wang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	9.0.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have seen the Bug #109013,and use Connector/J 9.0.0
but when i first start my project execute method，the transcation rollback fail，and after all execute rollback success
I have set useServerPrepStmts and useLocalTransactionState to true in my config

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

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

If I set any of useServerPrepStmts and useLocalTransactionState to true, I can successfully roll back the first time the project is started.

Hello kang wang,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
Hello kang wang,

Could you clarify what is the issue you are facing? I interpreted your report as "When setting useServerPrepStmts and useLocalTransactionState to true, the first rollback fails, but subsequent rollbacks are successful"
I ran the testcase you provided but couldn't find any bug so I wanted to know if there's something I'm missing or if I misunderstood your report.
Thank you for your report

Regards,
Axyoan

Sorry for the inconvenience, I’ve updated my code. The issue I’m currently facing is that in version 9.0.0, all inserts into cfg_user are successful, but when inserting into cfg_telephone, the operation correctly throws an exception. This indicates that the transaction is not being applied effectively.

sql：
CREATE TABLE `cfg_user` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    `age` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create telephone table with unique constraint on phone number
CREATE TABLE `cfg_telephone` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `phone` varchar(20) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

code：
public class MySQLTransactionTest {

    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&useServerPrepStmts=true&useLocalTransactionState=true";
    private static final String USERNAME = "your_username";
    private static final String PASSWORD = "your_password";

    @Test
    void testTransactionRollback() {
        try {
            for (int i = 0; i < 5; i++) {
                final Connection conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
                conn.setAutoCommit(false);
                doInsertOneByOne(conn);
                conn.setAutoCommit(true); // After this point, the data is actually committed
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    void testTransactionRollbackBatch() {
        try {
            for (int i = 0; i < 5; i++) {
                final Connection conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
                conn.setAutoCommit(false);
                doInsertBatch(conn);
                conn.setAutoCommit(true);
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void doInsertBatch(Connection conn) throws SQLException {
        try (PreparedStatement st = conn.prepareStatement("insert into cfg_user(name, age) values(?, ?)")) {
            // First insert
            st.setString(1, "cfg");
            st.setInt(2, 18);
            st.addBatch();

            // Second insert (insert phone to trigger error)
            PreparedStatement st2 = conn.prepareStatement("insert into cfg_telephone(phone) values(?)");
            st2.setString(1, "17612176673");
            st2.executeUpdate();

            st.executeBatch();
            conn.commit(); // This line will not be reached
        } catch (Exception e) {
            e.printStackTrace();
            conn.rollback(); // This rollback will actually fail
        }
    }

    private void doInsertOneByOne(Connection conn) throws SQLException {
        try {
            // Insert user
            PreparedStatement st1 = conn.prepareStatement("insert into cfg_user(name, age) values(?, ?)");
            st1.setString(1, "cfg");
            st1.setInt(2, 18);
            st1.executeUpdate();

            // Insert phone (trigger error)
            PreparedStatement st2 = conn.prepareStatement("insert into cfg_telephone(phone) values(?)");
            st2.setString(1, "17612176673");
            st2.executeUpdate();

            conn.commit(); // This line will not be reached
        } catch (Exception e) {
            e.printStackTrace();
            conn.rollback(); // This rollback will actually fail
        }
    }
}

Posted by developer:
 
Hello kang wang,

Thank you for the information, I can confirm I see the behavior you are describing. This is definitely a bug. Once again, thank you for your report

Regards,
Axyoan

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

"When the connection properties useServerPrepStmts and useLocalTransactionState were both true and a PreparedStatement was used, a statement rollback would fail. This was because the PreparedStatement always reset the local transaction state. With this fix, Connector/J checks and makes sure that the transaction state is only reset when it should be."