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: Bjö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

[31 Jan 2023 17:23] Björn Voigt
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
[1 Feb 2023 3:28] Zhe Huang
Hi Björn Voigt, you can go back to Bug #106252 to track the new update.
[1 Feb 2023 4:45] MySQL Verification Team
Hello Björn,

Thank you for the report and test case.

regards,
Umesh
[13 Feb 2023 17:04] Filipe Silva
Related to Bug#107577.
[14 Mar 2023 19:18] Skylar Sutton
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.
[14 Mar 2023 20:23] Björn Voigt
Skylar Sutton suggested to increase severity in https://bugs.mysql.com/bug.php?id=109864#c530709
[21 Mar 2023 15:19] Daniel So
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."
[27 Mar 2023 8:21] Björn Voigt
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.