MotherDuck reuses database instances for 15 minutes by default

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

TL;DR: the problem and solution

Problem: When opening multiple MotherDuck connections in sequence with different configurations (like switching from read-only to read-write), you might encounter this cryptic error:

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

Why: MotherDuck caches database instances for 15 minutes after connections are closed. When you try to reconnect with different settings before that cache expires, it causes conflicts.

Solution: Add dbinstance_inactivity_ttl=0s to your connection string to disable this caching behavior:

# Before:
conn = duckdb.connect('md:?motherduck_token={}', read_only=True):

# After:
conn = duckdb.connect('md:?dbinstance_inactivity_ttl=0s&motherduck_token={}', read_only=True) 

The full story: debugging a connection error

After recently upgrading a project to the latest DuckDB Python client, we started occasionally seeing strange errors pop up during periodic data ingestion to MotherDuck:

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

This was surprising because I was pretty confident that we were not leaving open any MotherDuck database connections.

Even more surprising: Googling this error turned up absolutely no results whatsoever.

I narrowed it down to a portion of our code that essentially opens two MotherDuck connections in sequence, first a read-only connection and then a read-write connection¹:

with duckdb.connect('md:?motherduck_token={}', read_only=True) as conn:
  result = conn.sql('select count(*) from {}')
  values = result.fetchall()

with duckdb.connect('md:?motherduck_token={}') as conn:
  conn.sql('insert into {} {}')

I eventually stumbled upon a page in the MotherDuck docs that gave me the answer:

DuckDB clients in Python, R, JDBC, and ODBC prevent redundant reinitialization by keeping instances of database-global context cached by the database path.

When connecting to MotherDuck, the instance is cached for an additional 15 minutes after the last connection is closed.

By default, connections to MotherDuck established through the database instance caching supporting DuckDB APIs will reuse the same database instance for 15 minutes after the last connection is closed.

Even though we’re closing each connection when we’re done with it (thanks to the context manager pattern) this cache will mean we’re not starting completely fresh if we reconnect within fifteen minutes.²

In my case, this throws that error because we’re switching from a read-only connection to a read-write connection.³

Disabling the cache

The solution is to disable this cache with the dbinstance_inactivity_ttl parameter, by adding dbinstance_inactivity_ttl=0s to all of our connection strings

with duckdb.connect('md:?dbinstance_inactivity_ttl=0s&motherduck_token={}', read_only=True) as conn:
  result = conn.sql('select count(*) from {}')
  values = result.fetchall()

with duckdb.connect('md:?dbinstance_inactivity_ttl=0s&motherduck_token={}') as conn:
  conn.sql('insert into {} {}')

I’m curious when this feature was added — my code definitely used to work until my recent upgrade, but I can’t find any reference to this behavior in MotherDuck or DuckDB release notes. (In fact, a Google search for dbinstance_inactivity_ttl returns exactly one result — that same Connecting to MotherDuck docs page.)

1. This is distilled, of course; in our actual code, opening and closing these multiple connections makes a little more sense.

2. I don’t entirely understand what’s actually going on here. I’m fuzzy on implementation details like what’s meant by “the same database instance” — do they mean a local in-memory DuckDB instance, or <handwaving> something about ducklings? And I don’t totally follow the boundaries between what sounds like generic DuckDB behavior (caching database-global context) and MotherDuck behavior (reusing the same database instance) — but this at least got me to a solution.

3. This seems like a bug to me; I feel like something in there ought to be smart enough to detect incompatible connection configs and prevent this behavior. On the other hand, the MotherDuck docs do say “you may want to make that period shorter to connect to the same database with a different configuration” so strictly speaking the current behavior is documented.