Exasol SQLA client usage stopped working in certain scenarios

[Nicoretti]

SQLA client stopped working

This issue was discovered by indirect usage, to be precise user(s) of the apache-superset project stumbled upon this issue while using it. apache-superset makes use of SQLA and this exasol dialect for SQLA. Due to the fact that the error couldn't yet narrowed down to a specific part in this library the error scenario/description does rely on apache-superset. Once the root cause is clear this issue should be updated accordingly.

The context in which the issue have been found can be seen here.

How to reproduce

1. Setup an Apache superset installation/instance

   1. install apache superset python -m pip install "apache-superset[exasol]==1.5.0"
   2. Setup apache superset

2. Add an exasol DB

   1. How to add a DB
   2. Exasol DB connection string(s)

3. Try to add a Dataset from the Exasol DB

   1. Select the Exasol DB
   2. Select Schema
   3. Select Table/View

   

Expected Behavior

✔️ On step 3.iii all available Tables/Views are shown and can be selected.

Actual Behavior

💥 No Tables/Views are shown.

[Nicoretti]

The current course of action, regarding the analysis will be:

1. Reproduce the error scenario
2. Identify major changes (especially API changes) between the releases of sqlalchemy-exasol 2.0.10 and 2.1.0
3. Try to identify how those changes could affect/cause the observed behavior

[tkilias]

One of the main differences between version 2.0.10 and 2.1.0 of this project, was the introduction of the usage of the ODBC metadata function.
I saw that superset uses the NullPool which opens and closes the connection every time they are used. For the usage of the ODBC metadata function, we use a function to recursively find the ODBC connection object. Maybe, this causes a problem together with the NullPool.

[Nicoretti]

As already mentioned by @tkilias, there are substantail changes in the following commit: f915033 ("Reimplement metadata queries with odbc metadata functions for pyodbc to avoid deadlocks (#94)", 2020-05-25), which are likely to result in different behavior. To add on this, it is very unlikely that any other comit than f915033 between the versions (git-tags) 2.0.10 and 2.1.0 would be the cause of this issue.