sqlite in-memory databases do not seem to work with connection pools

[lovasoa]

Hello, I am working on SQLPage, that uses sqlx, and where users can configure their own database connections.

I noticed that when using a connection pool and an in-memory sqlite database, migrations are only applied on whatever connection is made initially, and new connections returned by the connection pool are to new different empty databases. This is a nasty bug, because when you test the system initially, everything works, but only when the initial connection exceeds its idle_timeout, a new connection is successfully returned, which is also a valid connection, but to a completely different database.

A temporary fix is to use idle_timeout(None), but when I do that, I notice a leak of database connections under high load, and at some point all connections are exhausted and the system stays forever in a state where any try to acquire a new connection results in a timeout, even after the peak load has passed.

[mdegel]

I have experienced the same / a very similar issue (sqlx 0.7.2).

I have some code similar to this:

sqlx::any::install_default_drivers();
let pool = AnyPoolOptions::new()
    .max_connections(2)
    .connect("sqlite::memory:")
    .await?;
migrate!("db/migrations").run(&pool).await?;

This code runs without issues (no errors). Attempting to access the DB later yields errors like these for tables that should have been created during migration:

SqliteError { code: 1, message: "no such table: artifacts" }

For me, setting idle_timeout(None) on the pool (as described above) didn't have any impact, meaning the error stayed the same. What prevents the error for me, is explicitly disabling parallel connections, via .max_connections(1).

[mdegel]

Looking into it a bit further from earlier issues this seems to be an SQLite limitation: https://github.com/launchbadge/sqlx/issues/362#issuecomment-636661146

[kyrias]

That comment should not be relevant anymore because shared mode is supposed to be used by default, but there appears to be something strange going on when using AnyPool where even explicitly enabling the shared cache with sqlite::memory:?cache=shared doesn't work. Using a named in-memory database does appear to work though: sqlite:file:foo?mode=memory&cache=shared.

[hoxxep]

A potential workaround could be to set min_connections to a larger number, which shouldn't leak connections in the same way as idle_timeout. In my limited testing of this workaround, 3 appears to be enough connections.

// define in memory DB connection options
let sqlite_opts = SqliteConnectOptions::from_str(":memory:").unwrap();

// min_connections = 3 to prevent the DB from being wiped randomly
let pool = SqlitePoolOptions::new()
    .min_connections(3)
    .max_connections(10)  // default is 10
    .connect_with(sqlite_opts)
    .await?;

I don't know if this will fix all cases. I thought the pool could be dipping below min_connections, which may drop the in memory DB when it reaches 0 connections — I haven't looked at the code to verify any of this though.

Edit: Sadly it's not a complete fix and the issue still reoccurs, but slightly less frequently.

[filtsin]

I tried

    let pool = SqlitePoolOptions::new()
        .min_connections(1)
        .max_connections(1)
        .idle_timeout(None)
        .max_lifetime(None) // <-

Probably works, connection still alive after some time and tables are not disappeared in memory db.

[LecrisUT]

Could you try https://github.com/launchbadge/sqlx/pull/3289? It addresses a very specific issue though, i.e. if you are linking to a pre-built sqlite package.

[hoxxep]

    let pool = SqlitePoolOptions::new()
        .min_connections(1)
        .max_connections(1)
        .idle_timeout(None)
        .max_lifetime(None) // <- this works!

This also works for me. Did a bit of digging and the pool handler will immediately drop the connection if it's over the max lifetime. See Floating::return_to_pool and PoolConnection::return_to_pool in sqlx-core/src/pool/connection.rs.

The pool logic will drop connections and only afterwards perform min_connections_maintenance. Not a problem for most DB connection pools, but for in-memory SQLite this can cause the entire DB to be dropped.

I'm unsure what the fix for this issue is, as it's sensible behaviour for persistent databases. Perhaps a mix of:

• A documentation note specifically recommending that in-memory SQLite DBs to set max_lifetime = None and min_connections >= 1, on both the min_connections and max_lifetime methods?
• Should we prevent SQLite in-memory pools being instantiated with min_connections < 1 and max_lifetime != None? Or at least log a tracing::error?

[Jeremiah-Griffin]

@hoxxep I don't think it's just in memory connections. I demonstrated a few months back in #3080 that disk backed connections have this, or a very similar issue as well.

Likewise, it isn't clear to me that even when giving the Pool settings that don't 100% error at runtime that the Pool is actually behaving as it should. I vaguely remember running into intermittent table not found errors during concurrent access that suggested the Pool actually only had one usable connection it was repeatedly lending out, and if it had to give out another at the same time that one would be bugged - point being, I suspect a note in the documentation alone would be insufficient.

I was not then and am not now familiar enough with Sqlx's code to be able to test this myself.

[CommanderStorm]

3080 is quite different as that issue is about changing the connection pool settings at runtime from sqlite://sqlite/db.sqlite3 to :memory:.

This means that connections after part of the pool might have intialised suddenly are using a different connection setting.

Imo, #3080 should be a hard error or replace the Pools' connections fully.