SQLite Forum

unable to open a database file in a btrfs readonly snapshot filesystem
Is there a -journal file in the snapshot next to it? That's also part of the database.

To guarantee integrity of the database, there are situations where sqlite _must_ write to the main file even if you are performing read only operations. For example, imagine that the btrfs snapshot is taken halfway through a COMMIT operation. At this point the main database file is partially updated, but it may be in an inconsistent state. To safely access the database, sqlite has to use the rollback journal to undo the partially written transaction at which point the database is once again guaranteed to be in a consistent state. Obviously on a read-only file system attempting to write the main file will fail, resulting in a failure to open the database.

If you copy both the database and the journal out of the snapshot it will work because sqlite can write the main file.

If you copy only the main file out of the snapshot then sqlite doesn't see the -journal file and doesn't know that the database was mid-transaction. The resulting file _may_ work correctly, or you may end up with a corrupt database, or even a database which doesn't seem corrupted but has incorrect results (eg. half a transaction committed).

(note that this mechanism is how sqlite provides durability in the case of a system crash -- a snapshot partway through a transaction is exactly analogous to a crash)