MySQL Bugs: #102820: MySQL Connector/ODBC 8.0.19 and later versions error

Bug #102820	MySQL Connector/ODBC 8.0.19 and later versions error - client program crashes
Submitted:	4 Mar 2021 20:22	Modified:	22 Apr 2021 10:36
Reporter:	Евгений Б	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	8.0.19-8.0.23	OS:	Windows (10)
Assigned to:		CPU Architecture:	Other (x64)

Description:
Changes in MySQL Connector/ODBC 8.0.19 (2020-01-13, General Availability) and later versions: "With prepared SELECT statements the fixed-length numeric types such as INT were set to 0 instead of their stored value. (Bug #30428851, Bug #97191)".
Because of this changes on Memory engine tables the error occurs accidentally during SqlExecute command, and multithreaded client program crashes. The error arise because of Connector/ODBC driver.
Such error doesn't occur with Connector/ODBC 8.0.18 and earlier versions. Please, correct the error.

How to repeat:
evg2178@yandex.ru

Thank you for the bug report. Please provide a repeatable test case. Thanks.

This is too difficalt code to repeat - multithreaded application. All SQL commands are in SQL.LOG file. That's enough.

Thanks for providing us the ODBC trace log to analyze.

I have noticed that your code allocates and uses multiple HSTMT handles per connection:

For a single HDBC handle 0x0000000007423EA0 a lot of HSTMT handles are allocated:

0x0000000007424C70
0x00000000074252C0
0x0000000007425910
0x0000000007425F60
...
0x0000000007428540 - "server has gone away" happened when using this HSTMT:

SpreadTradingDe 96c-115c EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x0000000007423EA0
SQLHANDLE * 0x000000000083B948 ( 0x0000000007428540) <--- newly allocated HSTMT

The process id for SpreadTradingDe is 0x96c and the thread where HSTMT is allocated 0x115c.
I can see that some of HSTMT handles allocated in this thread are used there as well.

However, the problematic handle is used by multiple threads: at least 0x115c and 0xd74:

SpreadTradingDe 96c-d74 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x0000000007428540

DIAG [08S01] [MySQL][ODBC 8.0(a) Driver][mysqld-8.0.23]MySQL server has gone away (2006)

Please note that HSTMT handles are not supposed to be shared between threads unless your application provides the synchronization and protection.
There is no performance benefits for using this approach because threads sharing the same HSTMT (and hence the same HDBC connection handle) cannot run queries in parallel. However, attempting to do so will yield an undefined result such as "Commands our of Sync" or "Server has gone away" etc.

The HSTMT handles have some basic protection inside ODBC driver, but not for all scenarios and functions.
With the previous versions of ODBC connector it could be a pure coincidence that it worked.

For multithreaded environment the recommended way of designing the ODBC application would be using one HDBC connection per thread. This one connection can have multiple HSTMT handles in the same thread, but since they are not running in parallel it is safe.

So far the ODBC Trace has been pointing to the scenario not supported by MySQL ODBC Driver.
Unless you supply the evidence that HSTMT handles belonging to the same connection are not used at the same time we will have no choice, but consider it as unsupported usage and reject the bug report.

"For multithreaded environment the recommended way of designing the ODBC application would be using one HDBC connection per thread".
Thanks.

That's all

new log file

Attachment: SQL4.LOG (application/octet-stream, text), 83.43 KiB.

But the problem still exists. Now each thread creates its own connection HDBC and one STMT handle per connection, also sets attributes and other initialization SQL commands.
Then each thread repeatadly calls typical procedure:
 1. SQLFreeSTMT with SQL_CLOSE flag
 2. SQLExecute
 2. repeat SQLFetch until SQL_NO_DATA
With MySQL Connector/ODBC 8.0.19 and later versions when the thread calls SQLExecute it unexpectedly return from the described procedure and so it can call this procedure again, quering SQLFreeSTMT, and so on. The return is happened inside the SQLExecute code, i.e. INSIDE Connector/ODBC driver.  This unexpected sequence repeats any times until SQLExecute return ERROR code, the client program crashes.
While ca4-1274 thread works with described error, another ca4-3cfc thread somehow works properly, but the code of each thread is similar.
This problem does'n occur with MySQL Connector/ODBC 8.0.18.
New SQL4.LOG file is attached to this explanation.

Dear sirs, please reply! And make the status of bug "Open".

Thanks for supplying the fresh data.
We will come back to you after doing the analysis.