Description:
We have a MS Access application with linked tables and pass-through queries to the MySQL server. After updating the MySQL ODBC connector to v8.0.33, we observe - a new connection is created per SQL request- as opposed to use an existing connection.
Client:
Windows x64, Office x64(v2303), Ms Access
MySQL ODBC Connector v8.0.33
Server:
MySQL Community Version: 8.0.30
Our actual problem:
we create a temporary table via a pass-t query and expect the temporary table to be visible in subsequent requests by other pass-t queries. -It will be visible if the connection is the same as temporary tables are scoped to each connection.-
>>After opening a linked table with a large data set, we observed subsequent pass-t queries create new connections per query resulting in the previously generated temporary table not being visible.
>>When all linked tables are closed, all pass-t queries go back to using the same connection again and the previously generated temporary table is visible again.
ODBC connection pooling setting in Windows didn't have any effect.
How to repeat:
1. Prepare your environment described as above.
2. Create a linked table (large data set over 10k rows that has Text fields too)
3. Create 3 pass-through queries with sql "select connection_id();"
Scenario 1 (simplified):
1. open each pass-through query one after one. You will see the connection id will be the same in all queries. (re-using an existing connection)
2. Close 2 of the pass-through queries
3. Open the linked table and leave it open
4. Open the remaining 2 pass-through query : << here connection id differs in each query>>
5. Close the linked table
6. Close any of the 2 pass-t queries and reopen << here you can observe all pass-t queries have the same connection id again
We observe each pass-t query opened "AFTER" step 3. has a new connection id.
After step 5. Pass-t queries go back to using the same connection.
This was not the case prior to the 8.0.33 version.
Suggested fix:
Opening a linked table causes subsequent pass-t queries to have a new connection per query should be fixed. All requests within a client should respect re-usable connection/pool.
