connector-x: MSSQL bb8 timeout after 30 seconds on large table

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.

About this issue

  • Original URL
  • State: open
  • Created 2 years ago
  • Reactions: 1
  • Comments: 18 (6 by maintainers)

Most upvoted comments

It works ! Thanks @wangxiaoying 👌 Still, i wonder what’s the root cause in Tiberius

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

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

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

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

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.

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.