Domains on Adlists -2 (again... when StevenBlack isn't select)

[Z80user]

Pi-hole: v5.18.2 (Latest: N/A)
AdminLTE: v5.21 (Latest: N/A)
FTL: 5.25.2 (Latest: N/A)

Debian 12

Expected behavior

without adding the default Adlist the number on the main page mush be 0

Actual behavior / bug

The number on the main page on "Domains on Adlists" show -2

Steps to reproduce

fresh install... history commands are the follow after add the used to the sudoers... echo mean a commentary to the previous line

wget http://install.pi-hole.com
sudo bash index.html
echo Selecto on the Blocklists option... so StevenBlack lists isn't added to the Adlist
sudo apt install unbound
sudo nano /etc/unbound/unbound.conf.d/pi-hole.conf
echo https://docs.pi-hole.net/guides/dns/unbound/ <- copy the server: section
sudo nano /etc/network/interfaces
echo add a statics ip
sudo reboot

Debug Token

• URL: https://trycorder.pi-hole.net/uqhGVnYp

Screenshots

Additional context

I report that problem some time ago and it appear again... I can do any SQL query to the database if will be needed as the previous time when the bug was fixed

[Z80user]

echo Selecto on the...
echo select "NO" on the ...

when I send it the text change on that line because I used minus than and great than

[yubiuser]

Please run

pihole-FTL sqlite3 -ni /etc/pihole/gravity.db "SELECT COUNT(*) FROM gravity;"

[Z80user]

it give "0" as an answer
the size of the file gravity.db is 94208 bytes

[yubiuser]

And

pihole-FTL sqlite3 -ni /etc/pihole/gravity.db "SELECT COUNT(*) FROM (SELECT DISTINCT domain FROM gravity);

Additionally, please run

echo ">stats" | nc 127.0.0.1 4711

[Z80user]

output from the first command:
0
output from the second command:

domains_being_blocked -2
dns_queries_today 0
ads_blocked_today 0
ads_percentage_today 0.000000
unique_domains 0
queries_forwarded 0
queries_cached 0
clients_ever_seen 0
unique_clients 0
dns_queries_all_types 0
reply_UNKNOWN 0
reply_NODATA 0
reply_NXDOMAIN 0
reply_CNAME 0
reply_IP 0
reply_DOMAIN 0
reply_RRNAME 0
reply_SERVFAIL 0
reply_REFUSED 0
reply_NOTIMP 0
reply_OTHER 0
reply_DNSSEC 0
reply_NONE 0
reply_BLOB 0
dns_queries_all_replies 0
privacy_level 0
status enabled
---EOM---

[rdwebdesign]

Run pihole -g and then echo ">stats" | nc 127.0.0.1 4711 again.

What do you see in domains_being_blocked line? 0 or -2?

[yubiuser]

The issue is in the real of FTL. It will return -2 if the database query returned an error.
https://github.com/pi-hole/FTL/blob/8943e26041c8730a5a9060eb64f76c6c2dd4e458/src/FTL.h#L96-L97

The error is somewhere along

https://github.com/pi-hole/FTL/blob/8943e26041c8730a5a9060eb64f76c6c2dd4e458/src/database/gravity-db.c#L1143-L1163

---

Please check /var/log/pihole/FTL.log for error messages.

[Z80user]

"pihole -g" fix the problem now the new value is "0"
the gravity.db file grow to 98304 bytes, before it was 94208 bytes

btw I clone the VM so I have 3 VMs right now
1-. the one than I used (than work as a dns) to made www.myhome.com can exist on my network
2-. the one than I didn't used "pihole -g" (clean instalation)
3-. the one than I used "pihole -g" and the number is 0
Just ask me if if you will want to know the result of any command in any point in time, otherwise I will give the result of the normal path

#4519 that was when I report that issue in the past and was fixed on the next version

this is the output of the FTL.log file but I remove the time-date of the output:

Using log file /var/log/pihole/FTL.log

########## FTL started on aio! ##########
FTL branch: master
FTL version: v5.25.2
FTL commit: 8943e260
FTL date: 2024-05-08 20:59:50 +0100
FTL user: pihole
Compiled for x86_64 (compiled on CI) using gcc (Debian 8.3.0-6) 8.3.0
Starting config file parsing (/etc/pihole/pihole-FTL.conf)
   SOCKET_LISTENING: only local
   AAAA_QUERY_ANALYSIS: Show AAAA queries
   MAXDBDAYS: max age for stored queries is 365 days
   RESOLVE_IPV6: Resolve IPv6 addresses
   RESOLVE_IPV4: Resolve IPv4 addresses
   DBINTERVAL: saving to DB file every minute
   DBFILE: Using /etc/pihole/pihole-FTL.db
   MAXLOGAGE: Importing up to 24.0 hours of log data
   PRIVACYLEVEL: Set to 0
   IGNORE_LOCALHOST: Show queries from localhost
   BLOCKINGMODE: Null IPs for blocked domains
   ANALYZE_ONLY_A_AND_AAAA: Disabled. Analyzing all queries
   DBIMPORT: Importing history from database
   PIDFILE: Using /run/pihole-FTL.pid
   SOCKETFILE: Using /run/pihole/FTL.sock
   SETUPVARSFILE: Using /etc/pihole/setupVars.conf
   MACVENDORDB: Using /etc/pihole/macvendor.db
   GRAVITYDB: Using /etc/pihole/gravity.db
   PARSE_ARP_CACHE: Active
   CNAME_DEEP_INSPECT: Active
   DELAY_STARTUP: No delay requested.
   BLOCK_ESNI: Enabled, blocking _esni.{blocked domain}
   NICE: Set process niceness to -10 (default)
   MAXNETAGE: Removing IP addresses and host names from network table after 365 days
   NAMES_FROM_NETDB: Enabled, trying to get names from network database
   EDNS0_ECS: Overwrite client from ECS information
   REFRESH_HOSTNAMES: Periodically refreshing IPv4 names
   RATE_LIMIT: Rate-limiting client making more than 1000 queries in 60 seconds
   LOCAL_IPV4: Automatic interface-dependent detection of address
   LOCAL_IPV6: Automatic interface-dependent detection of address
   BLOCK_IPV4: Automatic interface-dependent detection of address
   BLOCK_IPV6: Automatic interface-dependent detection of address
   SHOW_DNSSEC: Enabled, showing automatically generated DNSSEC queries
   MOZILLA_CANARY: Enabled
   PIHOLE_PTR: internal PTR generation enabled (pi.hole)
   ADDR2LINE: Enabled
   REPLY_WHEN_BUSY: Drop queries when the database is busy
   BLOCK_TTL: 2 seconds
   BLOCK_ICLOUD_PR: Enabled
   CHECK_LOAD: Enabled
   CHECK_SHMEM: Warning if shared-memory usage exceeds 90%
   CHECK_DISK: Warning if certain disk usage exceeds 90%
Finished config file parsing
Creating mutex
Creating mutex
PID of FTL process: 446
Database version is 12
Resizing "FTL-strings" from 40960 to (81920 * 1) == 81920 (/dev/shm: 712.7KB used, 2.1GB total, FTL uses 700.8KB)
Imported 0 alias-clients
Database successfully initialized
Imported 0 queries from the long-term database
 -> Total DNS queries: 0
 -> Cached DNS queries: 0
 -> Forwarded DNS queries: 0
 -> Blocked DNS queries: 0
 -> Unknown DNS queries: 0
 -> Unique domains: 0
 -> Unique clients: 0
 -> Known forward destinations: 0
Successfully accessed setupVars.conf
listening on 0.0.0.0 port 53
listening on :: port 53
PID of FTL process: 446
Listening on port 4711 for incoming IPv4 telnet connections
Listening on port 4711 for incoming IPv6 telnet connections
Listening on port 4711 for incoming socket telnet connections
INFO: FTL is running as user pihole (UID 996)
Reloading DNS cache
gravityDB_count(SELECT value FROM info WHERE property = 'gravity_count';) - SQL error step no more rows available
Count of gravity domains not available. Please run pihole -g
Compiled 0 whitelist and 0 blacklist regex filters for 0 clients in 0.7 msec
Blocking status is enabled
Reloading DNS cache
SQLite3 message: file renamed while open: /etc/pihole/gravity.db (28)
Compiled 0 whitelist and 0 blacklist regex filters for 0 clients in 0.1 msec
Blocking status is enabled

[yubiuser]

Mhh this is interesting.

gravityDB_count(SELECT value FROM info WHERE property = 'gravity_count';) - SQL error step no more rows available

tells us that this property is not available (or the table completely empty) where it should have been populated with 0 by

pi-hole/gravity.sh

Lines 700 to 704 in 5490a6e

	if [[ "${table}" == "gravity" ]]; then
	local unique
	unique="$(pihole-FTL sqlite3 -ni "${gravityDBfile}" "SELECT COUNT(*) FROM (SELECT DISTINCT domain FROM ${table});")"
	echo -e " ${INFO} Number of ${str}: ${num} (${COL_BOLD}${unique} unique domains${COL_NC})"
	pihole-FTL sqlite3 -ni "${gravityDBfile}" "INSERT OR REPLACE INTO info (property,value) VALUES ('gravity_count',${unique});"

I don't see why this part of the code should not be executed, even when no adlist is selected (and why a second run of gravity will fix this)

---

Can you please check if the property is set in the database?

pihole-FTL sqlite3 -ni /etc/pihole/gravity.db "SELECT value FROM info WHERE property = 'gravity_count';"

[Z80user]

after the fix now apear as "0"

I just to check before and after the fix with a more generic SQL
pihole-FTL sqlite3 -ni /etc/pihole/gravity.db "SELECT * FROM info;"

BEFORE
version|15

AFTER
version|15
updated|1717645043
gravity_count|0

[rdwebdesign]

I don't see why this part of the code should not be executed, even when no adlist is selected (and why a second run of gravity will fix this)

Maybe something else (external - not related to Pi-hole) failed during installation, the script was interrupted and gravity was never executed, resulting in an empty table.
The second attempt was actually the first time gravity was executed.

[Z80user]

I just install Debian 12.4 on a VM machine on proxmox, with all of the basic option, I only change the number of Cores to 4 and the memory size to 4 GB to speed up the installation process. (Nothing strange here)
During the Debian installation process I didn't add anything except the basic "standard system utilities", everything is the default installation. with only 1 partition as "/" and no swap partition (I think isn't anything strange here)

