Vacuum and attach issues with sqlite

[esmevane]

I'm having some issues attempting to use VACUUM (and, relatedly ATTACH) statements with the sqlite drivers. Here's a reproduction repo which demonstrates the issue, check out the actions logs to see what's going on.

What I'm attempting to do:

1. Run a few separate sqlite databases for different purposes. One is for general persistence (file), one is file storage (file), one is cache (memory), and one is a worker queue (memory). This all works exactly as I expected.
2. On application shutdown, I want to have some kind of durable shutdown. That is, if we still have work in the queue, I want to move it to file persistence as part of the shutdown, then rehydrate it when the application comes back.
3. There's a few ways to do the persistence of the worker queue. I can either manually create the file and copy things over, but that would mean every time I update the queue structure I'd have to also update the hydration and shutdown processes. A more robust way would be to VACUUM into the file on shutdown, and then pull that fully back into memory on startup later.

The problem is this: whenever I attempt to run VACUUM or ATTACH statements in sqlx, it seems to just be dropping the statement entirely. As in, it seems to be running the statement but I can't observe any changes. After attach statements, nothing is attached. After vacuum statements, nothing is persisted.

Here's an example tracing log from a github action running the tests with tracing on:

2024-06-12T18:01:01.925232Z  INFO test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: vacuum_reproduction: name="Creating connection with shared cache"
2024-06-12T18:01:01.926629Z DEBUG test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: sqlx::query: summary="PRAGMA foreign_keys = ON; …" db.statement="\n\nPRAGMA foreign_keys = ON;\n" rows_affected=0 rows_returned=0 elapsed=60.353µs elapsed_secs=6.0353e-5
2024-06-12T18:01:01.926733Z  INFO test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: vacuum_reproduction: name="Creating table and inserting data" CREATE_TABLE="\n  create table test (id integer primary key, name text);\n  insert into test (name) values ('hello');\n  insert into test (name) values ('world');\n"
2024-06-12T18:01:01.929048Z DEBUG test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: sqlx::query: summary="create table test (id …" db.statement="\n\ncreate table test (id integer primary key, name text);\ninsert into\n  test (name)\nvalues\n  ('hello');\ninsert into\n  test (name)\nvalues\n  ('world');\n" rows_affected=2 rows_returned=0 elapsed=410.9µs elapsed_secs=0.0004109
2024-06-12T18:01:01.929136Z  INFO test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: vacuum_reproduction: name="Selecting all data from memory table" SELECT_ALL="select * from test"
2024-06-12T18:01:01.929325Z DEBUG test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: sqlx::query: summary="select * from test" db.statement="" rows_affected=1 rows_returned=2 elapsed=95.299µs elapsed_secs=9.5299e-5
[src/main.rs:28:5] format!("vacuum into '{db_str}'") = "vacuum into '/tmp/.tmp6Jdcm7'"
2024-06-12T18:01:01.929482Z  INFO test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: vacuum_reproduction: name="Vacuuming into new db" path="/tmp/.tmp6Jdcm7"
2024-06-12T18:01:01.930071Z DEBUG test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: sqlx::query: summary="vacuum into '/tmp/.tmp6Jdcm7'" db.statement="" rows_affected=1 rows_returned=0 elapsed=477.635µs elapsed_secs=0.000477635
2024-06-12T18:01:01.930183Z  INFO test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: vacuum_reproduction: name="Dropping connection"
2024-06-12T18:01:01.930232Z  INFO test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: vacuum_reproduction: name="Checking if new db exists"
2024-06-12T18:01:01.931415Z DEBUG test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: sqlx::query: summary="PRAGMA foreign_keys = ON; …" db.statement="\n\nPRAGMA foreign_keys = ON;\n" rows_affected=0 rows_returned=0 elapsed=40.415µs elapsed_secs=4.0415e-5
2024-06-12T18:01:01.931525Z  INFO test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: vacuum_reproduction: name="Checking if tables exist"
2024-06-12T18:01:01.932325Z DEBUG test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: sqlx::query: summary="SELECT name FROM sqlite_master …" db.statement="\n\nSELECT\n  name\nFROM\n  sqlite_master\nWHERE\n  type = 'table'\n" rows_affected=0 rows_returned=0 elapsed=127.949µs elapsed_secs=0.000127949
[src/main.rs:66:18] sqlx::query_as::<_,
                    Table>("SELECT name FROM sqlite_master WHERE type='table'").fetch_all(&pool).await? = []
2024-06-12T18:01:01.932421Z  INFO test_sqlx_with_shared_cache_connection:Running sqlx with shared cache connection: vacuum_reproduction: name="Current tables" tables=[]

