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)
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 bb8I 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.0seems 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.