Selecting Tools->Network should show current results for hosts that have done DNS lookups.
OS: Raspbian 9 (stretch)
Hardware: Raspberry Pi 3B+
Tools->Network shows hosts but it does not appear to have been updated for several months. I have verified that DNS for those hosts is pointing directly to the pi-hole server (not a router, etc.) and DNS resolution is working.
Your long term database appears to be corrupted:
[2020-11-24 12:38:00.055 26875/T26879] SQLite3 message: database corruption at line 66272 of [3bfa9cc97d] (11)
[2020-11-24 12:38:00.056 26875/T26879] SQLite3 message: statement aborts at 3: [SELECT MAX(ID) FROM queries] database disk image is malformed (11)
[2020-11-24 12:38:00.056 26875/T26879] Encountered step error in get_max_query_ID(): database disk image is malformed
[2020-11-24 12:38:00.056 26875/T26879] Encountered error while trying to close database: database is locked
[2020-11-24 12:38:00.056 26875/T26879] SQLite3 message: database corruption at line 66272 of [3bfa9cc97d] (11)
[2020-11-24 12:38:00.056 26875/T26879] SQLite3 message: statement aborts at 11: [INSERT INTO queries VALUES (NULL,?,?,?,?,?,?,?)] database disk image is malformed (11)
[2020-11-24 12:38:00.056 26875/T26879] Encountered error while trying to store queries in long-term database: database disk image is malformed
[2020-11-24 12:38:00.056 26875/T26879] SQLite3 message: statement aborts at 1: [END TRANSACTION] cannot commit - no transaction is active (1)
[2020-11-24 12:38:00.056 26875/T26879] ERROR: SQL query "END TRANSACTION" failed: SQL logic error
[2020-11-24 12:38:00.056 26875/T26879] END TRANSACTION failed when trying to store queries to long-term database
[2020-11-24 12:38:00.062 26875/T26879] db_query_int("SELECT id FROM network WHERE hwaddr = '9c:3d:cf:fd:28:9a';") called but database is not available!
[2020-11-24 12:38:00.063 26875/T26879] db_query_int("SELECT network_id FROM network_addresses WHERE ip = '192.168.0.46' AND lastSeen > (cast(strftime('%s', 'now') as int)-86400) ORDER BY lastSeen DESC LIMIT 1;") called but database is not available!
[2020-11-24 12:38:00.063 26875/T26879] db_query_int("SELECT id FROM network WHERE hwaddr = 'ip-192.168.0.46';") called but database is not available!
Well rats.
Ok, how should I recreate it? I assume removing the db itself will do, can you point me in the right direction?
Thanks for the quick response.
The commands below will stop FTL, move the corrupt database to a different file name, and start FTL. This will create a new database and the old one will still be there should you want to investigate it.
sudo service pihole-FTL stop
sudo mv /etc/pihole/pihole-FTL.db /etc/pihole/pihole-FTL-old.db
sudo service pihole-FTL start
Thanks, that did the trick, the data is now correct.
It looks like I can probably re-create the database because I was able to dump it, but other than displaying the stats, is that used for anything else? If not, I'm content to just use the new one.
Also, does the database periodically prune itself? This one was about 469MB and there were roughly 6.8 million entries in it.
That's pretty much it. It's your long term history of queries.
Yes, per your settings for number of days to retain data. The default is 365, but this can be as low as 1 day while still keeping the database active.
MAXDBDAYS
This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.