Connection pooling not working on version 2.3.11 on ubuntu 22.04

[RossHolland-Melt]

We are running ODBC 17, Ubuntu 22.04, Unixidbc 2.3.11, MS SQL Server, pyodbc 5.2.0, Python 3.10.12

We have set up the .ini is a similar fashion to #774 :

[ODBC]
Pooling            = Yes

[ODBC Driver 17 for SQL Server]
Description = Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1
UsageCount=1
CPTimeout = 100

We have our test.py file that tracks connection times. When we run this on Mac OS, the above works, showing the connection speeds (Please ignore the IDS):

Pooling off:

Connection 1: SPID = 142, ID = 4378212416, Connect = 0.0571s, Query = 0.0022s, Rows = 50, New = True
Connection 2: SPID = 139, ID = 4378212416, Connect = 0.0274s, Query = 0.0019s, Rows = 50, New = False
Connection 3: SPID = 139, ID = 4378212416, Connect = 0.0281s, Query = 0.0020s, Rows = 50, New = False
Connection 4: SPID = 139, ID = 4378212416, Connect = 0.0275s, Query = 0.0019s, Rows = 50, New = False
Connection 5: SPID = 139, ID = 4378212416, Connect = 0.0270s, Query = 0.0020s, Rows = 50, New = False
Connection 6: SPID = 139, ID = 4378212416, Connect = 0.0315s, Query = 0.0018s, Rows = 50, New = False
Connection 7: SPID = 139, ID = 4378212416, Connect = 0.0326s, Query = 0.0021s, Rows = 50, New = False
Connection 8: SPID = 139, ID = 4378212416, Connect = 0.0270s, Query = 0.0021s, Rows = 50, New = False
Connection 9: SPID = 139, ID = 4378212416, Connect = 0.0271s, Query = 0.0022s, Rows = 50, New = False
Connection 10: SPID = 139, ID = 4378212416, Connect = 0.0277s, Query = 0.0019s, Rows = 50, New = False 

Pooling on:

Connection 1: SPID = 142, ID = 4352194624, Connect = 0.0592s, Query = 0.0021s, Rows = 50, New = True
Connection 2: SPID = 142, ID = 4352194624, Connect = 0.0000s, Query = 0.0021s, Rows = 50, New = False
Connection 3: SPID = 142, ID = 4352194624, Connect = 0.0000s, Query = 0.0018s, Rows = 50, New = False
Connection 4: SPID = 142, ID = 4352194624, Connect = 0.0000s, Query = 0.0019s, Rows = 50, New = False
Connection 5: SPID = 142, ID = 4352194624, Connect = 0.0000s, Query = 0.0022s, Rows = 50, New = False
Connection 6: SPID = 142, ID = 4352194624, Connect = 0.0000s, Query = 0.0019s, Rows = 50, New = False
Connection 7: SPID = 142, ID = 4352194624, Connect = 0.0000s, Query = 0.0023s, Rows = 50, New = False
Connection 8: SPID = 142, ID = 4352194624, Connect = 0.0000s, Query = 0.0027s, Rows = 50, New = False
Connection 9: SPID = 142, ID = 4352194624, Connect = 0.0000s, Query = 0.0019s, Rows = 50, New = False
Connection 10: SPID = 142, ID = 4352194624, Connect = 0.0000s, Query = 0.0019s, Rows = 50, New = False

On Windows without unixodbc, we have similar results.

But on Ubuntu 22.04, no matter what we do, it makes no difference:

No Pooling:

