MySQL Bugs: #109864: Connector/J 8.0.32 hangs on MySQL 5.5 with prepared statements

Bug #109864	Connector/J 8.0.32 hangs on MySQL 5.5 with prepared statements
Submitted:	31 Jan 2023 17:23	Modified:	27 Mar 2023 8:21
Reporter:	Björn Voigt (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
The bugfix #106252 (included in MySQL Connector/J 8.0.32) describes itself as follows: "When working with MySQL Server 5.7.5 or earlier, if server-side prepared statements were used, Connector/J sometimes hung after the prepare phase for a statement. It was caused by Connector/J mistaking a metadata packet as an EOF packet and discarding it by mistake. With this fix, the mistake is avoided by improving the procedure for handling EOF packets. Thanks to Zhe Huang for contributing to the fix."

Unfortunately this bugfix breaks MySQL 5.5. MySQL 5.5 is unsupported. Anyway, some installations of MySQL 5.5 are still in production use.

How to repeat:
The following example hangs in conn.prepareStatement() if all three conditions are met:

1) MySQL server is 5.5 (tested with MySQL 5.5.62)
2) MySQL Connector/J is 8.0.32
3) useServerPrepStmts=true as connection property

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectorJBug8032 {

    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        PreparedStatement pStmt = null;

        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/test?"
                    + "useServerPrepStmts=true&user=root&password=...");

            stmt = conn.createStatement();

            stmt.executeUpdate("DROP TABLE IF EXISTS bug8032");
            stmt.executeUpdate(
                    "CREATE TABLE bug8032 ("
                    + "priKey INT NOT NULL AUTO_INCREMENT, "
                    + "dataField INT, PRIMARY KEY (priKey))");

            pStmt = conn.prepareStatement("INSERT INTO bug8032 (dataField) VALUES(?)");
            pStmt.setInt(1, 100);
            pStmt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) {
                    // ignore
                }
                stmt = null;
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (SQLException sqlEx) {
                    // ignore
                }

                pStmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    // ignore
                }
                conn = null;
            }
        }
    }
}

Suggested fix:
With "git bisect" I found that the break is caused by the following commit.

This patch needs further testing on MySQL 5.5.

commit bdfea2ac5bc76e820a4c486133565a04ba402920
Author: Filipe Silva <filipe.silva@oracle.com>
Date:   Wed Oct 26 17:33:02 2022 +0100

    Fix for Bug#106252 (Bug#33968169), Connector/J client hangs after prepare & execute process with old version server.
    
    Change-Id: I7e364fe32a21baf329ed1b92e2e66e5e43f464ea

Hi Björn Voigt, you can go back to Bug #106252 to track the new update.

Hello Björn,

Thank you for the report and test case.

regards,
Umesh

Related to Bug#107577.

Confirming we encountered this issue on MySQL 5.7.32, using driver com.mysql:mysql-connector-j:8.0.32, preparing a "DELETE FROM ...." statement. Debugging indicates it is the "if (checkEOF && isEOFPacket())" line that results in a hung connection. 

I would advocate for raising the Severity on this from S3 (non-critical) to something higher. A statement that returns zero columns (DELETE, INSERT, ALTER, CREATE, ...) is a common action and this would be a showstopping bug on many production systems.

Skylar Sutton suggested to increase severity in https://bugs.mysql.com/bug.php?id=109864#c530709

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

"When connecting to MySQL Server 5.7 and earlier with Connector/J 8.0.32, Connector/J sometimes hung after the prepare phase of a server-side prepared statement."

It would be nice to have the fix for this bug as open source, so we can test it. https://github.com/mysql/mysql-connector-j currently does not show the fix.