16 ms·

How would this work differently? As soon as you encounter a checksum failure, you can't trust anything from that point on. If the checksum were just per-page an

How would this work differently? As soon as you encounter a checksum failure, you can't trust anything from that point on. If the checksum were just per-page and didn't build on the previous page's checksum, you can't just apply pages from the WAL that were valid, skipping the ones which were not. The database at the end of that process would be corrupt. If you stop at the first failure, the database is restored to the last good state. That's the best outcome that can be achieved under the circumstances. Some data could be lost, but there wasn't anything sensible you could do with it anyway.

avinassh 1y ago
> How would this work differently? I would like it to raise an error and then provide an option to continue or stop. Since continuing is the default, we need a way to opt in to stopping on checksum failure. Not all checksum errors are impossible to recover from. Also, as the post mentions, only some non important pages could be corrupt too. My main complaint is that it doesn't give developers an option.
- thadt 1y ago
  Aight, I'll bite: continue or stop... and do what? As others have pointed out, the only safe option to get back to a consistent state is to roll back to a safe point. If what we're really interested in is the log part of a write ahead log - where we could safely recover data after a corruption, then a better tool might be just a log file, instead of SQLite.
  - avinassh 1y ago
    > Aight, I'll bite: continue or stop... and do what? As others have pointed out, the only safe option to get back to a consistent state is to roll back to a safe point. Attempt to recover! Again, not all checksum errors are impossible to recover. I hold the view that even if there is a 1% chance of recovery, we should attempt it. This may be done by SQLite, an external tool, or even manually. Since WAL corruption issues are silent, we cannot do that now. There is a smoll demo in the post. In it, I corrupt an old frame that is not needed by the database at all. Now, one approach would be to continue the recovery and then present both states: one where the WAL is dropped, and another showing whatever we have recovered. If I had such an option, I would almost always pick the latter.
    - ncruces 1y ago
      > Since WAL corruption issues are silent, we cannot do that now. You do have backups, right?
      - avinassh 1y ago
        Yes. Say if I am using something like litestream, then all subsequent generations will be affected. At some point, I'd go back and find out the broken generation. Instead of that, I'd prefer for it to fail fast
    - lxgr 1y ago
      What about all classes of durability errors that do not manifest in corrupted checksums (e.g. "cleanly" lost appends of entire transactions to the WAL)? It seems like you're focusing on a very specific failure mode here. Also, what if the data corruption error happens during the write to the actual database file (i.e. at WAL checkpointing time)? That's still 50% of all your writes, and there's no checksum there!
    - NortySpock 1y ago
      The demo is good, through I wish you had presented it as text rather than as a gif. I do see your point of wanting an option to refuse to delete the wal so a developer can investigate the wal and manually recover... But the the typical user probably wants the database to come back up with a consistent, valid state if power is lost. They do not want have the database refuse to operate because it found uncommitted transactions in a scratchpad file... As a SQL-first developer, I don't pick apart write-ahead logs trying to save a few bytes from the great hard drive in the sky, I just want the database to give me the current state of my data and never be in an invalid state.
      - avinassh 1y ago
        > As a SQL-first developer, I don't pick apart write-ahead logs trying to save a few bytes from the great hard drive in the sky, I just want the database to give me the current state of my data and never be in an invalid state. Yes, that is a very valid choice. Hence, I want databases to give me an option, so that you can choose to ignore the checksum errors and I can choose to stop the app and try to recover.
- lxgr 1y ago
  Giving developers that option would require SQLite to change the way it writes WALs, which would increase overhead. Checksum corruptions can happen without any lower-level errors; this is a performance optimization by SQLite. I've written more about this here: https://news.ycombinator.com/item?id=44673991 https://news.ycombinator.com/item?id=44673991
  - avinassh 1y ago
    > Giving developers that option would require SQLite to change the way it writes WALs, which would increase overhead. Yes! But I am happy to accept that overhead with the corruption detection.
    - lxgr 1y ago
      But why? You'd only get partial corruption detection. As I see it, either you have a lower layer you can trust, and then this would just be extra overhead, or you don't, in which case you'll also need error correction (not just detection!) for the database file itself.