Things I've already tried:

1. I've run it through various types of connections. Pools. Directly. It doesn't matter.
2. I've attempted to execute everything with shared caching on, and off, and with the pool size turned down entirely, assuming that I was somehow trying to copy an errant database.
3. I've run this as a query, with execute, and had the exact same results.
4. I've also done the exact same thing but locally, pointed these at my desktop, and observed that nothing gets created.
5. Finally, I tried it with rusqlite, and it worked just fine. I observed file creation, and the records were present, and I was able to rehydrate them.

I'm not sure if I'm misusing things still, but I'm pretty sure at this point that I'm not, and something else is going on. I'm happy to dig further myself if anyone knows what part of the codebase I'd want to look at.

Is this a bug? Am I holding it wrong? Should I use a different library for just this one case?

[esmevane]

I wanted to update here since, although I haven't solved this, I have figured out a workaround.

The workaround. Changing the invocation string for the connection from ":memory:" (a memory-mode db) to "" (a temp file that sqlite manages) seems like it's enough of a difference for the vacuum and attach statements to start working.

These two statements seem very close to me in terms of the connection configuration they yield:

let options = dbg!(sqlx::sqlite::SqliteConnectOptions::from_str(":memory:"))?;
let options = dbg!(sqlx::sqlite::SqliteConnectOptions::from_str(""))?;

Here's that output:

[src/main.rs:255:19] sqlx::sqlite::SqliteConnectOptions::from_str(":memory:") = Ok(
    SqliteConnectOptions {
        filename: "file:sqlx-in-memory-0",
        in_memory: true,
        read_only: false,
        create_if_missing: false,
        shared_cache: true,
        statement_cache_capacity: 100,
        busy_timeout: 5s,
        log_settings: LogSettings {
            statements_level: Debug,
            slow_statements_level: Warn,
            slow_statements_duration: 1s,
        },
        immutable: false,
        vfs: None,
        pragmas: {
            "key": None,
            "cipher_plaintext_header_size": None,
            "cipher_salt": None,
            "kdf_iter": None,
            "cipher_kdf_algorithm": None,
            "cipher_use_hmac": None,
            "cipher_compatibility": None,
            "cipher_page_size": None,
            "cipher_hmac_algorithm": None,
            "page_size": None,
            "locking_mode": None,
            "journal_mode": None,
            "foreign_keys": Some(
                "ON",
            ),
            "synchronous": None,
            "auto_vacuum": None,
            "analysis_limit": None,
        },
        extensions: {},
        command_channel_size: 50,
        row_channel_size: 50,
        collations: [],
        serialized: false,
        thread_name: Function,
        optimize_on_close: Disabled,
    },
)
[src/main.rs:256:19] sqlx::sqlite::SqliteConnectOptions::from_str("") = Ok(
    SqliteConnectOptions {
        filename: "",
        in_memory: false,
        read_only: false,
        create_if_missing: false,
        shared_cache: false,
        statement_cache_capacity: 100,
        busy_timeout: 5s,
        log_settings: LogSettings {
            statements_level: Debug,
            slow_statements_level: Warn,
            slow_statements_duration: 1s,
        },
        immutable: false,
        vfs: None,
        pragmas: {
            "key": None,
            "cipher_plaintext_header_size": None,
            "cipher_salt": None,
            "kdf_iter": None,
            "cipher_kdf_algorithm": None,
            "cipher_use_hmac": None,
            "cipher_compatibility": None,
            "cipher_page_size": None,
            "cipher_hmac_algorithm": None,
            "page_size": None,
            "locking_mode": None,
            "journal_mode": None,
            "foreign_keys": Some(
                "ON",
            ),
            "synchronous": None,
            "auto_vacuum": None,
            "analysis_limit": None,
        },
        extensions: {},
        command_channel_size: 50,
        row_channel_size: 50,
        collations: [],
        serialized: false,
        thread_name: Function,
        optimize_on_close: Disabled,
    },
)

What I've tried already. I tried to line up all the above configuration one way or the other, everything except is_memory and filename. Nothing seems to produce the behavior missing in the memory mode, and nothing seems to remove the correct behavior from the tempfile mode.

What would have helped. These two parts of the system are completely opaque and that made debugging a challenge:

• Because sqlite's adapter has to be run as a background task, there is no visibility into some of that background task's processes. That meant the standard "rows effected" debugging info was pretty sparse. This is understandable but unfortunate.
• The SqliteRow struct doesn't implement Debug, and so I couldn't take a look at rows without knowing first what their structure was.

Is there any way to get around either of these?