SQLITE WAL recovery error question

[Jeff Dawson]

When using sqlite has anyone come across this type of error?

[pd :open:WARN/0: FTsqlite_adt:12/17/2020 10:44:46] Error returned from sqlite: (0) Recovered 724846 frames from WAL file /cis/cis6.2/integrator/sqlLiteDbs/ADT/EpicADT.db-wal

 

I’ve gone through searching the web and came across a few of these entries

“This is because the previous process to access the database did not
call sqlite3_close() prior to exiting, and so the WAL file was not
cleaned up properly.”

We have a script that is inserting or updating ADT to a sqlite table

# assign db name
sqlite SqDbHandle $dbFile

# set write ahead logging
SqDbHandle eval {PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;}

#SqDbHandle close
if {[catch {SqDbHandle close} err]} {
echo “Error: Error closing the database: $err”
}

 

Then we have another tcl script that is used on other various interfaces that is only doing a “read” of the sqlite ADT database which is using the same sqlite commands and close statement as above.   Looking for any type of suggestions to try and narrow down why these WAL recovery errors are being thrown.

Jeff

Topic

[Charlie Bursell]

If other processes are trying to access the same DB you may be bumping heads.  Take a look here:

https://wiki.tcl-lang.org/page/Check+if+SQLite+Databases+Are+Locked

 

[Jeff Dawson]

Thanks Charlie,

I read through the link and it looks like it’s a few years old.  I checked to see what version of sqlite is installed with the CIS version we are running, 6.2.6.1 on AIX.

SQLite version 3.7.14.1 2012-10-04 19:37:12

I thought with the addition of WAL with Sqlite that would help when many processes are only reading the database.  I’m still reviewing some of aspects of WAL, not for sure if the culprit is since the writes (inserts/updates) are pretty frequent since its and ADT feed.

“SQLite 3.7.0 added a new journal mode called Write Ahead Locking that supports concurrent reading while writing.”

Jeff

[Charlie Bursell]

I have not used the WAL Journal method.  Is there a reason you use that rather than the default Journal mode of DELETE?

The best I can understand is if you are using the Journal mode= WAL the Journal file is automatically deleted with a COMMIT, I’m not sure this happens if you simply close the database.

Unless you really need WAL journaling perhaps it would be best to just use the default.  Otherwise you can use some associated WAL functions to better control it.

You may have seen it but here is the docs on WAL.

https://www.sqlite.org/wal.html

Good luck with it.

[Author]

Replies