5 ms·

This is the main point that the OP misses: even if the newer portion of the WAL file isn't corrupted, its content cannot be used in any way because doing so wou

This is the main point that the OP misses: even if the newer portion of the WAL file isn't corrupted, its content cannot be used in any way because doing so would require the lost transactions from the corrupted block. The chained checksums are a feature, not gratuitous fragility.

AlotOfReading 1y ago
Sqlite could attempt to recover the detected errors though and not lose the transactions.
- hobs 1y ago
  And not get in an infinite loop, and not harm the startup time of the process inordinately, and... This is just basically how a WAL works, if you have an inconsistent state the transaction is rolled back - at that point you need to redo your work.
  - HelloNurse 1y ago
    The WAL can be replayed up to the first corrupted block, period. It's like a rockslide destroying a road: you can progress up to the gap or rubble heap, but you cannot expect to use the unharmed road past the insurmountable obstacle.
- daneel_w 1y ago
  The WAL was corrupted, the actual data is lost. There's no parity. You're suggesting that sqlite should somehow recreate the data from nothing.
  - avinassh 1y ago
    > The WAL was corrupted, the actual data is lost. There's no parity. You're suggesting that sqlite should somehow recreate the data from nothing. Not all frames in the WAL are important. Sure, recovery may be impossible in some cases, but not all checksum failures are impossible to recover from.
    - pests 1y ago
      > but not all checksum failures are impossible to recover from Which failures are possible to recover from?
      - dec0dedab0de 1y ago
        what if the corruption only affected the stored checksum, but not the data itself?
        kstrauser 1y ago
        And you would detect this how?
        deleted 1y ago
        [deleted]
        daneel_w 1y ago
        ...then the checksum can't clear the associated operation. Same result.
      - jandrewrogers 1y ago
        There are two examples I know of that require no additional data: First, force a re-read of the corrupted page from disk. A significant fraction of data corruption occurs while it is being moved between storage and memory due to weak error detection in that part of the system. A clean read the second time would indicate this is what happened. Second, do a brute-force search for single or double bit flips. This involves systematically flipping every bit in the corrupted page, recomputing the checksum, and seeing if corruption is detected.
        daneel_w 1y ago
        > A significant fraction of data corruption occurs while it is being moved between storage and memory Surely you mean on the memory bus specifically? SATA and PCIe both have some error correction methods for securing transfers between storage and host controller. I'm not sure about old parallel ATA. While I understand it can happen under conditions similar to non-ECC RAM being corrupted, I don't think I've ever heard or read about a case where a storage device randomly returned erroneous data, short of a legitimate hardware error.
  - AlotOfReading 1y ago
    I was assuming sqlite did the sane thing and used a CRC. CRCs have (limited) error correction capabilities that you can use to fix 1-2 bit errors in most circumstances, but apparently sqlite uses a Fletcher variant and gives up that ability (+ long message length error detection) for negligible performance gains on modern (even embedded) CPUs.
    - lxgr 1y ago
      The ability to... correct 1-2 bit errors? Is that even a realistic failure mode on common hardware? CRCs as used in SQLite are not intended to detect data corruption due to bit rot, and are certainly not ECCs.
      - AlotOfReading 1y ago
        Yes, it's a pretty important error case with storage devices. It's so common that modern storage devices and filesystems include their own protections against it. Your system may or may not have these and bit flips may happen after that point, so WAL redundancy wouldn't be out of place. Sure, the benefits to the incomplete write use case are limited, but there's basically no reason to ever use a fletcher these days. It's also worth mentioning that the VFS checksums are explicitly documented as guarding against storage device bitrot and use the same fletcher algorithm.
        lxgr 1y ago
        It would be absolutely out of place if your lower layers already provided it, and not enough if they didn't (since you'd then also need checksums on the actual database file, which SQLite does not provide – all writes happen again there!)
        AlotOfReading 1y ago
        sqlite does actually provide database checksums, via the vfs extension I mentioned previously. There's no harm to having redundant checksums and it's not truly redundant for small messages. It's pretty common for systems not to have lower level checksumming either. Lots of people are still running NTFS/EXT4 on hardware that doesn't do granular checksums or protect data in transit. Of course this is all a moot point because sqlite does WAL checksums, it just does them with an obsolete algorithm.

