Description:
In 8.0.30, calling PreparedStatement.executeUpdate() on a query that is only a comment throws a SQLException that says:
Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets.
This was not the case in 5.1.25 where it ran without throwing an exception and simply returned 0.
(However, note that a query that is only whitespace will throw a SQLException in both cases, but the error message is different. I've included a whitespace query to show the difference.)
This is the output of the program below with the two different versions:
MySQL Connector Java version mysql-connector-java-5.1.25 ( Revision: ${bzr.revision-id} )
Exception: Query was empty Query:
Result: 0 Query: #comment
Result: 0 Query: -- comment
Result: 0 Query: /* comment */
Result: 0 Query: /* com
ment */
MySQL Connector/J version mysql-connector-java-8.0.30 (Revision: 1de2fe873fe26189564c030a343885011412976a)
Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query:
Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query: #comment
Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query: -- comment
Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query: /* comment */
Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query: /* com
ment */
openjdk 11.0.2 2019-01-15
OpenJDK Runtime Environment 18.9 (build 11.0.2+9)
OpenJDK 64-Bit Server VM 18.9 (build 11.0.2+9, mixed mode)
How to repeat:
import java.sql.*;
class ExecuteUpdateBug {
private static void executeUpdate(final Connection conn, final String sql) {
try (final Statement st = conn.createStatement()) {
int result = st.executeUpdate(sql);
System.out.println("Result: " + result + "\tQuery: " + sql);
} catch (SQLException e) {
System.out.println("Exception: " + e.getMessage() + "\tQuery: " + sql);
}
}
public static void main(final String[] args) throws Exception {
Class.forName(args[1]); // This value is "com.mysql.jdbc.Driver" for 5.1.25 and "com.mysql.cj.jdbc.Driver" for 8.0.30
try (final Connection conn = DriverManager.getConnection(args[0])) {
final DatabaseMetaData dmd = conn.getMetaData();
System.out.println(dmd.getDriverName() + " version " + dmd.getDriverVersion());
executeUpdate(conn, " ");
executeUpdate(conn, "#comment");
executeUpdate(conn, "-- comment");
executeUpdate(conn, "/* comment */");
executeUpdate(conn, "/* com\nment */");
}
}
}
Suggested fix:
The error message would seem to indicate that the queries in question produce result sets. But I would think that a query that's no more than a comment should produce no result set whatsoever.
In 8.0.30, when a ClientPreparedStatement is created, a QueryInfo object is instantiated. In the QueryInfo constructor, we call into the getQueryReturnType method which checks for the index of a statement keyword in the query string. Since this finds no such statement keyword in a query that's only a comment, it apparently sets the queryReturnType to QueryReturnType.NONE.
When executeUpdate is eventually called on the PreparedStatement, it calls a method to see whether this is a non-result set producing query. That logic returns true if the queryReturnType is QueryReturnType.DOES_NOT_PRODUCE_RESULT_SET or queryReturnType == QueryReturnType.MAY_PRODUCE_RESULT_SET.
I would imagine that a possible fix would be to include QueryReturnType.NONE in the set of queryReturnTypes that qualify as non-result set producing queries. That could be the suggested fix. However, I don't know all of the ramifications of such a change and thus leave it up to your discretion. Either way, the end result is that a query that's only a comment no longer throw this SQLException and instead behave similarly to the way it did in 5.1.25.
Description: In 8.0.30, calling PreparedStatement.executeUpdate() on a query that is only a comment throws a SQLException that says: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. This was not the case in 5.1.25 where it ran without throwing an exception and simply returned 0. (However, note that a query that is only whitespace will throw a SQLException in both cases, but the error message is different. I've included a whitespace query to show the difference.) This is the output of the program below with the two different versions: MySQL Connector Java version mysql-connector-java-5.1.25 ( Revision: ${bzr.revision-id} ) Exception: Query was empty Query: Result: 0 Query: #comment Result: 0 Query: -- comment Result: 0 Query: /* comment */ Result: 0 Query: /* com ment */ MySQL Connector/J version mysql-connector-java-8.0.30 (Revision: 1de2fe873fe26189564c030a343885011412976a) Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query: Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query: #comment Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query: -- comment Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query: /* comment */ Exception: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets. Query: /* com ment */ openjdk 11.0.2 2019-01-15 OpenJDK Runtime Environment 18.9 (build 11.0.2+9) OpenJDK 64-Bit Server VM 18.9 (build 11.0.2+9, mixed mode) How to repeat: import java.sql.*; class ExecuteUpdateBug { private static void executeUpdate(final Connection conn, final String sql) { try (final Statement st = conn.createStatement()) { int result = st.executeUpdate(sql); System.out.println("Result: " + result + "\tQuery: " + sql); } catch (SQLException e) { System.out.println("Exception: " + e.getMessage() + "\tQuery: " + sql); } } public static void main(final String[] args) throws Exception { Class.forName(args[1]); // This value is "com.mysql.jdbc.Driver" for 5.1.25 and "com.mysql.cj.jdbc.Driver" for 8.0.30 try (final Connection conn = DriverManager.getConnection(args[0])) { final DatabaseMetaData dmd = conn.getMetaData(); System.out.println(dmd.getDriverName() + " version " + dmd.getDriverVersion()); executeUpdate(conn, " "); executeUpdate(conn, "#comment"); executeUpdate(conn, "-- comment"); executeUpdate(conn, "/* comment */"); executeUpdate(conn, "/* com\nment */"); } } } Suggested fix: The error message would seem to indicate that the queries in question produce result sets. But I would think that a query that's no more than a comment should produce no result set whatsoever. In 8.0.30, when a ClientPreparedStatement is created, a QueryInfo object is instantiated. In the QueryInfo constructor, we call into the getQueryReturnType method which checks for the index of a statement keyword in the query string. Since this finds no such statement keyword in a query that's only a comment, it apparently sets the queryReturnType to QueryReturnType.NONE. When executeUpdate is eventually called on the PreparedStatement, it calls a method to see whether this is a non-result set producing query. That logic returns true if the queryReturnType is QueryReturnType.DOES_NOT_PRODUCE_RESULT_SET or queryReturnType == QueryReturnType.MAY_PRODUCE_RESULT_SET. I would imagine that a possible fix would be to include QueryReturnType.NONE in the set of queryReturnTypes that qualify as non-result set producing queries. That could be the suggested fix. However, I don't know all of the ramifications of such a change and thus leave it up to your discretion. Either way, the end result is that a query that's only a comment no longer throw this SQLException and instead behave similarly to the way it did in 5.1.25.