Timeout not being enforced in SQL query

[CarlaFernandez]

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be
sure to specify 32-bit Python or 64-bit:

• Python: 3.10 64-bit
• pyodbc: 5.1.0
• OS: Ubuntu 20.04.6 LTS
• DB: Azure SQL Database
• driver: ODBC Driver 18 for SQL Server

Issue

I have created a Python class to perform queries against my Azure SQL Database. The class basically does this:

def _connect(self):
    self.connection_string = (
            f"Driver={driver};Server=tcp:{server},1433;Database={database};"
            f"Uid={db_user};Pwd={db_pass};Encrypt=yes;TrustServerCertificate=no;"
            f"Connection Timeout=30;"
        )
    return pyodbc.connect(self.connection_string)

def launch_query(self, query, params=None, timeout=10):
    try:
        with self._connect() as conn:
            conn.timeout = timeout
            cursor = conn.cursor()
            cursor.execute(query, params or ())
            if cursor.description:
                columns = [column[0] for column in cursor.description]
                rows = cursor.fetchall()
                data = [list(row) for row in rows]
                return pd.DataFrame(data, columns=columns)
            else:
                return None
    except pyodbc.Error as e:
        print("An error occurred:", e)
        if 'HYT00' in str(e):  # Timeout error code
            print("Query execution was cancelled due to timeout.")
        # Handle or re-raise exception
        return None

When I use the launch_query function, setting the timeout to a value different from 0, the timeout is not enforced and my query keeps running for over 100 seconds.

Is this a bug or am I doing something wrong?

[v-chojas]

That driver does support SQL_ATTR_QUERY_TIMEOUT but it's not clear whether you've actually set it. Could you provide an ODBC trace?

(If it does timeout, disconnects might take 10 minutes due to this bug which will be fixed in the next driver release )

Also note that "Connection Timeout" is not a valid connection string keyword. See here for the full valid list: https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute

[gordthompson]

import pyodbc

cnxn = pyodbc.connect("DSN=msodbcsql18_199;UID=scott;PWD=tiger^5HHH")
cnxn.timeout = 33
crsr = cnxn.cursor()
print(crsr.execute("SELECT 1 AS foo").fetchval())

Yes, it appears that the value (33, 0x21) is being sent, but as SQL_ATTR_CONNECTION_TIMEOUT, not SQL_ATTR_QUERY_TIMEOUT.

odbctrace.log

At line 246:

[ODBC][4615][1718126912.519145][SQLSetConnectAttr.c][399]
        Entry:
            Connection = 0x55afcb362b70
            Attribute = SQL_ATTR_CONNECTION_TIMEOUT
            Value = 0x21
            StrLen = -5
[ODBC][4615][1718126912.519162][SQLSetConnectAttr.c][862]
        Exit:[SQL_SUCCESS]

[gordthompson]

Does seem to work, though. This

import pyodbc

cnxn = pyodbc.connect("DSN=msodbcsql18_199;UID=scott;PWD=tiger^5HHH")
cnxn.timeout = 5
crsr = cnxn.cursor()
print(crsr.execute("""\
SET NOCOUNT ON;
WAITFOR DELAY '00:00:10'
SELECT 1 AS foo;
""").fetchval())

fails as expected with

$ python main.py 
Traceback (most recent call last):
  File "/home/gord/venv/main.py", line 6, in <module>
    print(crsr.execute("""\
pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)')

[gordthompson]

I am unable to reproduce your issue:

import pandas as pd
import pyodbc


class DbClass():
    def _connect(self):
        self.connection_string = "DSN=msodbcsql18_199;UID=scott;PWD=tiger^5HHH"
        return pyodbc.connect(self.connection_string)

    def launch_query(self, query, params=None, timeout=10):
        try:
            with self._connect() as conn:
                conn.timeout = timeout
                cursor = conn.cursor()
                cursor.execute(query, params or ())
                if cursor.description:
                    columns = [column[0] for column in cursor.description]
                    rows = cursor.fetchall()
                    data = [list(row) for row in rows]
                    return pd.DataFrame(data, columns=columns)
                else:
                    return None
        except pyodbc.Error as e:
            print("An error occurred:", e)
            if 'HYT00' in str(e):  # Timeout error code
                print("Query execution was cancelled due to timeout.")
            # Handle or re-raise exception
            return None


sql = """\
SET NOCOUNT ON;
WAITFOR DELAY '00:00:10';
SELECT 1 AS foo;
"""
db = DbClass()
result = db.launch_query(sql, timeout=5)
"""console output:
$ python main.py 
An error occurred: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)')
Query execution was cancelled due to timeout.
None
"""

[gordthompson]

Note that the above repro code works as expected when run against Microsoft SQL Server 2019. I suppose it's possible that timeouts may work differently with Azure SQL Database.

[v-chojas]

Timeouts are handled in the driver. Azure or not won't make a difference.

Looks like query timeout is set to the same value as the connection timeout, if the latter is set:

pyodbc/src/cursor.cpp

Line 2528 in 7a710e7

ret = SQLSetStmtAttr(cur->hstmt, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)(uintptr_t)cnxn->timeout, 0);

[gordthompson]

Looks like query timeout is set to the same value as the connection timeout, if the latter is set:

Indeed, it was right there in the log but I missed it. 🤦‍♂️

[ODBC][4615][1718126912.519223][SQLSetStmtAttr.c][265]
        Entry:
            Statement = 0x55afcb43e7d0
            Attribute = SQL_ATTR_QUERY_TIMEOUT
            Value = 0x21
            StrLen = 0
[ODBC][4615][1718126912.519261][SQLSetStmtAttr.c][928]
        Exit:[SQL_SUCCESS]