My Query Keeps Blanking out and Have to run PiHole - r Repair to fix it Repeatedly

[ob1kenobi]

The issue I am facing:
RPi is working as evidenced by the Dashboard. But when I look at the Query Log it is empty. I search for domains and nothing shows up.
I actually drafted this about a week ago, but when I ran the update to Pi-hole, the Query log started showing up again. However, when I went to check today, the same problem recurred and my Query log I blank again. If I run pihole -r it shows up again and my Dashboard is empty and only starts to populate but the Query Log is showing entries again
I had a previous challenge with this that was resolved back in January

Details about my system:

My New (today Jun 10) Debug token: https://tricorder.pi-hole.net/EQWRst3d/

Nothing jumped out to my untrained noob eye
sudo cat /var/log/lighttpd/error.log - Empty

** Things I have tried and threads I have looked at. **
So I found a similar thread
Query Log Always Empty
And followed along
And tried the command suggested by @deHakkelaar

echo 'SELECT count(*) FROM queries WHERE timestamp BETWEEN "'$(date --date="2020-09-4 00:00:00" +%s)'" AND "'$(date --date="2020-09-5 23:59:59" +%s)'";' | sqlite3 /etc/pihole/pihole-FTL.d

Resolved the problem of sqlite3 not installed by updating everything and installing this
But then reached an impasse
Error: unable to open database "/etc/pihole/pihole-FTL.db": unable to open database file

I verified that the file exists

drwxr-xr-x 2 pihole pihole 4096 Feb 28 16:50 migration_backup_v6
-rw-r----- 1 pihole pihole 1984991232 Jun 1 09:53 pihole-FTL.db
-rw-r----- 1 pihole pihole 32768 Jun 1 09:53 pihole-FTL.db-shm
-rw-r----- 1 pihole pihole 12392 Jun 1 09:54 pihole-FTL.db-wal
-rw-r--r-- 1 pihole pihole 56161 Jun 5 19:06 pihole.toml
Tried to 'sudo' the above command but same response.

Noted the odd fact that the date on that file is Jun 1 (today is Jun 6)

Attempting to get ahead of questions, I am answering the requests for additional info below

hostnamectl | tail -3
Operating System: Debian GNU/Linux 12 (bookworm)
Kernel: Linux 6.12.20+rpt-rpi-v8
Architecture: arm64

lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 12 (bookworm)
Release: 12
Codename: bookworm

sudo netstat -nltup | grep 'Proto|:53 |:67 |:80 |:547 |:471[1-8] '
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:53 0.0.0.0:* LISTEN 1828475/pihole-FTL
tcp6 0 0 :::53 :::* LISTEN 1828475/pihole-FTL
udp 0 0 0.0.0.0:53 0.0.0.0:* 1828475/pihole-FTL
udp6 0 0 :::53 :::* 1828475/pihole-FTL

php-cgi -m
[PHP Modules]
calendar
cgi-fcgi
Core
ctype
date
dom
exif
FFI
fileinfo
filter
ftp
gettext
hash
iconv
intl
json
libxml
openssl
pcntl
pcre
PDO
pdo_sqlite
Phar
posix
random
readline
Reflection
session
shmop
SimpleXML
sockets
sodium
SPL
sqlite3
standard
sysvmsg
sysvsem
sysvshm
tokenizer
xml
xmlreader
xmlwriter
xsl
Zend OPcache
zlib

[Zend Modules]
Zend OPcache

uptime && dmesg -T | grep -i voltage
12:19:53 up 53 days, 19:38, 2 users, load average: 0.30, 0.43, 0.70

And added a whitelist domain while this was running, but log remained empty (whitelisted domain showed up in the list)
udo tail -f /var/log/lighttpd/{access,error}.log
==> /var/log/lighttpd/access.log <==

==> /var/log/lighttpd/error.log <==
<<<<<< END of Diagnostic Info

Not a browser cache problem as this occurs in all browsers I try some of which have never accessed the RpI

What I have changed since installing Pi-hole:

Not changed much aside from adding additional blocked domains and lists and excluding some

[rdwebdesign]

Your debug log shows your database is corrupted:

