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

[kapirajus]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues
  Possibly related issue - Connection pooling issue Unix #774
  Might also be related to changes introduced in Allow customization of default odbc version #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.

1. 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?

2. pyodbc.odbcversion = "3.8", from recent pyodbc changes (Allow customization of default odbc version #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]

possibly related:

lurcher/unixODBC@9b31cd2

[v-chojas]

This may also be related: lurcher/unixODBC#149

[gordthompson]

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:~$