SQLite corrupt DB recovery crashes autobrr on startup

[rpatterson]

Version

• Version: v1.35.0-rc1
• Build date: 2024-01-16T15:35:47.729Z
• Application: /usr/local/bin/autobrr
• Config path: /config
• Database: sqlite

Describe the bug

When the /config/autobrr.db SQLite DB becomes corrupt, it can't be recovered using the standard SQLite .recover command because the resulting recovered DB crashes autobrr on startup with the following error:

{"level":"fatal","module":"database","type":"sqlite","stack":[{"func":"(*DB).migrateSQLite","line":"124","source":"sqlite.go"},{"func":"(*DB).openSQLite","line":"76","source":"sqlite.go"},{"func":"(*DB).Open","line":"75","source":"database.go"},{"func":"main","line":"70","source":"main.go"},{"func":"main","line":"250","source" :"proc.go"},{"func":"goexit","line":"1598","source":"asm_amd64.s"}],"error":"failed to initialize schema: SQL logic error: table users already exists (1)","time":"2024-01-16T08:39:37-08:00","message":"could not migrate db"}

To Reproduce
Steps to reproduce the behavior:

1. Start with a corrupted SQLite DB that results the following error responses from the API:

{
    "code": "INTERNAL_SERVER_ERROR",
    "message": "error executing query: database disk image is malformed (11)"
}

2. Stop autobrr
3. Run the .recover SQLite command on the corrupt DB:

$ sqlite3 ./autobrr/config/autobrr.db .recover >"./autobrr/config/autobrr.sql.~repaired~"
$ mv -v --backup=numbered ./autobrr/config/autobrr.db ./autobrr/config/autobrr.db.~repaired~
renamed './autobrr/config/autobrr.db' -> './autobrr/config/autobrr.db.~repaired~'
$ sqlite3 ./autobrr/config/autobrr.db <"./autobrr/config/autobrr.sql.~repaired~"

4. Start autobrr
5. See error in the logs and note autobrr failed to start

Expected behavior
Some method of DB recovery should be supported, preferably one that uses standard, well supported SQLite recovery tools.

Desktop (please complete the following information):

# docker compose config --resolve-image-digests autobrr | grep '^ *image:'
   image: ghcr.io/autobrr/autobrr:latest@sha256:bbe518ae753ed6603a9abd8d009e811164c56d7d9ffeba45f366bf454bfd3ac3

[rpatterson]

I was able to workaround this by letting autobrr initialize a fresh clean DB and then loading the recovered SQL into that DB:

$ sqlite3 ./autobrr/config/autobrr.db .recover >"./autobrr/config/autobrr.sql.~repaired~"
$ mv -v --backup=numbered ./autobrr/config/autobrr.db ./autobrr/config/autobrr.db.~corrupt~
renamed './autobrr/config/autobrr.db' -> './autobrr/config/autobrr.db.~corrupt~'
+ docker compose start autobrr
[+] Running 1/1
 ✔ Container autobrr  Started                                                                                                                                                            0.2s
+ sleep 2
+ docker compose stop autobrr
[+] Stopping 1/1
 ✔ Container autobrr  Stopped                                                                                                                                                            0.2s
$ sqlite3 ./autobrr/config/autobrr.db <"./autobrr/config/autobrr.sql.~repaired~"

I'm still unclear on why loading the recovered SQL into an uninitialized DB results in the fatal DB migration error. There may still be a bug there. In the meantime, hopefully this will be helpful to other users. Maybe add it to the docs?

[zze0s]

Hi! Do you have any logs from before the fatal log? This was with an existing db correct? And you are running the latest tag that for unknown reason was built with the RC release 🤔

[rpatterson]

Hi! Do you have any logs from before the fatal log?

@zze0s No, I have no idea how long ago the corruption started, but I know it was at least long enough that no currently available logs cover it. Sorry.

This was with an existing db correct?

Yes.

And you are running the latest tag that for unknown reason was built with the RC release 🤔

That's copied from this project's README. I agree that part of the README should be updated. Unfortunately, I don't see any tag in this project's registry that would keep current with the latest final release. Something should be added to the build process to tag final releases with a separate tag. It should also probably tag major, and maybe even minor versions.

[zze0s]

@zze0s No, I have no idea how long ago the corruption started, but I know it was at least long enough that no currently available logs cover it. Sorry.

Alright, then it's hard to conclude anything. Are you storing the data on a NFS mount or similar? SQLite does not work well with that. I do not think it was anything with the update that broke it and instead suspect something with your environment.

That's copied from this project's README. I agree that part of the README should be updated. Unfortunately, I don't see any tag in this project's registry that would keep current with the latest final release. Something should be added to the build process to tag final releases with a separate tag. It should also probably tag major, and maybe even minor versions.

I don't see us changing from latest in the README since that would require we update it with every release. latest should generally be the latest tagged version but because of a misconfiguration in the Actions Workflow the RC1 release got tagged as latest as well as v1.35.0-rc1. We'll fix it so it does not happen again.

A lot of people are running auto-update tools with docker/containers and so far I have not heard of any DB corruption issues like yours with those that got it auto updated, so that's a relief.

We can keep this open for a couple more days but then I'll close it and only use as a reference, and update the docs with your recovery instructions.

[rpatterson]

Alright, then it's hard to conclude anything. Are you storing the data on a NFS mount or similar?

FWIW, the original cause of the corruption may have been exhausted disk space. But this issue isn't about the cause of the corruptionm it's about autobrr choking on the recovered DB.

I don't see us changing from latest in the README since that would require we update it with every release.

Hehe, so why did you comment on my use of the latest tag in your first reply then? ;-) Oh, I see, you were just noting that latest pointed to a pre-release when it shouldn't. At any rate, depending on latest is bad practice for both maintainers and users because of it's potential surprising behavior. Best practice is to explicitly push project-controlled tags that users can count on to contain final releases, but as you like. It's certainly not an issue I'm reporting or bringing up, just responding.

[zze0s]

As discussed in the other issue the tag issues has already been resolved.

The recovered db issue not working as intended I think might be because we use a SQLite built-in key-value sort of store, PRAGMA to set the schema version. Maybe that isn't recovered correctly with the .recover command 🤔