[Bug]: ConnectionError because of different configuration when using multiple connections against non memory database due to custom_user_agent

[MG-MW]

What happened?

When using multiple connections to non memory duckdb database I get an error:

OperationalError: (duckdb.duckdb.ConnectionException) Connection Error: Can't open a connection to same database file with a different configuration than existing connections
(Background on this error at: https://sqlalche.me/e/20/e3q8)

I then altered duckdb_engine.__init__.Dialect.connect like this:

    def connect(self, *cargs: Any, **cparams: Any) -> "Connection":
        core_keys = get_core_config()
        preload_extensions = cparams.pop("preload_extensions", [])
        config = cparams.setdefault("config", {})
        config.update(cparams.pop("url_config", {}))

        ext = {k: config.pop(k) for k in list(config) if k not in core_keys}
        if supports_user_agent:
            user_agent = f"duckdb_engine/{__version__}(sqlalchemy/{sqlalchemy_version})"
            if "custom_user_agent" in config:
                user_agent = f"{user_agent} {config['custom_user_agent']}"
            config["custom_user_agent"] = user_agent

        from pprint import pprint
        pprint(cargs)
        pprint(cparams)

        conn = duckdb.connect(*cargs, **cparams)

        for extension in preload_extensions:
            conn.execute(f"LOAD {extension}")

        apply_config(self, conn, ext)

        return ConnectionWrapper(conn)

to print the connection config.

I can reproduce the error like this:

from pathlib import Path
from tempfile import TemporaryDirectory

import sqlalchemy as sa

with TemporaryDirectory() as folder:
    file = Path(folder) / "duck.db"

    url = sa.engine.URL.create(
        drivername="duckdb",
        database=str(file),
    )

    engine_create = sa.create_engine(
        url,
        connect_args={
            "config": {
                "access_mode": "read_write",
            },
        },
    )
    engine_read_only = sa.create_engine(
        url,
        connect_args={
            "config": {
                "access_mode": "read_only",
            },
        },
    )
    engine_create.connect().close()
    engine_create.dispose()

    cnt1 = engine_read_only.connect()
    cnt2 = engine_read_only.connect()

This gives the output similar to this:

()
{'config': {'access_mode': 'read_write',
            'custom_user_agent': 'duckdb_engine/0.13.2(sqlalchemy/2.0.36)'},
 'database': '/tmp/tmph6u7zqzi/duck.db'}
()
{'config': {'access_mode': 'read_only',
            'custom_user_agent': 'duckdb_engine/0.13.2(sqlalchemy/2.0.36)'},
 'database': '/tmp/tmph6u7zqzi/duck.db'}
()
{'config': {'access_mode': 'read_only',
            'custom_user_agent': 'duckdb_engine/0.13.2(sqlalchemy/2.0.36) '
                                 'duckdb_engine/0.13.2(sqlalchemy/2.0.36)'},
 'database': '/tmp/tmph6u7zqzi/duck.db'}

followed by a raised exception:

ConnectionException                       Traceback (most recent call last)
...
OperationalError: (duckdb.duckdb.ConnectionException) Connection Error: Can't open a connection to same database file with a different configuration than existing connections
(Background on this error at: https://sqlalche.me/e/20/e3q8)

This fixes the error by not prepending the user agent if it is already in the list of user agents (does it make sense to have multiple user agents?):

    def connect(self, *cargs: Any, **cparams: Any) -> "Connection":
        core_keys = get_core_config()
        preload_extensions = cparams.pop("preload_extensions", [])
        config = cparams.setdefault("config", {})
        config.update(cparams.pop("url_config", {}))

        ext = {k: config.pop(k) for k in list(config) if k not in core_keys}
        if supports_user_agent:
            user_agent = f"duckdb_engine/{__version__}(sqlalchemy/{sqlalchemy_version})"
            if "custom_user_agent" not in config:
                config["custom_user_agent"] = user_agent
            elif user_agent not in config["custom_user_agent"].split(" "):
                user_agent = f"{user_agent} {config['custom_user_agent']}"
                config["custom_user_agent"] = user_agent

        conn = duckdb.connect(*cargs, **cparams)

        for extension in preload_extensions:
            conn.execute(f"LOAD {extension}")

        apply_config(self, conn, ext)

        return ConnectionWrapper(conn)

DuckDB Engine Version

0.13.2

DuckDB Version

1.1.2

SQLAlchemy Version

2.0.36

Relevant log output

---------------------------------------------------------------------------
ConnectionException                       Traceback (most recent call last)
File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/engine/base.py:146, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
    145 try:
--> 146     self._dbapi_connection = engine.raw_connection()
    147 except dialect.loaded_dbapi.Error as err:

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/engine/base.py:3302, in Engine.raw_connection(self)
   3281 """Return a "raw" DBAPI connection from the connection pool.
   3282 
   3283 The returned object is a proxied version of the DBAPI
   (...)
   3300 
   3301 """
-> 3302 return self.pool.connect()

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:449, in Pool.connect(self)
    442 """Return a DBAPI connection from the pool.
    443 
    444 The connection is instrumented such that when its
   (...)
    447 
    448 """
--> 449 return _ConnectionFairy._checkout(self)

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:1263, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1262 if not fairy:
-> 1263     fairy = _ConnectionRecord.checkout(pool)
   1265     if threadconns is not None:

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:712, in _ConnectionRecord.checkout(cls, pool)
    711 else:
--> 712     rec = pool._do_get()
    714 try:

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/impl.py:179, in QueuePool._do_get(self)
    178 except:
--> 179     with util.safe_reraise():
    180         self._dec_overflow()

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
    145     self._exc_info = None  # remove potential circular references
--> 146     raise exc_value.with_traceback(exc_tb)
    147 else:

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/impl.py:177, in QueuePool._do_get(self)
    176 try:
--> 177     return self._create_connection()
    178 except:

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:390, in Pool._create_connection(self)
    388 """Called by subclasses to create a new ConnectionRecord."""
--> 390 return _ConnectionRecord(self)

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:674, in _ConnectionRecord.__init__(self, pool, connect)
    673 if connect:
--> 674     self.__connect()
    675 self.finalize_callback = deque()

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:900, in _ConnectionRecord.__connect(self)
    899 except BaseException as e:
--> 900     with util.safe_reraise():
    901         pool.logger.debug("Error on connect(): %s", e)

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
    145     self._exc_info = None  # remove potential circular references
--> 146     raise exc_value.with_traceback(exc_tb)
    147 else:

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:896, in _ConnectionRecord.__connect(self)
    895 self.starttime = time.time()
--> 896 self.dbapi_connection = connection = pool._invoke_creator(self)
    897 pool.logger.debug("Created new connection %r", connection)

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/engine/create.py:643, in create_engine.<locals>.connect(connection_record)
    641             return connection
--> 643 return dialect.connect(*cargs, **cparams)

File ~/.envs/py312/lib/python3.12/site-packages/duckdb_engine/__init__.py:279, in Dialect.connect(self, *cargs, **cparams)
    277 pprint(cparams)
--> 279 conn = duckdb.connect(*cargs, **cparams)
    281 for extension in preload_extensions:

ConnectionException: Connection Error: Can't open a connection to same database file with a different configuration than existing connections

The above exception was the direct cause of the following exception:

OperationalError                          Traceback (most recent call last)
Cell In[17], line 34
     31 engine_create.dispose()
     33 cnt1 = engine_read_only.connect()
---> 34 cnt2 = engine_read_only.connect()

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/engine/base.py:3278, in Engine.connect(self)
   3255 def connect(self) -> Connection:
   3256     """Return a new :class:`_engine.Connection` object.
   3257 
   3258     The :class:`_engine.Connection` acts as a Python context manager, so
   (...)
   3275 
   3276     """
-> 3278     return self._connection_cls(self)

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/engine/base.py:148, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
    146         self._dbapi_connection = engine.raw_connection()
    147     except dialect.loaded_dbapi.Error as err:
--> 148         Connection._handle_dbapi_exception_noconnection(
    149             err, dialect, engine
    150         )
    151         raise
    152 else:

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/engine/base.py:2442, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine, is_disconnect, invalidate_pool_on_disconnect, is_pre_ping)
   2440 elif should_wrap:
   2441     assert sqlalchemy_exception is not None
-> 2442     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
   2443 else:
   2444     assert exc_info[1] is not None

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/engine/base.py:146, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
    144 if connection is None:
    145     try:
--> 146         self._dbapi_connection = engine.raw_connection()
    147     except dialect.loaded_dbapi.Error as err:
    148         Connection._handle_dbapi_exception_noconnection(
    149             err, dialect, engine
    150         )

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/engine/base.py:3302, in Engine.raw_connection(self)
   3280 def raw_connection(self) -> PoolProxiedConnection:
   3281     """Return a "raw" DBAPI connection from the connection pool.
   3282 
   3283     The returned object is a proxied version of the DBAPI
   (...)
   3300 
   3301     """
-> 3302     return self.pool.connect()

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:449, in Pool.connect(self)
    441 def connect(self) -> PoolProxiedConnection:
    442     """Return a DBAPI connection from the pool.
    443 
    444     The connection is instrumented such that when its
   (...)
    447 
    448     """
--> 449     return _ConnectionFairy._checkout(self)

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:1263, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1255 @classmethod
   1256 def _checkout(
   1257     cls,
   (...)
   1260     fairy: Optional[_ConnectionFairy] = None,
   1261 ) -> _ConnectionFairy:
   1262     if not fairy:
-> 1263         fairy = _ConnectionRecord.checkout(pool)
   1265         if threadconns is not None:
   1266             threadconns.current = weakref.ref(fairy)

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:712, in _ConnectionRecord.checkout(cls, pool)
    710     rec = cast(_ConnectionRecord, pool._do_get())
    711 else:
--> 712     rec = pool._do_get()
    714 try:
    715     dbapi_connection = rec.get_connection()

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/impl.py:179, in QueuePool._do_get(self)
    177     return self._create_connection()
    178 except:
--> 179     with util.safe_reraise():
    180         self._dec_overflow()
    181     raise

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
    144     assert exc_value is not None
    145     self._exc_info = None  # remove potential circular references
--> 146     raise exc_value.with_traceback(exc_tb)
    147 else:
    148     self._exc_info = None  # remove potential circular references

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/impl.py:177, in QueuePool._do_get(self)
    175 if self._inc_overflow():
    176     try:
--> 177         return self._create_connection()
    178     except:
    179         with util.safe_reraise():

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:390, in Pool._create_connection(self)
    387 def _create_connection(self) -> ConnectionPoolEntry:
    388     """Called by subclasses to create a new ConnectionRecord."""
--> 390     return _ConnectionRecord(self)

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:674, in _ConnectionRecord.__init__(self, pool, connect)
    672 self.__pool = pool
    673 if connect:
--> 674     self.__connect()
    675 self.finalize_callback = deque()

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:900, in _ConnectionRecord.__connect(self)
    898     self.fresh = True
    899 except BaseException as e:
--> 900     with util.safe_reraise():
    901         pool.logger.debug("Error on connect(): %s", e)
    902 else:
    903     # in SQLAlchemy 1.4 the first_connect event is not used by
    904     # the engine, so this will usually not be set

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
    144     assert exc_value is not None
    145     self._exc_info = None  # remove potential circular references
--> 146     raise exc_value.with_traceback(exc_tb)
    147 else:
    148     self._exc_info = None  # remove potential circular references

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/pool/base.py:896, in _ConnectionRecord.__connect(self)
    894 try:
    895     self.starttime = time.time()
--> 896     self.dbapi_connection = connection = pool._invoke_creator(self)
    897     pool.logger.debug("Created new connection %r", connection)
    898     self.fresh = True

File ~/.envs/py312/lib/python3.12/site-packages/sqlalchemy/engine/create.py:643, in create_engine.<locals>.connect(connection_record)
    640         if connection is not None:
    641             return connection
--> 643 return dialect.connect(*cargs, **cparams)

File ~/.envs/py312/lib/python3.12/site-packages/duckdb_engine/__init__.py:279, in Dialect.connect(self, *cargs, **cparams)
    276 pprint(cargs)
    277 pprint(cparams)
--> 279 conn = duckdb.connect(*cargs, **cparams)
    281 for extension in preload_extensions:
    282     conn.execute(f"LOAD {extension}")

OperationalError: (duckdb.duckdb.ConnectionException) Connection Error: Can't open a connection to same database file with a different configuration than existing connections
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Code of Conduct

• I agree to follow this project's Code of Conduct