MySQL Bugs: #94623: Stored procedure call via ADO db and ODBC connector returns resultset of 1 row

Bug #94623	Stored procedure call via ADO db and ODBC connector returns resultset of 1 row
Submitted:	11 Mar 2019 9:31	Modified:	18 Dec 2019 22:38
Reporter:	Remco Kuijer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	8.0.15 (unicode)	OS:	Windows (7 and windows 10)
Assigned to:		CPU Architecture:	x86
Tags:	adostoredproc, ODBC Connector, StoredProc

Description:
A stored procedure call via ADO db (VCL, TADOStoredProc) and MySQL ODBC connector 8.0.15 returns a resultset of 1 row when more rows are expected.

This happens if the stored procedure has 1 or more parameters, if the server is 8.0.15 and the odbc connector is 8.0.15.
When using MySQL ODBC connector 5.3.12 and Server 8.0.15, this works correctly.
(remark; this is not a workaround, as another bug is then present which also kills my application: https://bugs.mysql.com/bug.php?id=83698. The only workaround I currently have is to stick with ODBC driver 5.3.12 and MySQL server 5.6.35)

Attached files will be:
mysql-odbc8-sp-1row.exe - a test application that shows the result of the stored procedure call.
SQL-odbc8.LOG - a sql trace log that contains the sql log trace when using odbc connector 8.0.15.
SQL-odbc53.LOG - a sql trace log that contains the sql log trace when using odbc connector 5.3.12.
testschema.sql - sql file to create the test schema with 3 rows of data and a stored proc.

The most obvious differences in the SQL trace log is from row 1585:
8.0.15: mysql-odbc8-sp- 2a2c-113c	EXIT  SQLExtendedFetch  with return code 100 (SQL_NO_DATA_FOUND)
5.3.12: mysql-odbc8-sp- 2448-29ac	EXIT  SQLExtendedFetch  with return code 0 (SQL_SUCCESS)

How to repeat:
1. Create a schema using the testschema.sql
2. Create ODBC connection to this schema, dsn name:'mysql_sp_test' (create with driver version 8.0.15)
3. Create ODBC connection to this schema, dsn name:'mysql_sp_test_53' (create with driver version 5.3.12)
4. Start the application 'mysql-odbc8-sp-1row.exe'. Defaults are pre-entered (user:root, pass:root, dsn:mysql_sp_test, these may be changed)
5. Click button 'Open pr_test(0)'
6. See the faulty result containing only one row. (id:2, row 2). Expected result: 3 rows! See content of table testtable. Missing rows 1 and 3.
7. Change the dsn name to mysql_sp_test_53.
8. Click button 'Open pr_test(0)'
9. See the result containing 3 rows. This is correct behavior.

Suggested fix:
Make it return all rows that the stored procedure returns in the resultset.
It seems its an issue with the ODBC driver (8.0.15).

a test application that shows the result of the stored procedure call

Attachment: mysql-odbc8-sp-1row.zip (application/zip, text), 1.45 MiB.

sql file to create the test schema with 3 rows of data and a stored proc

Attachment: testschema.sql (application/octet-stream, text), 3.11 KiB.

What does work as a workaround when using MySQL ODBC driver 8.0.15 is to open the stored procedure with a TADOQuery, instead of a TADOStoredProc, like this:
call pr_test(0)

This is a perfect workaround for small projects. Its not so suitable for my current project.

Also tested on Windows 10, gives the same issue.

Thank you for the bug report. Please provide the code of the application (private if you wish) so development could analyze the results.

Thank you for the feedback.

Found out that this issue has something to do with server-side prepared statements.

Just tested by adding NO_SSPS=1 (disables server-side prepared statements) as a workaround into the connectionstring and the problem of the 1 row result goes away. 
*But* using this workaround destroys stored procedure calls where one or more parameters are INOUT or OUT parameters and I get the error: "[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.15]OUT or INOUT argument X for routine pr_storedproc is not a variable or NEW pseudo-variable in BEFORE trigger". And I think this error is legit, because there are no variables anymore.

So, I can't use NO_SSPS=1, but maybe this information points the MySQL developers to the right direction.

Regarding this issue, I can imagine that the cause of this may be the same as the cause for bug #92078 https://bugs.mysql.com/bug.php?id=92078 (Connector/ODBC bug when retrieving TEXT records using parameterized queries)

A comment there says that the bug is fixed in the version 8.0.16, but the fix is improved in 8.0.17.

Is there a way I can download 8.0.16 / 8.0.17 somewhere to see if it is solved? Or is there any ETA known? Because for the moment, we can't use 8.x server and drivers at all.

Posted by developer:
 
Thank you for your bug report, it helped us to find a nasty bug in our code which would be hard to spot otherwise!

To give you an idea, we had a part of code responsible for handling INOUT parameters in stored procedures defined conditionally, like this:

  #ifdef SERVER_PS_OUT_PARAMS
  # define IS_PS_OUT_PARAMS(_stmt) ((_stmt)->dbc->mysql.server_status &    SERVER_PS_OUT_PARAMS)
  #else

Now at certain point SERVER_PS_OUT_PARAMS was changed from a macro definition to an enumeration constant, which makes above #ifdef false and breaks the logic. This SERVER_PS_OUT_PARAMS constant is defined by MySQL client library headers, which we use to implement our ODBC driver, so it is not directly part of our code and for that reason it was easy to miss the change.

Now we will fix this, so that your scenario should work correctly, but on this occasion we discovered a deeper issue with handling of INOUT parameters that will require more time to fix. For now we can fix the basic scenario of calling stored procedure without INOUT parameters, but access to INOUT parameters will not work. We will treat it as a separate bug that we'll fix next.

Posted by developer:
 
Some more details on INOUT parameters issue. We are talking about scenario where we have prepared call to a stored procedure that has OUT/INOUT parameters and returns values via them. From ODBC API level, the values returned via such OUT parameters should be accessible after making a binding of type SQL_PARAM_INPUT_OUTPUT. Before we fix the underlying issue it is not going to work, but there is a way of getting to these values which I want to share here.

Under the hood, the values of OUT parameters are sent by the server as an additional row set, consisting of a single row (with these values). The issue we have is that we can not distinguish this additional row set from other row sets that a stored procedure might produce. It is a bug in our client library that will need some effort to fix. In the meantime, the extra row set with OUT parameter values will be treated as any other row set and can be accessed the same way. So if user knows that he is calling a stored procedure with OUT parameters, he can write code that checks if there is the extra row set in the reply and if it is there, get parameter values from it. This is a possible solution, but not a very good one, because once we are able to distinguish OUT parameters row set from other row sets, we will stop exposing it to the user and this work-around will not work any more.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.19 release, and here's the changelog entry proposed by the documentation team:

  Improved handling for stored procedures and the INOUT parameter.

  For example, if a stored procedure had one or more parameters 
  then an incomplete result set could be returned.

Thank you for the bug report.

Bug #98060 marked as duplicate of this one