locked sqlite db?

[jonathanstrong]

ran into an issue where shutting down the server with ctrl-c seems to have left the sqlite database in a "locked" state that alexandrie could not recover from on restart.

the log message that displayed is:

Feb 24 13:55:31.950 INFO --> GET /api/v1/crates/my-crate/0.3.15/download 200 1ms , SQL error: database is locked, version: 0.1.0 

I recovered by running the command

echo '.dump' | sqlite3 old.db | sqlite3 new.db

and switching to new.db in my config file.

I don't know exactly what happened to leave the sqlite file in locked state, however, I had just restarted the alexandrie server immediately prior to this, and had not opened the sqlite db manually or otherwise tinkered with the file.

[jonathanstrong]

update: just experienced this same problem again, but with no shutdown having occurred. turns out the server shutdown issue was a red herring, but it is still possible to get a locked sqlite db in the course of normal operation.

$ sqlite3 --version
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

$ uname -a
Linux penthu 5.4.0-65-generic #73-Ubuntu SMP Mon Jan 18 17:25:17 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

alexandrie was built from latest master at ab3429c

[Hirevo]

Hello

Thanks for reporting this issue, and sorry for the long wait.
It is indeed curious that the database can get locked in this way.
From what I can read online, it would be caused by too many concurrent accesses (most-likely writes) to the same table, and seems to be a SQLite-only issue.
In the log message you've provided, the message seems to come from the download endpoint, which indeed does a write to increase the download count.
Maybe the issue happened with too many concurrent writes due to simultaneous crate downloads (eg. during a crate build) ?
May I ask you if you were downloading multiple crates at once when you encountered this issue ?

[kristoff3r]

I experienced this issue in a reproducible way. It happens when I have around 10 published packages and try to cargo check a workspace that depends on most of them. As far as I can tell it is SQLite specific because it doesn't like having more than one open connection, and apparently r2d2 doesn't really protect you against that.

I found a workaround by setting max_conns = 1, I haven't been able to reproduce it since. I'm not sure if it's a proper fix or if it breaks other use cases (e.g. publishing while downloads are happening). There might also be a deeper issue in diesel/r2d2, but it might make sense to set max_conns = 1 as the default when using the SQLite backend.

[Hirevo]

I just remembered that we had this issue already brought up in the past as issue #22, and it was indeed the motivation behind adding the max_conns configuration option.
It's true that we could make the default for max_conns be 1 for the sqlite setup, it would make this error disappear for most users.
I'll look into this shortly, likely with a PR who'll close this issue.