Bug #91351 MysqlConnectionPoolDataSource - autocommit status lost if global autocommit = 0

• Submitted: 21 Jun 2018 11:23
• Modified: 13 Sep 2023 21:38
• Reporter: Ceyhan Kasap
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• Version: mysql-connector-java-8.0.11
• OS: Any
• CPU Architecture: Any

[21 Jun 2018 11:23] Ceyhan Kasap

Description:
When global autocommit is 0 in server, autocommit status of connections acquired from MysqlConnectionPoolDataSource ends up being lost because of executing `change user` on the server.

This problem is asked and verified by Filipe Silva:

https://forums.mysql.com/read.php?39,666702,666986#msg-666986

Currently as workaround,  we set `paranoid` flag which prevents executing `change user` so the connection/session keeps all previous state autocommit status is preserved.

How to repeat:
Set  global autocommit to 0 on server and execute below code.

Insert results are NOT persisted

MysqlConnectionPoolDataSource ds1 = new MysqlConnectionPoolDataSource();
ds1.setUser("usr");
ds1.setPassword("pwd");
ds1.setServerName("server");
ds1.setPort(port);
ds1.setDatabaseName("dbname");
ds1.setUseSSL(false);
ds1.setAllowPublicKeyRetrieval(true);

Connection conn = ds1.getPooledConnection("usr", "pwd").getConnection();

logger.info("connection " + conn.toString());

PreparedStatement ps = null;
ResultSet rs = null;

try {

    String query = "INSERT INTO ...";
    ps = conn.prepareStatement(query);

    int timeout = 10;
    ps.setQueryTimeout(timeout);

    logger.info("timeout: " + timeout);
    logger.info("Starting query execution for query: " + query);
    long qeStart = System.currentTimeMillis();

    ps.setString(1, "...");

    ps.executeUpdate();

    long qeEnd = System.currentTimeMillis();
    logger.info("Query execution completed in " + (qeEnd - qeStart) + "msec.");
} catch (Exception e) {
    logger.error("ERROR OCCURED", e);
    System.err.println("ERROR OCCURED");
    e.printStackTrace();

} finally {
    closeResultSet(rs);
    closeStatement(ps);
    closeConnection(conn);
}

[22 Jun 2018 8:24] Chiranjeevi Battula

Hello Ceyhan Kasap,

Thank you for the bug report and testcase.
Verified this behavior on MySQL Connector / J 8.0.11.

Thanks,
Chiranjeevi.

[13 Sep 2023 21:38] Daniel So

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

"Obtaining a connection from a MysqlConnectionPoolDataSource made Connector/J reset its connection state unless the connection property paranoid was set to be true. During the reset, the autocommit mode of the session was restored to the default value specified on the server by the system variable autocommit, while the JDBC specification mandates that autocommit be always enabled for a freshly created connection. With this patch, the connection reset will always enable autocommit in the situation."