SQLite Forum

Entries seen in WAL missing when reading DB

Entries seen in WAL missing when reading DB

(1) By anonymous on 2021-07-28 05:23:08 [link] [source]


It appears that a page (page 293) is not being read by sqlite.

Entries from that page in the WAL file do not show up when reading the database (via query)

Example of Issue

The Wal header and frame which does not appear is shown below

WAL Header:

Signature: 931071618
Version: 3007000
PageSize: 4096
SequenceNumber: 14
Salt1: 3134934172
Salt2: 3456589801
CheckSum1: 2134105805
Checksum2: 779796032

Frames which are suspected to be missing

Frame Header:

DBPageNumber: 293
commitRecords: 8232
Salt1: 3134934172
Salt2: 3456589801
Checksum1: 3224136425
Checksum2: 703062074

Frame Data

2, '2021 Jul 13 04:45:35.879', None, 'local0', 'notice', 'xxxxx', 'yyyyy', '5198', 'random log 1'
2, '2021 Jul 13 04:45:36.780', None, 'local0', 'notice', 'xxxxx', 'yyyyyy', '6313', 'random log 2'

As can be seen, the salt 1 and salt 2 values match those of the wal header and so the above frame should still be valid.

There are multiple frames (24 copies) of page 293 which are similar to the above. However, there are frames with page number 293 which have an older salt 1 value (indicating that frame has been checkpointed):

DBPageNumber: 293
commitRecords: 0
Salt1: 3134934171
Salt2: 3988219145
Checksum1: 3140164133
Checksum2: 2899453040

The entries from this frame do not appear when reading the database. The entries are expected to be there due to timestamps showing up in the database read.

Other Info

An integrity check returned no errors on the database.

I logged out the page numbers from sqlite3WalFindFrame() that sqlite was attempting to find in the wal file and none of them appear to be page 293. It seems sqlite does not even attempt to look for page 293 in the WAL file.

Any help/ideas would be appreciated.

(2) By Dan Kennedy (dan) on 2021-07-28 18:54:32 in reply to 1 [source]

Tricky to really say anything about this.

"showwal" is a tool for examining wal files. You can build it from the full source tree with "./configure && make showwal".


(3) By anonymous on 2021-07-29 05:47:15 in reply to 2 [link] [source]

Thanks for your help. Didn't know about showwal and it is a really useful tool. The entries has actually been deleted by a trigger and hence was not showing up.