Repairing a SQLite database

Category: LiveConfig
Created: 2022-05-17

This article describes what to do if you find the error message Database exception: database disk image is malformed in your LiveConfig log (/var/log/liveconfig/liveconfig.log).

Reasons for corrupted database

There are numerous reasons for a corrupted database file, SQLite even provides a separate article on that. Regarding LiveConfig, the most common reasons are

• out of disk space while writing to database
• restoring a “hot copy” from a backup (not dumped with lcdbbackup, but just copied the database file)
• deletion or incorrect restore of the database journal file (liveconfig.db-journal)

To correctly and safely backup the LiveConfig database, you need to run /usr/lib/liveconfig/lcdbbackup (installed together with LiveConfig, see its man page for usage details).

Repairing the database

1. Quit LiveConfig, so that the database is not accessed any more:

   root@srv:~# systemctl stop liveconfig
   

2. Make a backup of the database file

   root@srv:~# cp -av /var/lib/liveconfig/liveconfig.db /var/lib/liveconfig/liveconfig.db.BACKUP
   

3. Open the database with SQLite (you propably need to install the package sqlite3 first)

   root@srv:~# sqlite3 /var/lib/liveconfig/liveconfig.db
   SQLite version 3.27.2 2019-02-25 16:06:06
   Enter ".help" for usage hints.
   sqlite>
   

4. Now run the SQL command PRAGMA integrity_check;

   If everything is ok, then it returns just ok. You may leave SQLite with the command .quit.

5. If the integrity check from step 4 returned any error, create a SQL dump using the following commands:

   sqlite> .output /var/lib/liveconfig/liveconfig.db.sql
   sqlite> .dump
   sqlite> .quit
   

   Then rename the corrupt database file, and import the SQL dump:

   root@srv:~# mv /var/lib/liveconfig/liveconfig.db /var/lib/liveconfig/liveconfig.db.corrupt
   root@srv:~# mv /var/lib/liveconfig/liveconfig.db-journal /var/lib/liveconfig/liveconfig.db-journal.corrupt
   root@srv:~# sqlite3 /var/lib/liveconfig/liveconfig.db </var/lib/liveconfig/liveconfig.db.sql
   root@srv:~# chown liveconfig:liveconfig /var/lib/liveconfig/liveconfig.db*
   root@srv:~# chmod 600 /var/lib/liveconfig/liveconfig.db*
   

This should solve the problem. Start LiveConfig and check if everything is ok again.

root@srv:~# systemctl start liveconfig