3 ms·

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 alr

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 https://www.sqlite.org/howtocorrupt.html [2] https://forum.audacityteam.org/t/aup3-wal-file-remains-and-leads-to-corrupt-aup3/63309/7 https://forum.audacityteam.org/t/aup3-wal-file-remains-and-l...

wil421 2y ago
>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.
- macintux 2y ago
  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.
eli 2y ago
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.
- fauigerzigerk 2y ago
  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 https://sqlite.org/pragma.html#pragma_journal_mode
  - eli 2y ago
    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.
    - fauigerzigerk 2y ago
      >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.
renonce 2y ago
> 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.
- fauigerzigerk 2y ago
  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.

wil421 2y ago

>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.

macintux 2y ago
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.

eli 2y ago

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.

fauigerzigerk 2y ago
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 https://sqlite.org/pragma.html#pragma_journal_mode
- eli 2y ago
  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.
  - fauigerzigerk 2y ago
    >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.

renonce 2y ago

> 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.

fauigerzigerk 2y ago
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.