4 ms·

A real exciting improvement would be support for NFS, instead of hiding behind "some server implementations don't implement locking properly" in 2023...

• wahern 3y ago
  Is there something SQLite would need to do to support an NFS-mounted filesystem that already supported proper locking (fcntl) and sync'ing (fsync)?
  • formerly_proven 3y ago
    It does work but it also rarely results in hanging locks, that's with NFSv3 with NLM. Actual data corruption only happened once or twice. So it's not a super-reliable thing, but when you can't have a real database server or you can only make a network share accessible to the right groups, say, due e.g. organizational dysfunction, then it works, most of the time.
    • kayson 3y ago
      I've had the same experience. I have a NAS VM with nfs4 on Debian, but my services are running on other VMs (since they're in a DMZ vlan). A lot of selfhosted stuff runs on sqlite since its just easier. So there is only one user per db, and 95% of the time I have no issues. Every once in a while I get some sqlite i/o error, and most of the time it's fine but every once in a while I'll have to restart a container.
      • simonw 3y ago
        Are you running it in WAL mode? I've had applications (not on NFS, but with multiple processes accessing the same SQLite database file at once) which throw occasional I/O errors in default journal mode but didn't throw errors at all once I switched into WAL mode. https://til.simonwillison.net/sqlite/enabling-wal-mode https://til.simonwillison.net/sqlite/enabling-wal-mode
        • kayson 3y ago
          I think most of the apps use WAL nowadays (at least from what I remember when I looked into this a while back) but I'll have to check again! Thanks.
        • formerly_proven 3y ago
          WAL mode requires shared memory (unless you disable concurrency through exclusive locking) and therefore doesn't work on network shares.
          • simonw 3y ago
            Great point.
• simonw 3y ago
  If you need your SQLite database to be available over a network I think you'd do a lot better layering a dedicated network protocol on top of it as opposed to trying to get something like NFS working, which is evidently a poor platform for files that need transactional updates made to them by multiple users at once.
  • kayson 3y ago
    > evidently a poor platform for files that need transactional updates made to them by multiple users at once What makes this the case?
    • simonw 3y ago
      If it was a solid platform for this I imagine SQLite would work already!
      • kayson 3y ago
        It does work... Sort of. From what I've read, the NFSv4 server properly implements locking (and I think most of those bits are in the Linux kernel now anyway), but sqlite won't support it anyway. And I am able to run sqlite on NFSv4 with minimal problems. Every once in a while I do get a hiccup, but it's not clear why.
    • btilly 3y ago
      https://access.redhat.com/solutions/120733 https://access.redhat.com/solutions/120733 explains it. The critical bit is the root cause at the end. Which is that NFS sees access to any part of the file as access to all of the file. So any access locks it for anyone. Therefore shared access to the database will cause random hangs due to client behavior. If you turn off locking, then there is no way to avoid data corruption. And this is with NFS working correctly. Which is not a safe assumption given that widely used platforms like OS X implement it wrong. In short, there is a reason that we've joked since the last millennium that NFS stands for "No File System". And the joke is still relevant today.
• ilyt 3y ago
  Eh, I can understand not wanting to deal with NFS fuckery