Hi everyone, this is my first post here. I searched for this answer but never found.
So let me explain my case.
I have a Pi-Hole installed on my Raspberry Pi 4, because the file pihole-FTL.db is huge so I decided to have a symbolic link into an external HD (since Raspberry Pi have limited storage). So, after a kernel upgrade I had to unmount my HD temporarily and it caused my Pi-Hole to get disable. Ok so far everything normal since it could not find the symbolic link anymore. Then, after restoring the link I still could not make my DNS server to start. So I used the command $pihole -r and then it started back.
Although everything looks normal, my Long-Term Data is empty. The file pihole-FTL.db is around 400MB but when I check the pihole-FTL.log it says that 0 Queries were loaded from the file even with the file size on 400MB.
I used the command $strings pihole-FTL.db and I find a bunch of information there (my old data) but for some reason Pi-Hole is not loading it!.
-rw-rw-r-- 1 pihole root 15 Oct 8 21:16 /etc/pihole/pihole-FTL.conf
-rw-r--r-- 1 pihole pihole 184K Oct 8 22:22 /etc/pihole/pihole-FTL.db
PRIVACYLEVEL=0
I already checked all those configuration, I have been searching for this for a while, I already tried to chmod 644 pihole-FTL.log, already tried to change ownership to root:pihole, etc.
It is weird because the file has content but can't be loaded and after a while Pi-Hole starts writing on it.
It is exactly like this. Looks like it reads the file, the logs do not show any sign of error but it says it loads 0 Queries. I did a $strings pihole-FTL.db and I see my data there, it just can't read. Maybe the header or something like this was overwritten on the file?
Try this to identify if the database actually has a problem. We'll move the database to a new file name, then start with a fresh database. If the old database is the problem, it might be repairable.
Sure. Just for a Note, I removed the symbolic link and I pulled the DB from the HD (actually I removed the symbolic link as soon as I saw problems happening) and I loaded again and this is the message on the logs:
[2020-10-09 04:37:31.643 1719M] Using log file /var/log/pihole-FTL.log
[2020-10-09 04:37:31.643 1719M] ########## FTL started! ##########
[2020-10-09 04:37:31.643 1719M] FTL branch: master
[2020-10-09 04:37:31.643 1719M] FTL version: v5.2
[2020-10-09 04:37:31.643 1719M] FTL commit: dbd4a69
[2020-10-09 04:37:31.644 1719M] FTL date: 2020-08-09 22:09:43 +0100
[2020-10-09 04:37:31.644 1719M] FTL user: pihole
[2020-10-09 04:37:31.644 1719M] Compiled for armhf (compiled on CI) using arm-linux-gnueabihf-gcc (Debian 6.3.0-18) 6.3.0 20170516
[2020-10-09 04:37:31.644 1719M] Starting config file parsing (/etc/pihole/pihole-FTL.conf)
[2020-10-09 04:37:31.644 1719M] SOCKET_LISTENING: only local
[2020-10-09 04:37:31.644 1719M] AAAA_QUERY_ANALYSIS: Show AAAA queries
[2020-10-09 04:37:31.644 1719M] MAXDBDAYS: max age for stored queries is 365 days
[2020-10-09 04:37:31.644 1719M] RESOLVE_IPV6: Resolve IPv6 addresses
[2020-10-09 04:37:31.644 1719M] RESOLVE_IPV4: Resolve IPv4 addresses
[2020-10-09 04:37:31.644 1719M] DBINTERVAL: saving to DB file every minute
[2020-10-09 04:37:31.644 1719M] DBFILE: Using /etc/pihole/pihole-FTL.db
[2020-10-09 04:37:31.644 1719M] MAXLOGAGE: Importing up to 24.0 hours of log data
[2020-10-09 04:37:31.644 1719M] PRIVACYLEVEL: Set to 0
[2020-10-09 04:37:31.644 1719M] IGNORE_LOCALHOST: Show queries from localhost
[2020-10-09 04:37:31.644 1719M] BLOCKINGMODE: Null IPs for blocked domains
[2020-10-09 04:37:31.645 1719M] ANALYZE_ONLY_A_AND_AAAA: Disabled. Analyzing all queries
[2020-10-09 04:37:31.645 1719M] DBIMPORT: Importing history from database
[2020-10-09 04:37:31.645 1719M] PIDFILE: Using /run/pihole-FTL.pid
[2020-10-09 04:37:31.645 1719M] PORTFILE: Using /run/pihole-FTL.port
[2020-10-09 04:37:31.645 1719M] SOCKETFILE: Using /run/pihole/FTL.sock
[2020-10-09 04:37:31.645 1719M] SETUPVARSFILE: Using /etc/pihole/setupVars.conf
[2020-10-09 04:37:31.645 1719M] MACVENDORDB: Using /etc/pihole/macvendor.db
[2020-10-09 04:37:31.645 1719M] GRAVITYDB: Using /etc/pihole/gravity.db
[2020-10-09 04:37:31.645 1719M] PARSE_ARP_CACHE: Active
[2020-10-09 04:37:31.645 1719M] CNAME_DEEP_INSPECT: Active
[2020-10-09 04:37:31.645 1719M] DELAY_STARTUP: No delay requested.
[2020-10-09 04:37:31.645 1719M] NICE: Set process niceness to -10 (default)
[2020-10-09 04:37:31.645 1719M] BLOCK_ESNI: Enabled, blocking _esni.{blocked domain}
[2020-10-09 04:37:31.645 1719M] NAMES_FROM_NETDB: Enabled, trying to get names from network database
[2020-10-09 04:37:31.645 1719M] Finished config file parsing
[2020-10-09 04:37:31.647 1719M] Database version is 7
[2020-10-09 04:37:31.647 1719M] Database successfully initialized
[2020-10-09 04:37:31.647 1719M] Imported 0 queries from the long-term database
[2020-10-09 04:37:31.647 1719M] -> Total DNS queries: 0
[2020-10-09 04:37:31.647 1719M] -> Cached DNS queries: 0
[2020-10-09 04:37:31.647 1719M] -> Forwarded DNS queries: 0
[2020-10-09 04:37:31.647 1719M] -> Blocked DNS queries: 0
[2020-10-09 04:37:31.647 1719M] -> Unknown DNS queries: 0
[2020-10-09 04:37:31.648 1719M] -> Unique domains: 0
[2020-10-09 04:37:31.648 1719M] -> Unique clients: 0
[2020-10-09 04:37:31.648 1719M] -> Known forward destinations: 0
[2020-10-09 04:37:31.648 1719M] Successfully accessed setupVars.conf
[2020-10-09 04:37:31.654 1721M] PID of FTL process: 1721
[2020-10-09 04:37:31.654 1721/T1722] Listening on port 4711 for incoming IPv4 telnet connections
[2020-10-09 04:37:31.655 1721/T1724] Listening on Unix socket
[2020-10-09 04:37:31.655 1721/T1723] Listening on port 4711 for incoming IPv6 telnet connections
[2020-10-09 04:37:31.655 1721M] Reloading DNS cache
[2020-10-09 04:37:31.656 1721M] Blocking status is enabled
[2020-10-09 04:37:31.712 1721M] INFO: No regex blacklist entries found
[2020-10-09 04:37:31.712 1721M] INFO: No regex whitelist entries found
[2020-10-09 04:37:31.712 1721M] Compiled 0 whitelist and 0 blacklist regex filters for 0 clients in 0.9 msec
[2020-10-09 04:37:32.181 1721M] New upstream server: 8.8.4.4 (0/1024)
[2020-10-09 04:37:35.565 1721M] Reloading DNS cache
[2020-10-09 04:37:35.566 1721M] Blocking status is enabled
[2020-10-09 04:37:35.617 1721M] SQLite3 message: file unlinked while open: /etc/pihole/gravity.db (28)
[2020-10-09 04:37:35.622 1721M] INFO: No regex blacklist entries found
[2020-10-09 04:37:35.622 1721M] INFO: No regex whitelist entries found
[2020-10-09 04:37:35.623 1721M] Compiled 0 whitelist and 0 blacklist regex filters for 3 clients in 1.6 msec
[2020-10-09 04:40:48.887 1721M] Resizing "/FTL-strings" from 4096 to 8192
So, doing what you asked me I get this part different meaning that the DB was not found and was created:
[2020-10-09 04:42:39.114 2100M] No database file found, creating new (empty) database
[2020-10-09 04:42:39.233 2100M] Database version is 1
[2020-10-09 04:42:39.233 2100M] Updating long-term database to version 2
[2020-10-09 04:42:39.318 2100M] Updating long-term database to version 3
[2020-10-09 04:42:39.360 2100M] Updating long-term database to version 4
[2020-10-09 04:42:39.375 2100M] Updating long-term database to version 5
[2020-10-09 04:42:39.400 2100M] Updating long-term database to version 6
[2020-10-09 04:42:39.436 2100M] Updating long-term database to version 7
[2020-10-09 04:42:39.475 2100M] Database successfully initialized
I really have no clue what is going on. Thanks for your help so far.
Let's check just how old your older database is.
Assuming you've copied your backup db to /etc/pihole/pihole-FTL-old.db as suggested by jfb, what's the result of:
sqlite3 /etc/pihole/pihole-FTL-old.db "select * from ftl;"
Ok, that's the current version, so you shouldn't have any problems reading that database.
I guess Pi-hole created a new db when it couldn't access your unmounted one, probably disregarding your symlink from then on altogether.
I'd also guess when you copied your database back to Pi-hole's folder, it got accessed and read alright:
Note that the dashboard only shows the data for the past 24 hours.
If the backup db only contains data older than a day, if would of course report 0 entries.
We should be able to verify this by using about the same steps from jfb above:
sudo service pihole-FTL stop
sudo mv /etc/pihole/pihole-FTL.db /etc/pihole/pihole-FTL.current.db
sudo mv /mnt/raid1/Pi-hole/pihole-FTL.db /etc/pihole/pihole-FTL.db
sudo service pihole-FTL start
Thanks for the analysis, I tried the steps above and got the same results, I am really puzzled about what happened, it is not a big deal to lose the data but I just get a little puzzled when I can't find the root cause.
Does that mean it still doesn't show any past queries in your Long-term data?
You could also check the database directly with a statement like:
sqlite3 /etc/pihole/pihole-FTL.db \
"SELECT count(domain) FROM queries \
WHERE(timestamp BETWEEN strftime('%s','2020-09-01') AND strftime('%s','2020-09-30'));"
This should get you the number of queries in September (adjust as required).
It does not show anything, although the file size is 400Mb the results are 0, looks like it was somehow overwritten.
pi@raspberrypi:~ $ sqlite3 /etc/pihole/pihole-FTL.db
"SELECT count(domain) FROM queries
WHERE(timestamp BETWEEN strftime('%s','2020-09-01') AND strftime('%s','2020-09-30'));"
0
pi@raspberrypi:~ $ sqlite3 /mnt/raid1/Pi-hole/pihole-FTL.db "SELECT count(domain) FROM queries
WHERE(timestamp BETWEEN strftime('%s','2020-09-01') AND strftime('%s','2020-09-30'));"
0
