MySQL Bugs: #114653: Handler queries is marked as not producing responses

Bug #114653	Handler queries is marked as not producing responses
Submitted:	16 Apr 2024 11:07	Modified:	10 Aug 2024 13:20
Reporter:	kalyanasundaram somasundaram	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.3.0	OS:	Linux
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Mysql connector/j from 8.0.26 checks if a particular SQLString produces result before being executed via executeQuery. Handler command can also produce result if we use HANDLER READ. But handler is defaulted to "DOES_NOT_PRODUCE_RESULT_SET" https://github.com/mysql/mysql-connector-j/blob/release/8.x/src/main/core-api/java/com/mys.... This prevents Handler READ to be called via executeQuery. It returns an error here https://github.com/mysql/mysql-connector-j/blob/805f872a57875f311cb82487efcfb070411a3fa0/s... when we call the HANDLER READ query by wrongly assuming it doesn't return result. More details about handler query is available here https://dev.mysql.com/doc/refman/8.0/en/handler.html

How to repeat:
 _rs = _stmt.executeQuery(new SqlStringBuilder().append("HANDLER ")
          .appendIdent(TABLE_NAME)
          .append(" READ `PRIMARY` FIRST LIMIT ").append(_bufSize).toString());

java.sql.SQLException: Statement.executeQuery() cannot issue statements that do not produce result sets.
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130) ~[mysql-connector-j-8.3.0.jar:8.3.0]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98) ~[mysql-connector-j-8.3.0.jar:8.3.0]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90) ~[mysql-connector-j-8.3.0.jar:8.3.0]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64) ~[mysql-connector-j-8.3.0.jar:8.3.0]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:924) ~[mysql-connector-j-8.3.0.jar:8.3.0]

Suggested fix:
Add QueryReturnType.MAY_PRODUCE_RESULT_SET for handler queries

else if (firstStatementChar == 'H' && StringUtils.startsWithIgnoreCaseAndWs(sql, "HANDLER", statementKeywordPos)) {
            return QueryReturnType.MAY_PRODUCE_RESULT_SET;
}

Hello kalyanasundaram,

Thank you for the bug report.
Could you please provide repeatable test case (sample project etc. - please make it as private if you prefer) to reproduce this issue at our end? Thank you.

Regards,
Ashwini Patil

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".