pihole-FTL 5.8.1 stopping nightly

[eblake]

Expected Behaviour:

Ever since I used 'pihole -up' to get to FTL 5.8.1 last week, I've been waking up to the FTL service not running, and have to restart it. I'm using a RPi 4 (Linux raspberrypi 4.19.118-v7l+ #1311 SMP Mon Apr 27 14:26:42 BST 2020 armv7l GNU/Linux), and have SSH access to grab whatever logs you may need to help me further diagnose it. I'm not yet sure if it is the OOM killer or something else.

Actual Behaviour:

The FTL service is dying within 24 hours. While browsing /var/log/pihole-FTL.log right after restarting the FTL service, I see the following lines that look suspicious, but moving /etc/pihole/pihole-FTL.db out of the way before another restart did not resolve it:

[2021-09-10 05:26:45.433 11602/T11606] Compiled 8 whitelist and 1 blacklist regex filters for 0 clients in 2.8 msec
[2021-09-10 05:26:45.443 11602M] New upstream server: 2620:119:53::53:53 (0/1024)
[2021-09-10 05:26:45.728 11602M] New upstream server: 208.67.220.220:53 (1/1024)
[2021-09-10 05:26:52.820 11602M] New upstream server: 208.67.222.222:53 (2/1024)
[2021-09-10 05:27:00.454 11602/T11606] SQLite3 message: table queries has 7 columns but 8 values were supplied in "INSERT INTO queries VALUES (NULL,?,?,?,?,?,?,?)" (1)
[2021-09-10 05:27:00.454 11602/T11606] ERROR: Storing queries in long-term database failed: SQL logic error

[2021-09-10 05:27:00.454 11602/T11606]        Keeping queries in memory for later new attempt
[2021-09-10 05:27:00.456 11602/T11606] SQLite3 message: statement aborts at 1: [BEGIN TRANSACTION IMMEDIATE] cannot start a transaction within a transaction (1)
[2021-09-10 05:27:00.457 11602/T11606] ERROR: SQL query "BEGIN TRANSACTION IMMEDIATE" failed: SQL logic error

Debug Token:

https://tricorder.pi-hole.net/T0b5jTY2/

[Bucking_Horn]

@eblake

SQLite3 message: table queries has 7 columns but 8 values were supplied in "INSERT INTO queries VALUES (NULL,?,?,?,?,?,?,?)" (1)

@eblake

moving /etc/pihole/pihole-FTL.db out of the way before another restart did not resolve it:

That's unexpected, as auto-creation of a new database should indeed have resulted in the proper table structure.

Run on your Pi-hole RPi4 host, what's the output of:

sqlite3 -header /etc/pihole/pihole-FTL.db "PRAGMA table_info(queries);"

[eblake]

$ sqlite3 -header /etc/pihole/pihole-FTL.db "PRAGMA table_info(queries);"
cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|0||1
1|timestamp|INTEGER|1||0
2|type|INTEGER|1||0
3|status|INTEGER|1||0
4|domain|TEXT|1||0
5|client|TEXT|1||0
6|forward|TEXT|0||0
7|additional_info|TEXT|0||0

Hmm - I may have been reading from the wrong portion of the log, because my pihole stayed up for 3 days since I originally posted, and when I stopped and restarted the service to compose this reply, I made sure to look only for timestamps from today, where I no longer see that message in the logs. So regenerating the db does appear to have worked, and it may just be cockpit error for me filing this report with an incorrect portion of the log.