MSSQL bb8 timeout after 30 seconds on large table

[AlexRichards77]

What language are you using?

Python

What version are you using?

0.4.1

What database are you using?

Microsoft SQL Server

What dataframe are you using?

Polars

Can you describe your bug?

Getting RuntimeError: Timed out in bb8 when querying large table. Same code executes when limiting number of rows in query or using partition_on.

What are the steps to reproduce the behavior?

Query a large MSSQL table that takes more than 30 seconds to complete.

Example query / code

This succeeds:

query = 'select top 1000 * from my_large_table'
df = pl.read_database_uri(query=query, uri=connection)

This fails:

query = 'select * from my_large_table'
df = pl.read_database_uri(query=query, uri=connection)

This succeeds after more than a minute but I had to create a copy of the table in a different database and add the row_num column. The source I am trying to query does not have a column I can partition on.

query = 'select * from my_large_table'
df = pl.read_database_uri(query=query, uri=connection, partition_on='row_num', partition_num=10)

What is the error?

RuntimeError: Timed out in bb8.

[anderson-bernard0]

If your connection is using user authentication and the number of available sessions in mssql was equal to 1, it may be generating the timeout.

[kmatt]

Seeing the same issue, when MSSQL is queried via Polars, which uses Connector-x.

Rolling back to connectorx==0.4.0 seems to help, but a configurable command timeout would be a better solution.

[sco-atolcd]

I also have the exact same issue. If the query takes too long the bb8 timeout exception appears.
Downgrading to 0.4.0 fix the issue as suggested @kmatt
It seems that problem comes from version bump of bb8 / bb8-tiberius and tiberius in 0.4.1 : chore: bumps to fix some issues in tiberius

[wangxiaoying]

It seems related to this issue of tiberius. What operating system are you using? Is it macos?

[wangxiaoying]

I don't have a test environment for mssql for now. Can anyone try the newest alpha version from test.pypi by pip install -i https://test.pypi.org/simple/ connectorx==0.4.3a1 and see whether the issue remains?

[sco-atolcd]

Yeah sure, let me try that !
And personally I'm running Ubuntu 20.04 (i need to upgrade i know)

[AlexRichards77]

On Windows 11 I get the following error on 0.4.3a1 and 0.4.2

Traceback (most recent call last):
  File "C:\Test\mem_test\.venv\Lib\site-packages\polars\io\database\_utils.py", line 65, in _read_sql_connectorx
    tbl = cx.read_sql(
          ^^^^^^^^^^^^
  File "C:\Test\mem_test\.venv\Lib\site-packages\connectorx\__init__.py", line 426, in read_sql
    raise ValueError(return_type)
ValueError: arrow2

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\Test\mem_test\cat_contact.py", line 66, in <module>
    df = pl.read_database_uri(query=query, uri=connection)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Test\mem_test\.venv\Lib\site-packages\polars\io\database\functions.py", line 434, in read_database_uri
    return _read_sql_connectorx(
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Test\mem_test\.venv\Lib\site-packages\polars\io\database\_utils.py", line 77, in _read_sql_connectorx
    raise type(err)(errmsg) from err
ValueError: arrow2

Downgrading to 0.4.0 also fixes it for me.

[wangxiaoying]

Hi @AlexRichards77 , we have removed arrow2 from the destination since 0.4.2. Can you try to set the destination as arrow?

On Windows 11 I get the following error on 0.4.3a1 and 0.4.2

Traceback (most recent call last):
  File "C:\Test\mem_test\.venv\Lib\site-packages\polars\io\database\_utils.py", line 65, in _read_sql_connectorx
    tbl = cx.read_sql(
          ^^^^^^^^^^^^
  File "C:\Test\mem_test\.venv\Lib\site-packages\connectorx\__init__.py", line 426, in read_sql
    raise ValueError(return_type)
ValueError: arrow2

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\Test\mem_test\cat_contact.py", line 66, in <module>
    df = pl.read_database_uri(query=query, uri=connection)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Test\mem_test\.venv\Lib\site-packages\polars\io\database\functions.py", line 434, in read_database_uri
    return _read_sql_connectorx(
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Test\mem_test\.venv\Lib\site-packages\polars\io\database\_utils.py", line 77, in _read_sql_connectorx
    raise type(err)(errmsg) from err
ValueError: arrow2

Downgrading to 0.4.0 also fixes it for me.

[sco-atolcd]

I tried with 0.4.3a1, I still have the exception exactly 30 seconds after the query starts :
RuntimeError: Timed out in bb8

I also get a new exception on a small table containing 8 lines :
File "/home/sco/Dev/MEAE/rece/pipelines/traitements-reprise-rece/utils/dbs/scec_doc_sqlserver_utils.py", line 31, in query_with_polars_scec_docs df = cx.read_sql(conn, sql_query, return_type='polars', partition_num=partition_num, partition_on=partition_column).lazy() ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/sco/Dev/MEAE/rece/pipelines/pipeline_rece_venv/lib/python3.11/site-packages/connectorx/__init__.py", line 409, in read_sql result = _read_sql( ^^^^^^^^^^ RuntimeError: MsSQL get None at position: (0, 14)

Here is the sql dump of the table :
bug_0.4.3a1.txt

[AlexRichards77]

Upgrading polars to the latest version 1.24 fixes the arrow2 error but still has the timeout error.

[wangxiaoying]

hmm, seems like it's not caused by the tls then.

[mcrumiller]

Just came here because I am running into this issue as well, unfortunately. This one is really hurting me... I need the pre-execution queries for postgres, but this renders me unable to use mssql :/.

[mgsnuno]

also having timeout when version > 0.4.0

[wangxiaoying]

I downgrad the tiberius version and overwrite the latest alpha version (pip install connectorx==0.4.3a1). Can anyone try whether the bb8 timeout error is still there?