Bug #103229 Timeout during statement fetch causes silent loss of trailing rows
Submitted: 6 Apr 21:39 Modified: 22 Apr 12:32
Reporter: Pierre le Riche Email Updates:
Status: Need Feedback Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:8.0.23 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: data loss, ODBC, SQLFetch, timeout

[6 Apr 21:39] Pierre le Riche
Description:
When a timeout is set on the maximum execution time of a statement (via SQL_ATTR_QUERY_TIMEOUT) and the application takes longer than this timeout to fetch all the rows then SQLFetch will return SQL_NO_DATA when the timeout is hit instead of an error. This causes the remaining rows to be dropped silently, causing data loss.

How to repeat:
1) Call SQLSetStmtAttrW on a statement handle and set SQL_ATTR_QUERY_TIMEOUT to a low value, e.g. 1
2) Open a select query on the statement that will return many records. Use a forward-only cursor.
3) Call SQLFetch in order to fetch a row
4) Insert a delay in the code longer than the time specified in step 1 so that the statement times out
5) Call SQLFetch a second time in order to attempt to fetch another row
Expected: SQLFetch returns SQL_ERROR
Actual: SQLFetch returns SQL_NO_DATA

Suggested fix:
If a statement has timed out then SQLFetch should return SQL_ERROR and not SQL_NO_DATA. The latter is not an error condition, causing the application to wrongly assume that all the data has been fetched.
[7 Apr 12:07] MySQL Verification Team
Hello Pierre le Riche,

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 confirm this issue at our end?

Regards,
Ashwini Patil
[7 Apr 20:50] Pierre le Riche
Hi Ashwini,

Thank you for looking into this. I develop applications in Delphi (Pascal). Let me know if a test case written in Delphi will help you.

Some additional information: In the connection string you have to enable option NO_CACHE ($100000) so that the entire result set is not fetched when the statement is opened. The result set also has to be fairly large so that the internal driver buffer is not large enough to fetch the entire result set during the first call to SQLFetch.

Best regards,
Pierre
[22 Apr 12:32] MySQL Verification Team
Hello Pierre le Riche,

Thank you for the feedback.
Please provide complete repeatable test case along with exact steps. Thanks.

Regards,
Ashwini Patil