AlotOfReading 1y ago

Sqlite could attempt to recover the detected errors though and not lose the transactions.

hobs 1y ago
And not get in an infinite loop, and not harm the startup time of the process inordinately, and... This is just basically how a WAL works, if you have an inconsistent state the transaction is rolled back - at that point you need to redo your work.
- HelloNurse 1y ago
  The WAL can be replayed up to the first corrupted block, period. It's like a rockslide destroying a road: you can progress up to the gap or rubble heap, but you cannot expect to use the unharmed road past the insurmountable obstacle.
daneel_w 1y ago
The WAL was corrupted, the actual data is lost. There's no parity. You're suggesting that sqlite should somehow recreate the data from nothing.
- avinassh 1y ago
  > The WAL was corrupted, the actual data is lost. There's no parity. You're suggesting that sqlite should somehow recreate the data from nothing. Not all frames in the WAL are important. Sure, recovery may be impossible in some cases, but not all checksum failures are impossible to recover from.
  - pests 1y ago
    > but not all checksum failures are impossible to recover from Which failures are possible to recover from?
    - dec0dedab0de 1y ago
      what if the corruption only affected the stored checksum, but not the data itself?
      - kstrauser 1y ago
        And you would detect this how?
        deleted 1y ago
        [deleted]
      - daneel_w 1y ago
        ...then the checksum can't clear the associated operation. Same result.
    - jandrewrogers 1y ago
      There are two examples I know of that require no additional data: First, force a re-read of the corrupted page from disk. A significant fraction of data corruption occurs while it is being moved between storage and memory due to weak error detection in that part of the system. A clean read the second time would indicate this is what happened. Second, do a brute-force search for single or double bit flips. This involves systematically flipping every bit in the corrupted page, recomputing the checksum, and seeing if corruption is detected.
      - daneel_w 1y ago
        > A significant fraction of data corruption occurs while it is being moved between storage and memory Surely you mean on the memory bus specifically? SATA and PCIe both have some error correction methods for securing transfers between storage and host controller. I'm not sure about old parallel ATA. While I understand it can happen under conditions similar to non-ECC RAM being corrupted, I don't think I've ever heard or read about a case where a storage device randomly returned erroneous data, short of a legitimate hardware error.
- AlotOfReading 1y ago
  I was assuming sqlite did the sane thing and used a CRC. CRCs have (limited) error correction capabilities that you can use to fix 1-2 bit errors in most circumstances, but apparently sqlite uses a Fletcher variant and gives up that ability (+ long message length error detection) for negligible performance gains on modern (even embedded) CPUs.
  - lxgr 1y ago
    The ability to... correct 1-2 bit errors? Is that even a realistic failure mode on common hardware? CRCs as used in SQLite are not intended to detect data corruption due to bit rot, and are certainly not ECCs.
    - AlotOfReading 1y ago
      Yes, it's a pretty important error case with storage devices. It's so common that modern storage devices and filesystems include their own protections against it. Your system may or may not have these and bit flips may happen after that point, so WAL redundancy wouldn't be out of place. Sure, the benefits to the incomplete write use case are limited, but there's basically no reason to ever use a fletcher these days. It's also worth mentioning that the VFS checksums are explicitly documented as guarding against storage device bitrot and use the same fletcher algorithm.
      - lxgr 1y ago
        It would be absolutely out of place if your lower layers already provided it, and not enough if they didn't (since you'd then also need checksums on the actual database file, which SQLite does not provide – all writes happen again there!)
        AlotOfReading 1y ago
        sqlite does actually provide database checksums, via the vfs extension I mentioned previously. There's no harm to having redundant checksums and it's not truly redundant for small messages. It's pretty common for systems not to have lower level checksumming either. Lots of people are still running NTFS/EXT4 on hardware that doesn't do granular checksums or protect data in transit. Of course this is all a moot point because sqlite does WAL checksums, it just does them with an obsolete algorithm.