2025-06-10 16:02:01.362 EDT [2209656/T2209713] ERROR: SQLite3: index corruption at line 100113 of [17144570b0] (779)
2025-06-10 16:02:01.362 EDT [2209656/T2209713] ERROR: SQLite3: statement aborts at 48: [DELETE FROM query_storage WHERE id IN (SELECT id FROM query_storage WHERE timestamp <= 1741723320 LIMIT (SELECT COUNT(*)/100 FROM query_storage));] database disk image is malformed (779)
2025-06-10 16:02:01.365 EDT [2209656/T2209713] ERROR: ERROR: SQL query "DELETE FROM query_storage WHERE id IN (SELECT id FROM query_storage WHERE timestamp <= 1741723320 LIMIT (SELECT COUNT(*)/100 FROM query_storage));" failed: database disk image is malformed (SQLITE_CORRUPT)
2025-06-10 16:02:01.365 EDT [2209656/T2209713] WARNING: Database /etc/pihole/pihole-FTL.db is damaged and cannot be used.
2025-06-10 16:02:01.365 EDT [2209656/T2209713] ERROR: delete_old_queries_in_DB() failed!

Database /etc/pihole/pihole-FTL.db is damaged and cannot be used.

Run these commands to move the existing database to a new file name and let Pi-hole create a fresh database.

sudo service pihole-FTL stop

sudo mv /etc/pihole/pihole-FTL.db /etc/pihole/pihole-FTL-old.db

sudo service pihole-FTL start

[rdwebdesign]

NOTE:

You are now running Pi-hole v6. You need to pay attention to some changes between v5 and v6.

@ob1kenobi

sudo cat /var/log/lighttpd/error.log - Empty

...

sudo tail -f /var/log/lighttpd/{access,error}.log
==> /var/log/lighttpd/access.log <==

==> /var/log/lighttpd/error.log <==

There is no lighttpd in Pi-hole v6. You can even uninstall it if your don't use it for other services.

@ob1kenobi

php-cgi -m
[PHP Modules]
...

There is no PHP in Pi-hole v6.

[deHakkelaar]

@ob1kenobi

Resolved the problem of sqlite3 not installed by updating everything and installing this

You dont need to install sqlite3 anymore bc this code is already embedded in the pihole-FTL binary ... since ages:

$ pihole-FTL sqlite3 --version
3.47.2 2024-12-07 20:39:59 2aabe05e2e8cae4847a802ee2daddc1d7413d8fc560254d93ee3e72c14685b6c (64-bit)

Eg:

$ sudo pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db  ".help"
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB from the existing database
.connection [close] [#]  Open or close an auxiliary database connection
.crlf ?on|off?           Whether or not to use \r\n line endings
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?OBJECTS?          Render database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.indexes ?TABLE?         Show names of indexes
.intck ?STEPS_PER_UNLOCK?  Run an incremental integrity check on the db
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.log FILE|on|off         Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?OPTIONS?     Set output mode
.nonce STRING            Suspend safe mode for one command if nonce matches
.nullvalue STRING        Use STRING in place of NULL values
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Stop interpreting input stream, exit if primary.
.read FILE               Read input from FILE or command output
.recover                 Recover as much data as possible from corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save ?OPTIONS? FILE     Write database to FILE (an alias for .backup ...)
.scanstats on|off|est    Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.separator COL ?ROW?     Change the column and row separators
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?ARG?             Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.version                 Show source, library and compiler versions
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set minimum column widths for columnar output
.www                     Display output of the next command in web browser

Things change quickly around here

[deHakkelaar]

@ob1kenobi

RPi is working

@rdwebdesign

Your debug log shows your database is corrupted:

If file corruption occurs on a Pi, always check below for "under-voltage" messages in the Linux kernel ring buffer/log with journalctl:

Raspberry Pi loses network connection

Have you tried another power adapter and cable? Raspi's can crash or even corrupt storage (SD card etc) if the power isnt sufficient. See also below: https://www.raspberrypi.com/documentation/computers/raspberry-pi.html#power-supply-warnings You could also check the systemd journals for errors/warnings etc for the current --boot: sudo journalctl --full --no-hostname --catalog --priority warning --boot Or two boots back (-2): sudo journalctl --full --no-hostname --catalog --priority warnin…

Could also search the whole journal for voltage messages:

sudo journalctl --full --no-hostname --pager-end --grep voltage

Or if it says --grep isnt supported/compiled:

sudo journalctl --full --no-hostname | grep -i voltage

[ob1kenobi]

Thanks for this @rdwebdesign
I looked ot see how I could find what else was using lighttpd, but the guidance was not easy/clear. I'd like to remove it if it is not used.

Same question re Sqlite3 (which I am pretty sure I installed manually because it was not there) @deHakkelaar - assuming it is not required, I feel I should remove this as well

[rdwebdesign]

@ob1kenobi

I looked ot see how I could find what else was using lighttpd, but the guidance was not easy/clear.

You can try to stop the service using sudo systemctl lighttpd.service and check if any of the services running on your system stops working.

If no web service is broken, then you can safely uninstall it.