5 ms·

>In wal2 mode, the system uses two wal files instead of one. The files are named "<database>-wal" and "<database>-wal2", Heh, I wonder how many people will del

by vdaea 3y ago

>In wal2 mode, the system uses two wal files instead of one. The files are named "<database>-wal" and "<database>-wal2", Heh, I wonder how many people will delete the "wal" file thinking that, since they switched to wal2, the wal file must be a leftover.

ceeam 3y ago
Would it be a problem since the wal you delete, its inode, will still be open and processed at the DB closing normally? Just guessing, never tried that.
- andix 3y ago
  There are cases where the wal file is not merged on shutdown of the application. I think a corrupted database can also prevent merging the wal file automatically. A corrupted database can often be repaired, but it needs to be done manually. I've been bitten badly by that issue once. I just mounted the .db file into a docker container and didn't realize that sqlite creates wal files. On an non-graceful shutdown of the application the wal file was not merged into the db and the container deleted. And around a day of changes were lost. Conclusion: Sqlite databases should be placed into their own folder, so it's obvious that it's not always just one file.
  - ricardobeat 3y ago
    This is configurable, and for small things you might disable WAL completely. When using WAL, if you’re copying or backing up the database it’s possible to force a checkpoint, then you can copy the .db file alone knowing exactly up to when it contains data.
    - andix 3y ago
      I had to learn all that the hard way ;)
worksonmine 3y ago
If people just randomly delete files they don't fully understand on a production system maybe they should be bitten.
- bsaul 3y ago
  in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much. IMHO this (having a variable number of files containing the data, depending on your configuration) is the only real design quirks of this technology.
  - another2another 3y ago
    Indeed sqlite's original mission was "to be a replacement for fopen()", but as more features are being added it looks like that initial simplicity can't be maintained.
  - resoluteteeth 3y ago
    > in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much. If you're using it as a standalone file format you presumably shouldn't leave .sqlite files with associated wal files lying around in places where users are going to get confused by them, either by sticking to the rollback journal mode or by using some other method
    - fauigerzigerk 3y ago
      If you use sqlite as a standalone file format for an app that has user managed files then it is hard to avoid this confusion. Rollback journal mode creates a temporary file as well. Also, a scheduled backup process might come along at any moment and non-atomically copy the database file and any -journal or -wal files. Ideally, user visible files should survive copying at random points in time without corruption and without losing too much recent data. Having read "How To Corrupt An SQLite Database File"[1], I'm still not quite sure how to achieve this. [1] https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html
fbdab103 3y ago
As opposed to the `-journal` file already created?

by vdaea 3y ago

ceeam 3y ago

Would it be a problem since the wal you delete, its inode, will still be open and processed at the DB closing normally? Just guessing, never tried that.

andix 3y ago
There are cases where the wal file is not merged on shutdown of the application. I think a corrupted database can also prevent merging the wal file automatically. A corrupted database can often be repaired, but it needs to be done manually. I've been bitten badly by that issue once. I just mounted the .db file into a docker container and didn't realize that sqlite creates wal files. On an non-graceful shutdown of the application the wal file was not merged into the db and the container deleted. And around a day of changes were lost. Conclusion: Sqlite databases should be placed into their own folder, so it's obvious that it's not always just one file.
- ricardobeat 3y ago
  This is configurable, and for small things you might disable WAL completely. When using WAL, if you’re copying or backing up the database it’s possible to force a checkpoint, then you can copy the .db file alone knowing exactly up to when it contains data.
  - andix 3y ago
    I had to learn all that the hard way ;)

worksonmine 3y ago

If people just randomly delete files they don't fully understand on a production system maybe they should be bitten.

bsaul 3y ago
in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much. IMHO this (having a variable number of files containing the data, depending on your configuration) is the only real design quirks of this technology.
- another2another 3y ago
  Indeed sqlite's original mission was "to be a replacement for fopen()", but as more features are being added it looks like that initial simplicity can't be maintained.
- resoluteteeth 3y ago
  > in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much. If you're using it as a standalone file format you presumably shouldn't leave .sqlite files with associated wal files lying around in places where users are going to get confused by them, either by sticking to the rollback journal mode or by using some other method
  - fauigerzigerk 3y ago
    If you use sqlite as a standalone file format for an app that has user managed files then it is hard to avoid this confusion. Rollback journal mode creates a temporary file as well. Also, a scheduled backup process might come along at any moment and non-atomically copy the database file and any -journal or -wal files. Ideally, user visible files should survive copying at random points in time without corruption and without losing too much recent data. Having read "How To Corrupt An SQLite Database File"[1], I'm still not quite sure how to achieve this. [1] https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html

fbdab103 3y ago

As opposed to the `-journal` file already created?