No query log update anymore and no top clients (all and blocked)

jf62 · January 27, 2024, 10:45pm

Hi, I just updated to following versions:

Core vDev (development-v6, cc8f9fe0)
FTL vDev (development-v6, ce4407b5)
Web interface vDev (development-v6, 1dcf90bd)

and have no query list updates anymore and also the top clients aren't shown anymore at dashboard.

Numbers are counted and looks like blocking and resolving works fine

jf62 · January 27, 2024, 10:47pm

Flush log didn't change anything
in dashboard I see 2230 total and 1446 blocked but in query log are only 52

rdwebdesign · January 27, 2024, 11:10pm

Please upload a debug log and post just the token URL that is generated after the log is uploaded by running the following command from the Pi-hole host terminal:

pihole -d

jf62 · January 27, 2024, 11:28pm

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

jf62 · January 27, 2024, 11:30pm

btw. If I go back to a older version (I use proxmox and used a backup from 21st of January) all works fine:

Core vDev (development-v6, e1c41fe4)
FTL vDev (development-v6, 3341f0e4)
Web interface vDev (development-v6, de5a4b0d)

DL6ER · January 28, 2024, 3:21am

Is your database on the same host as the VM? Which filesystem are you using?

jf62 · January 28, 2024, 11:02am

It's a Debian 12 LXC container and PI-hole is all and the only whats running there, No access to other LXC's. Also standard ext4 filesystem and as I told it's running after backup older version I mentioned in prior task fine again.

jf62 · January 28, 2024, 8:59pm

Any idea what the reason can be?

DL6ER · January 28, 2024, 9:24pm

Your debug log shows two issues - while the first one may or not be the cause for the second one:

Your log contains a warning that the database was not closed properly. Maybe the container was restarted abruptly or something else happened that didn't allow the pihole-FTL process to handle the database correctly.
As a consequence of no. 1 above (or completely independent, we don't know yet), your database got locked. This prevented pihole-FTL from reading your gravity database resulting in many warnings in your /var/log/pihole/FTL.log. And - apparently - down the line also prevented it from accessing any query data at all.

Please try starting from a fresh database to eliminate a database corruption as cause using:

sudo service pihole-FTL stop
sudo mv /etc/pihole/pihole-FTL.db /etc/pihole/pihole-FTL_broken.db
sudo rm /etc/pihole/pihole-FTL.db*
sudo service pihole-FTL start

inside the container.

Also of interesting is the output of:

pihole-FTL sqlite3 /etc/pihole/pihole-FTL_broken.db "PRAGMA integrity_check;"

(this command may take some time to finish, there is no progress reporting)

jf62 · January 28, 2024, 9:49pm

No change at all after doing the recomended steps and:
pihole-FTL sqlite3 /etc/pihole/pihole-FTL_broken.db "PRAGMA integrity_check;"
ok

jf62 · January 28, 2024, 10:38pm

Back to prior version and all fine again. If I can help further investigation, let me know

DL6ER · January 29, 2024, 3:09pm

Thank for you the offer and sorry for the long delay in responses... First, we should investigate if this is due to the WAL journal or due to some other problem (which will be harder to trace).

Please run

sudo pihole checkout ftl new/useWAL
sudo pihole-FTL --config database.useWAL false

to get an experimental branch which allows controlling the WAL mode and, subsequently, turning it off.

FTL from this branch should be available in a few minutes. Until then, you'd get a harmless error during the checkout and nothing happens.

edit Wait, I just had another look into your debug log and see that you have non-empty setting for webserver.api.excludeClients and webserver.api.excludeDomains. We merged a feature last week that extended these options to also be effective for the Query Log:

github.com/pi-hole/FTL

Add regex filtering support for domains on the Query Log

pi-hole:development-v6 ← pi-hole:new/queryLogRegex

opened 02:59PM - 01 Aug 23 UTC

DL6ER

+364 -95

# What does this implement/fix? ~~Add regex filtering support for domains on …the Query Log (new config option `webserver.api.excludeRegex` accepting an array of strings).~~ updated description: Add regex filtering support for domains on the Query Log as well as the "Top Items" tables on the dashboard. This is implemented by offering regex support for existing `webserver.api.excludeClients` and `webserver.api.excludeDomains` settings. This implements a highly upvoted Discourse feature request (see comment below). --- **Related issue or feature (if applicable):** N/A **Pull request in [docs](https://github.com/pi-hole/docs) with documentation (if applicable):** TBD --- **By submitting this pull request, I confirm the following:** 1. I have read and understood the [contributors guide](https://docs.pi-hole.net/guides/github/contributing/), as well as this entire template. I understand which branch to base my commits and Pull Requests against. 2. I have commented my proposed changes within the code. 3. I am willing to help maintain this change if there are issues with it later. 4. It is compatible with the [EUPL 1.2 license](https://opensource.org/licenses/EUPL-1.1) 5. I have squashed any insignificant commits. ([`git rebase`](http://gitready.com/advanced/2009/02/10/squashing-commits-with-rebase.html)) ## Checklist: - [x] The code change is tested and works locally. - [x] I based my code and PRs against the repositories `developmental` branch. - [x] I [signed off](https://docs.pi-hole.net/guides/github/how-to-signoff/) all commits. Pi-hole enforces the [DCO](https://docs.pi-hole.net/guides/github/dco/) for all contributions - [x] I [signed](https://docs.github.com/en/authentication/managing-commit-signature-verification/signing-commits) all my commits. Pi-hole requires signatures to verify authorship - [x] I have read the above and my PR is ready for review.

Could you please check if editing these (e.g. via the web interface's settings -> all settings page) resolves the issue you are seeing? I see you are using a limited set but - maybe just maybe - if all your queries are either localhost or fritz.box, then this would easily explain what is happening here.

jf62 · January 29, 2024, 6:25pm

so, removing all entries in webserver.api.excludeClients and webserver.api.excludeDomains fixed the behavior,

But how can I use this feature in future? And also how will you handle this, if you will once do a upgrade for all users from V5 to V6 which are using this feature in V5?

DL6ER · January 29, 2024, 6:42pm

There is no issue with using these settings at all. We still don't really know why the settings have been an issue for you. Is that that

?

jf62 · January 29, 2024, 6:44pm

exclude clients:
localhost
fritz.box
iobroker.fritz.box
SKY-IPTV-WZ.fritz.box
SKY-IPTV-GZ.fritz.box
DiskStation.fritz.box
pihole-6.fritz.box

exclude domains:
www.example.com
www.example.net
www.example.org
ipinfo.io
cloudsync-tw.synology.com
checkip.synology.com
fritz.box
www.gstatic.com
connectivitycheck.gstatic.com
wpad.fritz.box
connectivity.sky.com

DL6ER · January 29, 2024, 6:50pm

Yes, I have seen these in your debug log. My point is:

When you have localhost and fritz.box as clients to be excluded and all your queries either come from localhost or fritz.box (because you are using the DNS server of your FritzBox instead of distributing the Pi-hole as DNS server), then everything is behaving as expected.

The only bit that was not expected is that the functionality of these settings was extended to the Query Log.

jf62 · January 29, 2024, 7:00pm

ok, but what's the opions then? (Pi-hole as dns doesn't work for me )

DL6ER · January 29, 2024, 7:01pm

Why?

jf62 · January 29, 2024, 7:04pm

forget it - I use Pi-hole as DNS (I mean as DHCP doesn't work)

jf62 · January 29, 2024, 7:07pm

Where do you see, that I'm not using Pi-hole as DNS? fritz-box as DHCP sends only the IP of Pi-Hole to the clients (v4 and v6) and also use it by itself