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 2023 13:14	Modified:	26 Jan 2024 13:00
Reporter:	Jan Stepan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.36	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 ?

Hello, guys!

In 2024-01-16, the MySQL Connector/ODBC 8.3.0 was released. Looking for something related to our issues, I've found this in the Relese Notes (https://dev.mysql.com/doc/relnotes/connector-odbc/en/news-8-3-0.html):

"Bugs Fixed
ADODB.Recordset.Open() reported a transaction error when a string type was used with prepared statements. The fix was changing the SQL_MAX_CONCURRENT_ACTIVITIES value from 1 to 0 (unlimited). (Bug #36031548)
References: This issue is a regression of: Bug #34916959."

Back to the Release Notes of the version 8.0.33, I've noticed a added "Note":

"The SQL_MAX_CONCURRENT_ACTIVITIES value changed from 0 (unlimited) to 1 because Connector/ODBC supports one active statement per connection.
Note: this change was reverted in Connector/ODBC 8.3.0. (Bug #34916959)"

I will test connections using this new release MySQL Connector/ODBC 8.3.0 this week. Can you guys make tests either? Let's check it out if the issue is gone!

We discovered that changing SQL_MAX_CONCURRENT_ACTIVITIES to 1 is causing other problems and we had to revert that change. This was reverted back to 0 in current versions 8.3.0 and 8.0.36.

To offer any solution we must be able to reproduce the issue locally first.

Hello!
I've installed the Connector ODBC 8.3.0, x64 and updated the configuration in the ODBC Data Sources x64 on MS Windows 11 Home.

After that, I got successful in the application usage with no error found until now, regarding the erros I've shared in November, 14.

Thank's for the reversion of SQL_MAX_CONCURRENT_ACTIVITIES to 0 as default.

If any issue come to appear, I'll share.

Unfortunately, I had to roll back the Driver to version 8.0.31, x64, because the recent AUTO_RECONNECT functionality removal affects, somehow, my application. 

I noticed if I run the MySQL server remotely (paid host), the process has a timeout of 120 seconds that I couldn't change by anyway. On localhost (my notebook), on the other hand, seems there's no timeout to process that continued exists after 120 seconds. I monitored this using SHOW PROCESSLIST in both servers, remotely and localhost. But, this is another bug to be discussed in another Bug registry (Should I submit e new bug report, considering I've not found any related in the Bugs list opened in the last 15 days?).

My application runs as x64's architecture and, therefore, demands x64 ODBC Connector. But it's also make usage of the AUTO_RECONNECT functionality, set in the  connection string. Also was build using the traditional client-server database connection architecture, with no backend and no other tier. All business rules resides in the .EXE file in each client computer. And, finally, it is also affected by SQL_MAX_CONCURRENT_ACTIVITIES.

In summary, the issue related to SQL_MAX_CONCURRENT_ACTIVITIES was solved with the change of it's value from 1 to 0 (unlimited); but the new 8.3.0 release's AUTO_RECONNECT functionality removal made me come back to the 8.0.31 x64 release.

I can confirm that the issue was successfully resolved in my case as well through the utilization of the new 8.0.36 version.

The AUTO_RECONNECT option was an easy way to repair unexpected disconnects. In fact it was too easy since many users disregarded dangers posed by auto-reconnecting as the client application received no notification about the reconnect being done. As a result the new reconnected session would not have the transactions, session variables, temporary tables etc.

For the above reasons it was removed from libmysql, which is used by ODBC driver for network communications with MySQL Server. Without the seamless support of auto-reconnecting functionality in libmysql there was no reason to keep this option in ODBC driver either.

The MySQL ODBC Driver 8.3.0 and newer will not support AUTO_RECONNECT option anymore.