Accessing Tools-> Network and any Long term query fails and results in errors

RoninTech · September 9, 2022, 6:49am

Expected Behaviour:

I believed I had successfully installed pi-hole into a docker on my 8GB Raspberry pi 4 running 64 bit Debian 11 (bullseye). It is successfully providing ad-free DNS to my home network with the aid of unbound, also in a docker. Once I had everything up and working I stopped the pihole docker and deleted the pihole-FTL.db and also used the Settings -> Flush Logs and Flush Network Table buttons. I am not 100% sure but I believe that the issues detailed below started after that.

Two scenarios are having issues:

Accessing the Tools -> Network page should provide a list of devices.
Attempting a query with any of the Long term Data pages should return the appropriate query results.

Looking for advice on how to get these web gui actions working again.

Actual Behaviour:

Accessing Tools -> Network page
An error window pops up with the message:

An unknown error occurred while loading the data.

A "tail -f /var/log/lighttpd/error-pihole.log" while accessing the page shows:

2022-09-09 13:21:41: mod_fastcgi.c.487) FastCGI-stderr:PHP Warning:  SQLite3::query(): Unable to prepare statement: 1, no such table: network in /var/www/html/admin/api_db.php on line 32
2022-09-09 13:21:41: mod_fastcgi.c.487) FastCGI-stderr:PHP Fatal error:  Uncaught Error: Call to a member function finalize() on bool in /var/www/html/admin/api_db.php:55
2022-09-09 13:21:41: mod_fastcgi.c.487) FastCGI-stderr:Stack trace:
2022-09-09 13:21:41: mod_fastcgi.c.487) FastCGI-stderr:#0 {main}
2022-09-09 13:21:41: mod_fastcgi.c.487) FastCGI-stderr:  thrown in /var/www/html/admin/api_db.php on line 55
2022-09-09 13:21:41: mod_fastcgi.c.487) 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 380
2022-09-09 13:21:47: mod_fastcgi.c.487) 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 380

Attempting a query with any of the Long term Data pages:
An error window pops up with the message:

An unknown error occurred while loading the data. Check the server's log files (/var/log/lighttpd/error-pihole.log) for details. You may need to increase PHP memory limit. You can find more info in pi-hole's FAQ.....

A "tail -f /var/log/lighttpd/error-pihole.log" while accessing the page shows:

2022-09-09 13:31:47: mod_fastcgi.c.487) 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 380
2022-09-09 13:31:50: mod_fastcgi.c.487) FastCGI-stderr:PHP Warning:  SQLite3::prepare(): Unable to prepare statement: 1, no such table: query_storage in /var/www/html/admin/api_db.php on line 93
2022-09-09 13:31:50: mod_fastcgi.c.487) FastCGI-stderr:PHP Fatal error:  Uncaught Error: Call to a member function bindValue() on bool in /var/www/html/admin/api_db.php:94
2022-09-09 13:31:50: mod_fastcgi.c.487) FastCGI-stderr:Stack trace:
2022-09-09 13:31:50: mod_fastcgi.c.487) FastCGI-stderr:#0 {main}
2022-09-09 13:31:50: mod_fastcgi.c.487) FastCGI-stderr:  thrown in /var/www/html/admin/api_db.php on line 94
2022-09-09 13:31:55: mod_fastcgi.c.487) 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 380

As mentioned in the FAQ, I created /var/www/html/.user.ini and separately tried the following memory limits with no observed change (still fails):

memory_limit = 256M

memory_limit = 512M

Debug Token:

https://tricorder.pi-hole.net/8Q8b8c63/

Bucking_Horn · September 9, 2022, 7:27am

Your debug log shows pihole-FTL.db to be zero-sized file:

*** [ DIAGNOSING ]: Pi-hole FTL Query Database
-rw-rw-r-- 1 pihole pihole 0 Sep  9 10:49 /etc/pihole/pihole-FTL.db

This seems certainly related to:

How excatly did you do that?
Please provide the exact command.

In addition, your observation may also be attributable to your following setting:

*** [ DIAGNOSING ]: contents of /etc/pihole

-rw-rw-r-- 1 pihole root 330 Sep  9 10:47 /etc/pihole/pihole-FTL.conf
   LOCAL_IPV4=0.0.0.0
   RATE_LIMIT=1000/60
   DBINTERVAL=60.0

That last option instructs Pi-hole to write to its database once every 60 minutes.
So if you'd run your Long Term queries within an hour after deleting the database, the database would not have been populated yet.

Still, those messages about missing database tables are somewhat unexpected.
But then, I wouldn't know off the top of my head if Pi-hole would recreate tables for the database that you manually deleted only on its first write attempt.

Also, unrelated to your observation, but you should set your Pi-hole's FTLCONF_LOCAL_IPV4 address for your container.

RoninTech · September 9, 2022, 8:09am

Thanks for the quick reply @Bucking_Horn!

I stopped the docker from my Portainer web GUI and from a console on the host machine I removed the /etc/pihole/pihole-FTL.db file which exists in the docker mapped volume on the host machine.

volumes:
  - '/home/me/dockers/pihole/etc-pihole:/etc/pihole'

So the command was 'rm -f /home/me/dockers/pihole/pihole-FTL.db'

My Raspberry Pi runs off an SSD drive, so I followed the instructions here and added:

DBINTERVAL=60.0

To /etc/pihole.pihole-FTL.conf.

I've been up and running for a lot longer than 60 mins so something should be in there by now if that was the issue. /etc/pihole/pihole-FTL.db is still sitting at zero bytes. Any ideas how to get the FTL DB populating again?

Also, unrelated to your observation, but you should set your Pi-hole's FTLCONF_LOCAL_IPV4 address for your container.

Thanks for this. I must have looked at a dozen guides over the last couple of days and don't recall any of them using this env var. I will definitely add it. The README.md you linked mentions:

Set to your server's LAN IP, used by web block modes and lighttpd bind address.

My raspberry Pi's eth0 IP is 192.168.1.7 and the pihole docker's private IP is 172.20.0.6. For this env var I should use the 192.168.1.7 address? In my docker compose file I have "ipv4_address: 172.20.0.6" in the pihole section so I'm not 100% sure which IP to use for that var.

Thanks again!

RoninTech · September 10, 2022, 2:31am

Looks like there might be a bug with using DBINTERVAL=60.0 and not having an FTL db populated. I had to nuke the docker, set DBINTERVAL=0.0, start the docker and get the FTL db populated then stopped the docker, set DBINTERVAL=60.0 and bring it back up. Now everything is happy again and the FTL db appears to be updating every hour.

system · October 1, 2022, 2:31am

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.