[FTL goes offline intermittently throughout the week; new behavior as of latest update. I have to send this command to restore pihole status back to 'active':. When problem occurs, 'FTL Offline' and 'Lost connection to API' presents on the dashboard.]
Sorry about that; I had to wait for the issue to occur again (today). The pihole webservice crashed this time too, when I attempted to run the debug log while the issue was occurring. I used VNC to remote in and tried to run the next debug from the cmd line. It kept getting held up at
'discovering active DHCP servers (takes 10 seconds)'.
I have a eufy security app, and its push notifications are always held up immediately when the pihole goes offline. When I ran this command today [pihole restartdns] after the first failed debug attempt, all the (58) push notifications flooded to my devices all within seconds; the sound of 58 notifications coming in for several seconds was awesome!
I changed the DNS setting back to 'cloudfare', and suddenly the debug logging got unstuck from the step described above. Here's the newest log, though the issue was not present when the log was run: https://tricorder.pi-hole.net/3kbh3i7flv
As the pihole had to be rebooted to get the debug to work, I think I'll have to wait until the issue occurs again to try again. Let me know if any other details can be gathered beforehand.
Your debug log suggests your pihole-FTL.db is either corrupted or missing completely, e.g.:
*** [ DIAGNOSING ]: contents of /var/log/lighttpd
-rw-r--r-- 1 www-data www-data 10276 Jun 18 20:30 /var/log/lighttpd/error.log
-----head of error.log------
2021-06-13 00:00:13: (server.c.1759) logfiles cycled UID = 0 PID = 22883
2021-06-13 22:18:38: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning: SQLite3::query(): Unable to prepare statement: 1, no such table: message in /var/www/html/admin/api_db.php on line 402
*** [ DIAGNOSING ]: contents of /var/log
-rw-r--r-- 1 pihole pihole 83582 Jun 18 20:30 /var/log/pihole-FTL.log
-----tail of pihole-FTL.log------
[2021-06-18 20:30:48.477 14215/T14219] SQLite3 message: no such table: message in "DELETE FROM message;" (1)
[2021-06-18 20:30:49.138 14215M] SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
[2021-06-18 20:30:49.139 14215M] SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
You could try to copy pihole-FTL.db to a safe location (e.g. to your home dir) for later recovery attempts (your blocklist configuration will stay intact, as that resides ingravity.db):
sudo service pihole-FTL stop
sudo mv /etc/pihole/pihole-FTL.db ~
sudo service pihole-FTL start
If you can do without your past query history, you could instead delete pihole-FTL.db.
A new query database will be created automatically.
Thanks a lot for your help and time looking into this! After stopping FTL, I couldn't see the .db file, and I'm unsure if it was even present beforehand. Since stopping and starting the pihole-FTL service, it looks like I have a new FTL.db file and my query logs were immediately working better. I'll continue to look out for new instances of FTL going offline; if it happens again, I'll try to get another debug log and share.
I'll also continue to monitor how my home security system behaves moving forward; I may be forced to permit device-metrics-us.amazon.com and googleadservices.com in order to preserve some functionality.