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]

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]

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

Code: [Select]

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