- slashdev 1y ago
  It is good that it doesn't give you an option. I don't want some app on my phone telling me its database is corrupt, I want it to always load back to the last good state and I'll handle any missing data myself. The checksums are not going to fail unless there was disk corruption or a partial write. In the former, thank your lucky stars it was in the WAL file and you just lose some data but have a functioning database still. In the latter, you didn't fsync, so it couldn't have been that important. If you care about not losing data, you need to fsync on every transaction commit. If you don't care enough to do that, why do you care about checksums, it's missing the point.

avinassh 1y ago

> How would this work differently? I would like it to raise an error and then provide an option to continue or stop. Since continuing is the default, we need a way to opt in to stopping on checksum failure. Not all checksum errors are impossible to recover from. Also, as the post mentions, only some non important pages could be corrupt too. My main complaint is that it doesn't give developers an option.

thadt 1y ago
Aight, I'll bite: continue or stop... and do what? As others have pointed out, the only safe option to get back to a consistent state is to roll back to a safe point. If what we're really interested in is the log part of a write ahead log - where we could safely recover data after a corruption, then a better tool might be just a log file, instead of SQLite.
- avinassh 1y ago
  > Aight, I'll bite: continue or stop... and do what? As others have pointed out, the only safe option to get back to a consistent state is to roll back to a safe point. Attempt to recover! Again, not all checksum errors are impossible to recover. I hold the view that even if there is a 1% chance of recovery, we should attempt it. This may be done by SQLite, an external tool, or even manually. Since WAL corruption issues are silent, we cannot do that now. There is a smoll demo in the post. In it, I corrupt an old frame that is not needed by the database at all. Now, one approach would be to continue the recovery and then present both states: one where the WAL is dropped, and another showing whatever we have recovered. If I had such an option, I would almost always pick the latter.
  - ncruces 1y ago
    > Since WAL corruption issues are silent, we cannot do that now. You do have backups, right?
    - avinassh 1y ago
      Yes. Say if I am using something like litestream, then all subsequent generations will be affected. At some point, I'd go back and find out the broken generation. Instead of that, I'd prefer for it to fail fast
  - lxgr 1y ago
    What about all classes of durability errors that do not manifest in corrupted checksums (e.g. "cleanly" lost appends of entire transactions to the WAL)? It seems like you're focusing on a very specific failure mode here. Also, what if the data corruption error happens during the write to the actual database file (i.e. at WAL checkpointing time)? That's still 50% of all your writes, and there's no checksum there!
  - NortySpock 1y ago
    The demo is good, through I wish you had presented it as text rather than as a gif. I do see your point of wanting an option to refuse to delete the wal so a developer can investigate the wal and manually recover... But the the typical user probably wants the database to come back up with a consistent, valid state if power is lost. They do not want have the database refuse to operate because it found uncommitted transactions in a scratchpad file... As a SQL-first developer, I don't pick apart write-ahead logs trying to save a few bytes from the great hard drive in the sky, I just want the database to give me the current state of my data and never be in an invalid state.
    - avinassh 1y ago
      > As a SQL-first developer, I don't pick apart write-ahead logs trying to save a few bytes from the great hard drive in the sky, I just want the database to give me the current state of my data and never be in an invalid state. Yes, that is a very valid choice. Hence, I want databases to give me an option, so that you can choose to ignore the checksum errors and I can choose to stop the app and try to recover.
lxgr 1y ago
Giving developers that option would require SQLite to change the way it writes WALs, which would increase overhead. Checksum corruptions can happen without any lower-level errors; this is a performance optimization by SQLite. I've written more about this here: https://news.ycombinator.com/item?id=44673991 https://news.ycombinator.com/item?id=44673991
- avinassh 1y ago
  > Giving developers that option would require SQLite to change the way it writes WALs, which would increase overhead. Yes! But I am happy to accept that overhead with the corruption detection.
  - lxgr 1y ago
    But why? You'd only get partial corruption detection. As I see it, either you have a lower layer you can trust, and then this would just be extra overhead, or you don't, in which case you'll also need error correction (not just detection!) for the database file itself.
slashdev 1y ago
It is good that it doesn't give you an option. I don't want some app on my phone telling me its database is corrupt, I want it to always load back to the last good state and I'll handle any missing data myself. The checksums are not going to fail unless there was disk corruption or a partial write. In the former, thank your lucky stars it was in the WAL file and you just lose some data but have a functioning database still. In the latter, you didn't fsync, so it couldn't have been that important. If you care about not losing data, you need to fsync on every transaction commit. If you don't care enough to do that, why do you care about checksums, it's missing the point.