Connection pooling broken in 5.1.0 or 5.0.1 (with unixODBC 2.3.12)?

kapirajus commented 3 days ago

Please first make sure you have looked at:

Documentation: https://github.com/mkleehammer/pyodbc/wiki
Other issues Possibly related issue - https://github.com/mkleehammer/pyodbc/issues/774 Might also be related to changes introduced in https://github.com/mkleehammer/pyodbc/pull/1278

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.13
pyodbc: 5.1.0 or 5.0.1
unixodbc: 2.3.12
OS: CBL Mariner
DB: SQL Server Azure
driver: ODBC Driver 18 for SQL Server

Issue

Often it is easiest to describe your issue as "expected behavior" and "observed behavior".

Helper code

import pyodbc

# https://github.com/mkleehammer/pyodbc/wiki/The-pyodbc-Module#pooling
pyodbc.pooling = True # Global, to disable set it to False at the beginning, default is True

# https://github.com/mkleehammer/pyodbc/wiki/The-pyodbc-Module#odbcversion
#pyodbc.odbcversion = "3.8" # Set it explicitly, default is 3.x, global parameter set it prior to first connection

def transform_token(access_token: str) -> dict:
    import struct

    # https://ivan-georgiev-19530.medium.com/connect-to-a-sql-database-from-python-using-access-token-62dcf20c5f5f
    exptoken = b""
    for i in bytes(access_token, "UTF-8"):
        exptoken += bytes({i})
        exptoken += bytes(1)
    tokenstruct = struct.pack("=i", len(exptoken)) + exptoken
    # mssql odbc driver specific setting for the token
    SQL_COPT_SS_ACCESS_TOKEN = 1256
    return {SQL_COPT_SS_ACCESS_TOKEN: tokenstruct}

def create_odbc_connection(autocommit: bool = True):
    driver = "ODBC Driver 18 for SQL Server"
    server = "fillyourserver,port"
    database = "fillyour_dbname"

    connection = pyodbc.connect(
        f"DRIVER={driver};SERVER=tcp:{server};DATABASE={database};",
        attrs_before=transform_token(mssparkutils.credentials.getToken("DW")),
        autocommit=autocommit,
    )
    return connection

def exec_statement(connection, statement: str):
    result = None
    try:
        cursor = connection.cursor()
        session_id = cursor.execute("SELECT session_id() AS session_id").fetchone().session_id
        #session_id = cursor.execute("SELECT @@SPID AS session_id").fetchone().session_id
        from textwrap import dedent
        print(f"### session_id={session_id}\t### statement={dedent(statement).strip()} ###")
        cursor.execute(statement)
        if cursor.description:
            columns = [column[0] for column in cursor.description]
            result = [dict(zip(columns, row)) for row in cursor.fetchall()]
    except Exception as e:
        raise
    return result

Test function causing unexpected behaviour (explained below).

def poolTestWithAutoCommit():
    con2 = create_odbc_connection(autocommit=True) # <-- autocommit False is not throwing any exception
    print("pyodbc.connection.autocommit:", con2.autocommit)
    print("query result:", exec_statement(con2, "CREATE SCHEMA test_scnema_001"))
    con2.close()

We are trying to create schema. In every scenario autocommit is True and Connection pooling is enabled.

The issue is when connection pool (global parameter) is enabled.

pyodbc.odbcversion = "3.x" We are getting the following exception ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]111212;Operation cannot be performed within a transaction. (111212) (SQLExecDirectW)')

Is this expected?

pyodbc.odbcversion = "3.8", from recent pyodbc changes (https://github.com/mkleehammer/pyodbc/pull/1278), we tried this parameter.

On first run, the function poolTestWithAutoCommit() creates the schema, no error.

However, on subsequent run, we are getting

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]111212;Operation cannot be performed within a transaction. (111212) (SQLExecDirectW)')

where as it is expected to show "There is already an object named 'test_scnema_001' in the database..."

Is connection pooling broken on latest versions of pyodbc? Any recommended ways to use the pooling?

What is the role of this parameter "odbcversion" with connection pooling in place, do we need to set it explicitly on latest drivers? Looking for examples that simplify complexity involved with various global parameter (pooling, odbcversion and autocommit) combinations.

gordthompson commented 3 days ago

possibly related:

https://github.com/lurcher/unixODBC/commit/9b31cd228d01e768a7ad9126d5da8860f1ed72a9

v-chojas commented 3 days ago

This may also be related: https://github.com/lurcher/unixODBC/issues/149

gordthompson commented 1 day ago

FWIW, I am unable to reproduce your issue on Ubuntu 22.04 hitting an on-prem SQL Server 2019 instance.

gord@xubu-22-04:~$ odbcinst -j
unixODBC 2.3.12
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/gord/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
gord@xubu-22-04:~$ # --------------------
gord@xubu-22-04:~$ cat /usr/local/etc/odbcinst.ini
[ODBC]
Pooling = Yes

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.3.1
UsageCount=1
DontDLClose     = 1
CPTimeout       = 120
gord@xubu-22-04:~$ # --------------------
gord@xubu-22-04:~$ cat ~/.odbc.ini 
[msodbcsql18]
Driver=ODBC Driver 18 for SQL Server
Server=192.168.0.199
Database=test
TrustServerCertificate=yes
gord@xubu-22-04:~$ # --------------------
gord@xubu-22-04:~$ cat zzz.py 
import pyodbc

pyodbc.odbcversion = "3.8"

print(f"{pyodbc.version=}")

def get_connection():
    return pyodbc.connect(
        "DSN=msodbcsql18;UID=scott;PWD=tiger^5HHH",
        autocommit=True,
    )

for x in range(2):
    print(f"[{x=}]")
    cnxn = get_connection()
    crsr = cnxn.cursor()
    crsr.execute("CREATE SCHEMA test_schema_001")
    crsr.close()
    cnxn.close()
    print("Done.")
gord@xubu-22-04:~$ # --------------------
gord@xubu-22-04:~$ python3 zzz.py 
pyodbc.version='5.1.0'
[x=0]
Done.
[x=1]
Traceback (most recent call last):
  File "/home/gord/zzz.py", line 17, in <module>
    crsr.execute("CREATE SCHEMA test_schema_001")
pyodbc.ProgrammingError: ('42S01', "[42S01] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]There is already an object named 'test_schema_001' in the database. (2714) (SQLExecDirectW)")
gord@xubu-22-04:~$

mkleehammer / pyodbc

Connection pooling broken in 5.1.0 or 5.0.1 (with unixODBC 2.3.12)? #1368

Environment

Issue