`error: executing SQLite query` ... `database disk image is malformed` ... `binary-cache-v6.sqlite`

[roberth]

Describe the bug

When querying a substituter, the following message is printed.

error: executing SQLite query 'select present, namePart, url, compression, fileHash, fileSize, narHash, narSize, refs, deriver, sigs, ca from NARs where cache = 61 and hashPart = 'zzgqhanslw4zr4g38kdhqpndlwj0j74h' and ((present = 0 and timestamp > 1688533570) or (present = 1 and timestamp > 1685941570))': database disk image is malformed, database disk image is malformed (in '/root/.cache/nix/binary-cache-v6.sqlite')

It's the first time I see this

It may be very remotely related to https://github.com/NixOS/nix/issues/1353.

---

Workaround: IF the file is a binary-cache-v*.sqlite, just remove it. Note that you may have two of these files; one for root (nix-daemon) and one in ~/.cache/nix for any given user that runs nix.

---

Steps To Reproduce

1. Run Nix concurrently on a crash prone VM for a while. (This may be an important factor, probably, but not sure)
2. Find the above message in the log of a build.

Expected behavior

• Ideally SQLite db doesn't get corrupted.
• Nix makes backups of important SQLite dbs (not binary-cache-v*.sqlite)
• Nix makes it easy to recover from this error, e.g. by providing instructions (only worthwhile if this reoccurs)

nix-env --version output

nix-env (Nix) 2.15.1

hercules-ci-agent:

• nix 2.15.1
• agent 2a686c882c07886b64a556d9c58cb15fdf2d4e07 (via flake, nixos module)

identical nix versions:

# nix-store -qR /run/current-system | grep nix-2.15
/nix/store/3n6hkpq4j9igmbcdf1ynzpz3wxkil9jq-nix-2.15.1-doc
/nix/store/aqzqfxc32j0p5dclp3d6sdv0hb3dbl3y-nix-2.15.1-man
/nix/store/bmqhccndbdib0mk3xh4nv6avrqr0bpcy-nix-2.15.1
/nix/store/ync16psd3nxlfgblfv63wagrd5skspkx-nix-2.15.1-dev

Additional context

Reported for completeness. I don't think this requires action yet, because of the suspicious circumstance in which I found the error. However, if more reports come in, some improvements may be in order (docs, error messages, a fix? ...)

Priorities

Add :+1: to issues you find important. (and leave a comment if you think you have possibly relevant details, such as different / better circumstances than the original report)

[pluiedev]

Ran into this bug after the system locked up and hard rebooted.

error: executing SQLite statement 'pragma synchronous = off': database disk image is malformed, database disk image is malformed (in '/root/.cache/nix/fetcher-cache-v1.sqlite')

Unfortunately removing the corrupted file doesn't seem to have solved the problem... EDIT: It did, but somehow my initial sudo rm -f didn't work. Switching to root via su - and deleting manually fixed it

[roberth]

Thanks for letting us know.

Observations:

• This can happen to any cache (or database), including the fetcher cache and binary cache
• The file may come with a separate journal file that may need to be deleted along with the main file (I don't know if sqlite would be confused by it)
• The error may be raised in the first (?) statement (e.g. pragma synchronous = off), or later during an actual query