Delete queries error


Pi-hole v5.9
FTL v5.14
Web Interface v5.11

I've always run this command to remove entries from the database:
/usr/bin/sqlite3 /etc/pihole/pihole-FTL.db "DELETE FROM queries WHERE client=''"

I get the below error:
Error: cannot modify queries because it is a view

Did the command or process change for removing entries?

With the latest version the database schema has changed. The actual data is stored in the new table query_storage. The queries is a view only from which you can't delete queries anymore. Furthermore, the client information is not stored in query_storage as IP but rather an ID linked to the new client_by_id table.
You need to do two things now:

sqlite3 /etc/pihole/pihole-FTL.db "DELETE FROM query_storage WHERE client='';"

To delete the remaining queries for before the update.

From now on you need to perform:

sqlite3 /etc/pihole/pihole-FTL.db "DELETE FROM query_storage where client in (select id from client_by_id where ip='');"


Thanks. I'll give it a shot shortly.


Clearly worked.
Biggest difference now is, you have to stop FTL prior to the delete. Not a big deal at all.


It worked, but the queries view table that is responsible for the top domains on web GUI did not change. How can I force the queries table to regenerate?

Try restating FTL.

I think you mean restarting FTL.

pihole restartdns does not help.

I tried to use the following command:

sudo service pihole-FTL stop && sudo sqlite3 pihole-FTL.db "delete from query_storage where domain='';" ; pihole restartdns

But still shows on the web gui.

Reboot does not help either.

Edit: I used a wrong command. See next reply.

I used a incorrect command. The new one should be this:

sudo service pihole-FTL stop && sudo sqlite3 pihole-FTL.db "delete from query_storage where domain in (select id from domain_by_id where domain like '');" ; pihole restartdns

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.