Comment by fauigerzigerk | Hacker News Reader

Comment by fauigerzigerk | Hacker News Reader

Parent Full thread

Those journal/wal files create a number of potential issues for user visible application files. E.g. a user could copy a database file into a directory that already has an old journal file, corrupting the database [1].

Or users could simply get confused about what to do with those files and contact support a million times just to ask why these files exist, whether they need to be copied as well, why they are getting large, etc.

These issues can be worked around, but I wonder how many apps that use SQLite actually bother doing that. Clearly not all of them [2].

Avoiding file corruption is non-trivial regardless of file format, but giving users additional ways to corrupt their data is never a good thing.

So I think for small, user visible application files that don't need any database functionality, the onus is still on engineers to justify their choice of SQLite.

[1] https://www.sqlite.org/howtocorrupt.html

[2] https://forum.audacityteam.org/t/aup3-wal-file-remains-and-l...

>Or users could simply get confused about what to do with those files and contact support a million times just to ask why these files exist, whether they need to be copied as well, why they are getting large, etc.

This reminds me of a college roommate who was dissatisfied with his computer, likely due to malware from sketchy sites. He decided to delete large or suspicious files from window’s system32 folder. By suspicious I mean he didn’t like the file name.

He had to get a new computer later in the semester.

Decades ago I troubleshot a Solaris server that wouldn’t boot, and discovered the kernel file was missing. I suspect someone had tried to deal with a too-small root partition by removing large files.

You could simply turn off WAL for a simple application database, if you were really worried. You lose some performance and scale, but it should work fine.

Setting the journal_mode to OFF or MEMORY is a sure-fire way of corrupting the database. It's not fine.

https://sqlite.org/pragma.html#pragma_journal_mode

If the application crashes in the middle of a writing a transaction, yes. You can work around this if you care to, and I don't think it's anything specific to SQLite. What happens if your application crashes in the middle writing out a JSON file?

I had actually meant JOURNAL_MODE=DELETE, though. This would not technically use a WAL file, though there would be a rollback journal file that exists only for the duration of the transaction.

>What happens if your application crashes in the middle writing out a JSON file?

The usual way to deal with this is to write to a temporary file and then atomically replace the original file with that temporary file. You could do the same thing with an SQLite database. This is the workaround I was referring to earlier.

journal_mode=delete may not be the worst compromise, but I hate the idea of exposing users to potential database corruption, even in a relatively unlikely event.

> 1.4. Mispairing database files and hot journals

I’m surprised it would corrupt the database. The database could have included a unique identifier as well as in the journal and refuse opening the database if the identifiers don’t match.

Uniquely identifying a database and its journal/wal files would prevent just one of the five mispairing cases they mention. And it's the least likely case.

Reply on news.ycombinator.com