Analysis of DBLE Prepared Statement Protocol and Bug Fixes #1122 and #1124

DBLE is an enterprise‑grade open‑source distributed middleware, often nicknamed “MyCat Plus”. Starting with MySQL 5.1, the server supports a binary prepared‑statement protocol that reduces parsing overhead and helps prevent SQL‑injection attacks.

Two bugs were reported by DBLE users on 12 April 2019: #1122 – an “unknown pStmtId when executing” error when useServerPrepStmts=true ; and #1124 – incorrect results when using JDBC with useCursorFetch=true . Both bugs involve the prepared‑statement protocol.

The article first reviews the MySQL prepared‑statement protocol, describing the COM_STMT_PREPARE, COM_STMT_EXECUTE, COM_STMT_FETCH, and COM_STMT_RESET commands and their required ordering.

DBLE’s implementation caches the SQL on the client side, pretends to return an OK packet for COM_STMT_PREPARE, translates COM_STMT_EXECUTE into a COM_QUERY sent to backend nodes, and does not support COM_STMT_FETCH. This design leads to the observed bugs.

#1122 Bug Analysis : In the same connection, DBLE performed COM_STMT_CLOSE and a new COM_STMT_PREPARE asynchronously, causing a thread‑safety issue. The fix synchronises these two operations to avoid the race condition.

#1124 Bug Analysis : JDBC enables server‑side cursors only when several conditions are met (SELECT statement, fetchSize>0, useCursorFetch=true, forward‑only result set, read‑only concurrency, MySQL 5.0.5+). The bug stemmed from DBLE not satisfying these requirements, leading to wrong results.

Below is a Java example that correctly enables cursor‑fetch mode:

public static void testPrepareStmt() {
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(
            "jdbc:mysql://127.0.0.1:8066/poc?useCursorFetch=true", "root", "123456");
        stmt = conn.prepareStatement(
            "select long_col_1, long_col_2 from problemTable where to_days(create_time) <= to_days(now()) and id = ?");
        stmt.setFetchSize(10);
        stmt.setInt(1, 2);
        ResultSet rs = stmt.executeQuery();
        int count = 0;
        while (rs.next()) {
            ++count;
            System.out.println("########### row " + count + " ###################");
            System.out.println("long_col_1 : " + rs.getString(1));
            System.out.println("long_col_2 : " + rs.getString(2));
            System.out.println();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException ce) {
        ce.printStackTrace();
    } finally {
        try { if (stmt != null) stmt.close(); } catch (Exception ignored) {}
        try { if (conn != null) conn.close(); } catch (Exception ignored) {}
    }
}

After locating the root causes, the DBLE community released version 2.19.03.0 within five days, fixing both bugs and improving the prepared‑statement handling.