MySQL Bugs: #111944: Issue with ADO transactions when using MySQL Connector ODBC 8.0.33 32-bit

Bug #111944	Issue with ADO transactions when using MySQL Connector ODBC 8.0.33 32-bit
Submitted:	2 Aug 13:14	Modified:	14 Nov 8:05
Reporter:	Jan Stepan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.33	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	x86
Tags:	ADO, Delphi, transaction

Description:
After upgrading from 8.0.32 to 8.0.33 of ODBC connector an issue in MySQL database part. We reproduced it in our existing software build under both Delphi 10.1 Berlin and Delphi 11.3 Alexandria
The problem arises when attempting to execute multiple queries within the same transaction using a single TADOConnection instance. When using the previous version of the provider, it was sufficient just to call Close method on the first query. When using the new version of the connector, it is necessary to free the first query object before proceeding with next commands.
The issue may also be in underlying Microsoft ADO libraries, but there seems to be some major changes with transaction handling since the last release.

How to repeat:
Create a TADOConnection instance and set up the connection.
Begin a transaction using TADOConnection.BeginTrans.
Create a TADOQuery instance and associate it with the connection.
Proceed with any select command (using the TADOConnection.Open) and then close the query (using TADOConnection.Close). Do not free the TADOQuery instance.
Execute a command using TADOConnection.Execute, e.g. some table creation command.
This step results in the error message: "Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets."
Alternatively, create another TADOQuery instance and associate it with the same connection.
Attempt to open the second TADOQuery instance using TADOQuery.Open.
This step results in the error: "Unspecified error."

Hi,

Please wait until the C/ODBC expert takes a look at the problem.

Hi Mr. Stepan,

We have noticed that you are using Delphi.

This is a commercial product that we use and we can not repeat your test case, unless we purchase or somehow obtain a copy of Delphi.

If we manage to achieve that, you will hear from us again.

Thank you. I plan to report the issue to Embarcadero support portal too, as soon as I get access.

Note: Regarding the description of this issue, there is a typo:

*Proceed with any select command (using the TADOConnection.Open) and then close the query (using TADOConnection.Close). Do not free the TADOQuery instance.

should be

*Proceed with any select command (using the TADOQuery.Open) and then close the query (using TADOQuery.Close). Do not free the TADOQuery instance.

I received today a reply from Embarcadero Support with some details they discovered regarding the issue:

"
I see that there is a difference in behaviour between 8.0.32 and 8.0.33. In fact, if the two queries are select statements, it is not necessary to to close the first query before opening the second one. My guess is that the older driver implements mysql_stmt_store_result()

https://dev.mysql.com/doc/c-api/8.0/en/mysql-stmt-store-result.html

This forces all rows to be fetched back to the client so that another query can be executed.
"

Hi Jan,

Both drivers 8.0.32 and 8.0.33 work with mysql_stmt_store_result().
Please make sure this functionality is not disabled via the connection string (NO_CACHE=1, which I don't see in your example code, but still can be present in the real live app).

Also, you need to make sure if you are using a DSN (which is also not a case in the example code) the checkbox is not checked in Details >> Cursors/Results >> [ ] Don't cache results of forward-only cursors.

So far, I saw no evidence of this happening.

Perhaps you could create an ODBC trace in ODBC Data Administrator via Tracing tab. The tracing needs to be activated before you start your test application.
Please upload the trace for us to review.

Thanks.

With help of Embarcadero Support, I got some additional details about the issue with the ODBC driver. I will upload SQL ODBC logs comparing the usage of old and new version of the driver. 

Here is a note about the logs I received:
"
I have attached the traces that I made from my test case with both driver versions. Everything looks the same up until the first select statement is closed. I see with the 8.00.33 driver that a SQLFreeStmt is issued (line 4834 of the trace file), which should free all resources from that statement and therefore allow the second one to be opened.
"

Note: Embarcadero offers a trial version of Delphi for the testing purposes.
https://www.embarcadero.com/products/delphi/start-for-free

ODBC SQL trace log of the 8.0.32 version of the driver.

Attachment: 8032_2.log (application/octet-stream, text), 692.00 KiB.

ODBC SQL trace log of the 8.0.33 version of the driver.

Attachment: 8033_3.log (application/octet-stream, text), 162.88 KiB.

Hello, any update about this issue?

Hello, thanks for notification about the issue. 
However, there is not much what can be done from my side.
I was able to reproduce the issue even with the latest 8.0.35 Connector version.
As I noted in previous comments, Embarcadero did not seem to find any issue in their own ODBC handling code. They provided me witch ODBC logs, which I attached to this issue some time ago as the developers requested.

Unfortunately, I had to roll back the Driver to version 8.0.31. Starting from version 8.0.33, including 8.2.0, the following errors appear:

"Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets."

or, in some cases:

"Transaction cannot start because more than one ODBC connection is in use"

Same environment same problem here.
Any advice to avoid this blocking issue ?