unable to open a database file in a btrfs readonly snapshot filesystem
(1) By sprhawk on 2020-06-12 11:15:52
I'm opening a database file in a btrfs readonly snapshot filesystem, using latest sqlite3(`SQLite version 3.32.2 2020-06-04 12:58:43`), inside cli, execute .schema, it reported "unable to open database file". When I copy that file out, sqlite3 can access it without problem. I tried to open in readonly mode (`sqlite3 -readonly db`), it didn't work either.
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)
Is your DB in *WAL* mode? I assume it is, otherwise read-only would have worked. If so, does it use the *Persistent WAL* option? By preventing the -SHM file to be deleted on checkpoints, I think you can open still open that DB in readonly mode then. Although WAL is part of the DB file itself (i.e. persistent), keeping the -SHM around is not, all your connections need to set it. (I believe).
Thanks both of you @sqweek and @ddevienne. It is in WAL mode, not Persistent mode. The database file is retrieved from a device, having no -shm or -wal file. I do see those -shm, and -wal files alongside db file under a readwrite snapshot after I opened it.
What you could do is, after a snapshot, mount it as read-write, and open the db so the state is consistent, and maybe also changing the journaling mode. Note that this will increase the storage since it will rewrite some blocks that can no longer be shared with other snapshots.
I am running SQLite for years on a BTRFS on multiple Linux PC without a problem. Never had an issue. However, if the database is in use by a process that sets a lock you will get an error that something is amiss. Wait a while or kill the process and the problem is solved. Don't try to repair the database. Linux BTRFS and corrupt files are a mystical phenomenon.