litestream icon indicating copy to clipboard operation
litestream copied to clipboard

Restored database fails integrity checks

Open kalafut opened this issue 10 months ago • 2 comments

I'm using version v0.3.13 in a mostly vanilla fashion: single server replicating to S3. The only modifications have been to the retention setup:

retention: 168h
snapshot-interval: 24h

Things had been working fine, including test restores. Even now replication is proceeding as normal. But about a week ago, I did a test restore and saw some database integrity errors. When I ran pragma integrity_check I saw stuff like:

*** in database main ***
Tree 88 page 88 cell 46: Rowid 5452 out of order
Tree 88 page 35: btreeInitPage() returns error code 11
Tree 88 page 88 cell 43: Rowid 5436 out of order
Tree 88 page 88 cell 39: Rowid 5402 out of order
Tree 88 page 88 cell 38: Rowid 5393 out of order
Tree 88 page 88 cell 37: Rowid 5384 out of order
...
Tree 88 page 88 cell 7: Rowid 5233 out of order
Tree 88 page 91 cell 1: 2nd reference to page 35
row 22 missing from index idx_objects_user_id_modified_at
row 34 missing from index sqlite_autoindex_objects_1
row 35 missing from index sqlite_autoindex_objects_1
row 36 missing from index sqlite_autoindex_objects_1
...
row 49 missing from index idx_objects_user_id_modified_at
row 49 missing from index sqlite_autoindex_objects_1
row 50 missing from index idx_objects_user_id_modified_at
row 50 missing from index sqlite_autoindex_objects_1

I restarted the Litestream service and just let things run, thinking maybe it was a glitchy WAL and would be flushed out (still not great, but...). A week later, a freshly restored file still exhibits the same issues. When I ssh into the server and run pragma integrity_check on the actual database, it always returns OK.

I'm sure if I just wiped/vacuumed/restarted everything, thing would probably again run fine, but that's not answering any questions. Are there any diagnostics you'd like me to run on this bad state?

kalafut avatar Apr 22 '24 16:04 kalafut