I get the script with wget
I install sudo with root "su" and "apt install sudo"
I add the default user to the sudoers file with nano
I close root and the user session to can used sudo
I execute the file with sudo bash index.html (as I didn't change the filename but isn't needed to can be executed with bash)
and during the execution of the script I only disable the Adlist "StevenBlack" as I don't really need it...

as that is the only change I do than is different from a "normal" installation

I don't see why this part of the code should not be executed, even when no adlist is selected (and why a second run of gravity will fix this)

Maybe something else (external - not related to Pi-hole) failed during installation, the script was interrupted and gravity was never executed, resulting in an empty table. The second attempt was actually the first time gravity was executed.

that issue was previously fixed it just reappear again at some point in time... surely I'm one of the few people than used pi-hole without any default list
434e9f3

maybe create another VM like that one and record it on a video will help... with and without the default Adlist

[rdwebdesign]

I execute the file with sudo bash index.html

This is a very strange step...

Are you trying to execute the HTML file on the command line?

Most important: Pi-hole doesn't have an index.html file.

EDIT:
I just saw you are using the original filename when wget command saves the file without -O option ("as I didn't change the filename ...").

[yubiuser]

I tried it on a fresh debian image and it failed at

  [i] Creating new gravity database
  [i] Migrating content of /etc/pihole/adlists.list into new database

  [✗] Unable to fill table adlist in database /etc/pihole/gravity.db
  Error: cannot open "/tmp/tmp.pIjdwfkGQe"
  [✓] Cleaning up stray matter

[yubiuser]

Ok, I know what happens here:

pi-hole/gravity.sh

Lines 156 to 159 in 5490a6e

	# Create a temporary file. We don't use '--suffix' here because not all
	# implementations of mktemp support it, e.g. on Alpine
	tmpFile="$(mktemp -p "${GRAVITY_TMPDIR}")"
	mv "${tmpFile}" "${tmpFile%.*}.gravity"

Created and moved the temp file, but the variable name is not updated. When the adlist list is not empty, the file will be re-created in

pi-hole/gravity.sh

Lines 198 to 200 in 5490a6e

	elif [[ "${table}" == "adlist" ]]; then
	# Adlist table format
	echo "${rowid},\"${domain}\",1,${timestamp},${timestamp},\"Migrated from ${src}\",,0,0,0" >> "${tmpFile}"

and therefore exist. But when the list of adlist is empty, the file does not exist at this point.

---

This is fixed in v6 with #5457