0.6.0: query macro fails on sqlite with "error returned from database: database is locked"

[ipetkov]

When doing a clean build with a freshly created sqlite database the query validation fails with error returned from database: database is locked, which is very prominent and reproducible in CI environments. I can also observe the behavior when running cargo clippy --all-targets from the command line, although sometimes the error randomly goes away with repeated invocations, sometimes it doesn't.

Here is a gist with a minimal reproduction: https://gist.github.com/ipetkov/e6d514b3fc5518276fffd7dbbbc5cc8f

As a quick summary on how to repro:

1. Run sqlx mig add and add an arbitrary table
2. Create a minimal project with a main file which runs any sqlx::query!(...)
3. cargo clean
4. yes | sqlx database reset
5. cargo clippy --all-targets

This triggers with sqlx-cli version 0.6.0, and using sqlx = "0.6.0" in Cargo.toml`. This does not trigger with versions 0.5.9.

Also worth noting that the issue does not trigger with a plain cargo clippy invocation. Perhaps the issue is that the regular crate build and the test version are compiled in parallel which results in concurrent proc-macro expansions trying to access the database at the same time

[ipetkov]

Confirming that using cargo clippy --all-targets -j1 (i.e. run one job at a time) does not trigger the error.

I did notice that the expansion proc-macro has a connection cache that is never cleared, perhaps this is something to note

[CosmicHorrorDev]

I was able to reproduce the issue using the above setup, and managed to get rid of the issue by reverting the journal changes with the following

diff --git a/sqlx-macros/src/query/mod.rs b/sqlx-macros/src/query/mod.rs
index dbd6bd92..c98abc28 100644
--- a/sqlx-macros/src/query/mod.rs
+++ b/sqlx-macros/src/query/mod.rs
@@ -208,7 +208,7 @@ fn expand_from_db(input: QueryMacroInput, db_url: &str) -> crate::Result<TokenSt
                     let sqlite_conn = SqliteConnectOptions::from_str(db_url)?
                         // Connections in `CONNECTION_CACHE` won't get dropped so disable journaling
                         // to avoid `.db-wal` and `.db-shm` files from lingering around
-                        .journal_mode(SqliteJournalMode::Off)
+                        // .journal_mode(SqliteJournalMode::Off)
                         .connect()
                         .await?;
                     AnyConnection::from(sqlite_conn)

Doing that does still leaves the lingering -shm and -wal files that were mentioned in #1782 and is why journaling was disabled for SQLite