Bug #89542 ODBC Parameter NO_CACHE Lead To Function Call Error Not Return
Submitted: 6 Feb 2018 2:38 Modified: 4 Jun 2021 20:57
Reporter: Sun Edward Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:8.0.24 OS:Windows (WIndows 7 Enterprise Service Pack 1)
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[6 Feb 2018 2:38] Sun Edward
Description:
If set NO_CACHE=1 parameter in ODBC connection string, the function call error in the select list will not return. 

How to repeat:
there is function definition like:

DELIMITER |
DROP FUNCTION IF EXISTS quest_stage.avglinda;
CREATE FUNCTION quest_stage.`avglinda`(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED, num3 SMALLINT UNSIGNED) RETURNS float(3,2)
BEGIN
RETURN (num1+num2+num3)/3;
END|
DELIMITER ;

function avglinda have three parameters.

And there is a query like:
SELECT `REGION_ID`    AS `COL_1`, `REGION_NAME`  AS `COL_2`, avglinda(1, 2) AS `COL_3`  FROM `quest_stage`.`region`

the function call of avglinda only provide two parameter.
Normally it will return error as:
# ERR: Incorrect number of arguments for FUNCTION quest_stage.avglinda; expected
 3, got 2 (MySQL error code: 1318, SQLState: 42000 )

But with the following connection string, it will return SQL_SUCCESS with empty result.

DRIVER={MySQL ODBC 5.3 ANSI Driver};server=localhost;port=3306;database=quest_stage;UID=linda;PWD=xxx;FBS=3000000;NO_CACHE=1;FORWARD_CURSOR=1

After remove the parameter 'NO_CACHE=1;' the issue gone. As I need to query on very large table in production environment, so I can not remove the parameter.
But the incorrect return status make me not able to display proper message for client.

Suggested fix:
parameter NO_CACHE=1 should not hide function call error.
[6 Feb 2018 12:27] Chiranjeevi Battula
Hello !!,

Thank you for the bug report.
Verified this behavior on Visual Studio 2017 (C#.Net) and Connector / ODBC 5.3.10.

Thanks,
Chiranjeevi.
[6 Feb 2018 12:27] Chiranjeevi Battula
Screenshot

Attachment: Bug_89542.PNG (image/png, text), 33.84 KiB.

[6 Feb 2018 12:27] Chiranjeevi Battula
Screenshot

Attachment: Bug_89542_1.PNG (image/png, text), 42.32 KiB.

[7 Feb 2018 6:09] Sun Edward
@Chiranjeevi, thanks for your verification. 
Do you know is there any workaround before I get the fixing?
Obviously I can not remove the NO_CACHE parameter, because the sharing connections may work on large table with millions of rows.
[4 May 2021 20:43] Stephen Lin
This defect is still reproducible in ODBC driver 8.0.24. Here is my repro steps,

1. Set up a MySQL DSN with "Don't cache results of forward-only cursors" and "Force use of forward-only cursors" checked. 

2. Open ODBCTest. Connect using the DSN created above. 

3. SQLPrepare with query "SELECT 1 + 9223372036854775807;"

4. SQLExecute returns SQL_SUCCESS.

5. If "Don't cache results of forward-only cursors" and "Force use of forward-only cursors" are not checked in DSN, SQLExecute returns SQL_ERROR correctly.
[7 May 2021 7:18] Bogdan Degtyariov
Verified against 8.0.24.
[26 May 2021 9:00] Bogdan Degtyariov
Posted by developer:
 
By the nature of NO_CACHE=1 option the error could not be returned upon calling of SQLPrepare()/SQLExecute().
This is happening because NO_CACHE causes the driver not to fetch and buffer the resultset produced by the statement execution.
The actual error is received from the server not when the statement is executed, but when the resultset is read using mysql_stmt_store_result() or mysql_stmt_fetch().
Normally SQLEcecute() would pre-buffer the resultset and receive the error from the server causing reporting error immediately.
However, NO_CACHE=1 causes SQLExecute() not to read the resultset immadiately, so the error could only be detected upon calling of SQLFetch() function which starts reading resultset row by row.

Currently SQLFetch() does not report the error correctly and this is the bug.
[28 May 2021 10:59] Bogdan Degtyariov
Posted by developer:
 
The patch is pushed into the source tree.
With NO_CACHE=1 the error can only be returned from SQLFetch() instead of SQLExecute().
[4 Jun 2021 20:57] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.26 release, and here's the proposed changelog entry from the documentation team:

Setting NO_CACHE=1 in the ODBC connection string would cause function
errors to go undetected.

Thank you for the bug report.