SQLite Forum

WAL recovery error
Login

WAL recovery error

(1) By DawsonV5 on 2020-12-17 17:56:46 [link] [source]

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 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 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

(2) By Simon Slavin (slavin) on 2020-12-21 05:54:30 in reply to 1 [link] [source]

A number of things here.

SQLite does not have separate ways of reporting errors and warnings. The message you received is a warning, not an error. I think 'WARN/0' in it is for that reason but I don't use tcl so I'm not sure. So while your software could report this to the user, or put it in a log file, there's no reason for the program to quit after reporting the warning unless you are cautious about anything unusual.

The warning that was reported – recovering frames from a WAL file – does normally happen because a connection wasn't properly closed by one of your programs. However, this may have happened not because your code doesn't close the file, but because while the database was open the program crashed, or the computer lost power, or the connection with the storage device was lost. In other words, your code may be fine and you just had a hardware fault when a program was last run. If it happens frequently, you may have bad code. If it happened just once, it was probably a hardware problem.

The part of SQLite that reports that warning should correct that warning, making changes to the database file and saving them. In other words, if you run a program twice in a row, and it reports that warning both times, something is wrong. A frequent cause for this is that your program doesn't have change privileges it needs to write to the database file, or to the journal file, to correct the problem.

What it does, when complete, should yield an uncorrupted database file. In other words you should not worry about the database being corrupted just because you got that warning.

The journal mode is stored in the database. If you have had any program set the journal mode to WAL at any point, this is stored in the database and all connections which open the database will know this. There's no need to set it repeatedly, just set it once when the database is first created. The synchronous mode of NORMAL is default. If your code has never set that mode, you don't need to set it.

(3) By Keith Medcalf (kmedcalf) on 2020-12-21 07:46:19 in reply to 2 [source]

The synchronous mode of NORMAL is default. If your code has never set that mode, you don't need to set it.

The default for synchronous, unless you changed it at compile time, is FULL.

There are compile time options to set the default synchronous mode, and to allow the the synchronous mode be reduced to NORMAL when the journal mode is WAL, however the default option is FULL, not NORMAL.

(4) By Keith Medcalf (kmedcalf) on 2020-12-21 07:53:04 in reply to 1 [link] [source]

This message indicates that there were frames in the WAL file when a database connection was opened that had not yet been written to the main database file. It is not an error condition. It is merely apprising you of the extant circumstance.

There can be many reasons for this. Perhaps something was holding open a read snapshot so that the frames could not be moved to the main database when the connection on which those changes were made was closed. This condition subsequently cleared (the reader released its lock) and when a new connection to the database was opened it "noticed" that WAL contained frames that could be now moved to the main database, so it did so.

This is normal.

Of course, I have no clue from where this message arose, nor do you say from whence it levitated. How did you happen to come across at (that is, from where did it arise)?

(5) By Richard Hipp (drh) on 2020-12-21 13:01:29 in reply to 1 [link] [source]

In the script that is inserting and updating the database, are you closing the database connection using something like:

SqDbHandle close

Prior to exiting?