News for hackers. Stuff that matters.
Re:SQLite the only database you will ever need in most cases by nh2 on Thursday April 15, 2021 @08:29AM (#26819606)
Note that using SQLite over SSHFS is dangerous if you're not the only user of the DB, because SSHFS does not support file locking.

According to: https://www.fossil-scm.org/forum/forumpost/8749496886

> There's a very real possibility that you could corrupt the SQLite DB by running it over SSHFS.

Re:SQLite the only database you will ever need in most cases by loeg on Thursday April 15, 2021 @11:02AM (#26821315)
If you're just using sshfs for RO queries for debugging, it should be harmless.
Re:SQLite the only database you will ever need in most cases by tedunangst on Thursday April 15, 2021 @02:28PM (#26824382)
It will try to replay the journal on open, though, even without running a query if I'm not mistaken. (There's a read only flag for open, just have to be sure you're using it.)
Re:SQLite the only database you will ever need in most cases by hannofcart on Thursday April 15, 2021 @10:55AM (#26821227)
I wasn't aware of this. Thanks for the information. Is this true even when you use a read-only sshfs mount?
Re:SQLite the only database you will ever need in most cases by loeg on Thursday April 15, 2021 @11:00AM (#26821292)
No. Read-only cannot mutate the database. I don't know if Sqlite ensures you won't observe inconsistent results if there is a concurrent (single) writer.
Re:SQLite the only database you will ever need in most cases by nh2 on Thursday April 15, 2021 @10:16PM (#26828615)
The docs [1] and this [2] suggest that you'll probably observe inconsistent results.

WAL mode supports it ("reading and writing can proceed concurrently"), but "does not work over a network filesystem" like SSHFS [3] because it either requires shared memory on the same machine, or exclusive locking [4] (which SSHFS does not provide, and which again does not allow readers concurrent with a writer).

Thus overall, you can only rely on your read queries over SSHFS if you know that there is no concurrent writer.

[1]: https://www.sqlite.org/lockingv3.html [2]: https://stackoverflow.com/questions/1005206/does-sqlite-lock... [3]: https://www.sqlite.org/wal.html [4]: https://www.sqlite.org/wal.html#noshm