=== First round of connections ===
Connection 1: SPID = 62, ID = 129482506190896, Connect = 0.0287s, Query = 0.0016s, Rows = 50, New = True
Connection 2: SPID = 80, ID = 129482506190896, Connect = 0.0165s, Query = 0.0011s, Rows = 50, New = False
Connection 3: SPID = 99, ID = 129482506190896, Connect = 0.0180s, Query = 0.0016s, Rows = 50, New = False
Connection 4: SPID = 115, ID = 129482506190896, Connect = 0.0207s, Query = 0.0020s, Rows = 50, New = False
Connection 5: SPID = 129, ID = 129482506190896, Connect = 0.0171s, Query = 0.0018s, Rows = 50, New = False
Connection 6: SPID = 142, ID = 129482506190896, Connect = 0.0167s, Query = 0.0017s, Rows = 50, New = False
Connection 7: SPID = 145, ID = 129482506190896, Connect = 0.0159s, Query = 0.0011s, Rows = 50, New = False
Connection 8: SPID = 146, ID = 129482506190896, Connect = 0.0159s, Query = 0.0011s, Rows = 50, New = False
Connection 9: SPID = 147, ID = 129482506190896, Connect = 0.0167s, Query = 0.0014s, Rows = 50, New = False
Connection 10: SPID = 149, ID = 129482506190896, Connect = 0.0152s, Query = 0.0012s, Rows = 50, New = False

Pooling on:

=== First round of connections ===
Connection 1: SPID = 55, ID = 124814602354736, Connect = 0.0255s, Query = 0.0013s, Rows = 50, New = True
Connection 2: SPID = 62, ID = 124814602354736, Connect = 0.0159s, Query = 0.0011s, Rows = 50, New = False
Connection 3: SPID = 77, ID = 124814602354736, Connect = 0.0155s, Query = 0.0010s, Rows = 50, New = False
Connection 4: SPID = 80, ID = 124814602354736, Connect = 0.0187s, Query = 0.0013s, Rows = 50, New = False
Connection 5: SPID = 115, ID = 124814602354736, Connect = 0.0173s, Query = 0.0011s, Rows = 50, New = False
Connection 6: SPID = 129, ID = 124814602354736, Connect = 0.0154s, Query = 0.0013s, Rows = 50, New = False
Connection 7: SPID = 142, ID = 124814602354736, Connect = 0.0149s, Query = 0.0010s, Rows = 50, New = False
Connection 8: SPID = 145, ID = 124814602354736, Connect = 0.0159s, Query = 0.0011s, Rows = 50, New = False
Connection 9: SPID = 146, ID = 124814602354736, Connect = 0.0155s, Query = 0.0011s, Rows = 50, New = False
Connection 10: SPID = 147, ID = 124814602354736, Connect = 0.0158s, Query = 0.0010s, Rows = 50, New = False

[gordthompson]

I am unable to reproduce your issue.

(.venv) gord@xubu-22-04:~$ odbcinst -j
unixODBC 2.3.11
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

Without pooling

(.venv) gord@xubu-22-04:~$ head /usr/local/etc/odbcinst.ini 
[ODBC]
Pooling=No

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1
UsageCount=1
CPTimeout=100
(.venv) gord@xubu-22-04:~$ python pooltest.py 
cnxn 1: 0.097 seconds
cnxn 2: 0.054 seconds
cnxn 3: 0.049 seconds
cnxn 4: 0.055 seconds
cnxn 5: 0.047 seconds

With pooling

(.venv) gord@xubu-22-04:~$ head /usr/local/etc/odbcinst.ini 
[ODBC]
Pooling=Yes

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1
UsageCount=1
CPTimeout=100
(.venv) gord@xubu-22-04:~$ python pooltest.py 
cnxn 1: 0.102 seconds
cnxn 2: 0.000 seconds
cnxn 3: 0.000 seconds
cnxn 4: 0.000 seconds
cnxn 5: 0.000 seconds

[RossHolland-Melt]

@gordthompson thanks for the reply.
May I please have your test script set up ?

[gordthompson]

import time

import pyodbc

for i in range(5):
    t0 = time.perf_counter()
    cnxn = pyodbc.connect("DSN=msodbcsql17_199;UID=scott;PWD=tiger^5HHH;")
    print(f"cnxn {i + 1}: {(time.perf_counter() - t0):0.3f} seconds")
    cnxn.close()
    time.sleep(1)