Bug #106465 Query timeout is ignored when "useCursorFetch" enabled
Submitted: 15 Feb 2022 15:36 Modified: 16 Feb 2022 5:24
Reporter: José Fidalgo Email Updates:
Status: Can't repeat Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.49 OS:Any
Assigned to: CPU Architecture:Any

[15 Feb 2022 15:36] José Fidalgo
On a JDBC connection with "useCursorFetch" enabled, the timeout set using `Statement.setQueryTimeout(int)` is ignored.

Only tested with connector/J 5.1.49, but likely happens with other connectors and versions.

How to repeat:
Create a statement, set a query timeout, then execute a query that takes longer to run than that timeout.

Statement s = conn.createStatement();
s.executeQuery("SELECT SLEEP(3)");

When this is executed using a connection with the default "useCursorFetch" disabled, the query times out according to the set query timeout.
When this is executed using a connection with "useCursorFetch" enabled, the set query timeout is ignored.

Suggested fix:
Checking the code of StatementImpl I see that the `timeoutInMillis` is only used if `useServerFetch()` is false.
I believe a CancelTask should be used regardless of whether server fetch is being used or not.

To complement this, maybe the driver could parse the query and add "MAX_EXECUTION_TIME" for that query.
[16 Feb 2022 5:24] MySQL Verification Team
Hello José Fidalgo,

Thank you for the report and feedback.
Per https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/

Support EOL for MySQL Connector/J 5.1

Per Oracle's Lifetime Support policy, as of Feb 9th, 2021, MySQL Connector/J 5.1 series is covered under Oracle Sustaining Support. Users are encouraged to upgrade to MySQL Connector/J 8.0 series.

I'm afraid since 5.1 is EOL you may have to upgrade to C/J 8.0 in order to avoid this issue and this report may well be closed as won't fix.

Moreover, this issue seems to be fixed in after Bug #103796, please see Bug #103796

 [24 Sep 2021 21:24] Daniel So

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

"setQueryTimeout() failed to set a timeout for queries when a cursor was used for fetching rows. Thanks to Hong Wang for contributing the fix. "