Captive Portal: how to recover a broken database

[daudo]

From time to time, the captive portal stops to work and the log show this:

Code Select Expand

Traceback (most recent call last): File "/usr/local/opnsense/scripts/OPNsense/CaptivePortal/cp-background-process.py", line 207, in main bgprocess.db.cleanup_sessions() File "/usr/local/opnsense/scripts/OPNsense/CaptivePortal/lib/db.py", line 403, in cleanup_sessions """) sqlite3.DatabaseError: database disk image is malformed

This seems to have been an issue occurring for a long time, see https://forum.opnsense.org/index.php?topic=12843.0 for example.

The solution laid out there is pretty brute force, however. Instead of deleting the database altogether and losing all session and state information, the right way to do it is to simply repair the database:

1. stop the captive portal
2. move away the broken database

Code Select Expand

mv /var/captiveportal/captiveportal.sqlite /var/captiveportal/captiveportalbad.sqlite
3. recover the database

Code Select Expand

sqlite3 /var/captiveportal/captiveportalbad.sqlite ".recover" | sqlite3 /var/captiveportal/captiveportal.sqlite