Delete queries error

Help
1

Hello,

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='192.168.10.51'"

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

Did the command or process change for removing entries?
Thanks!
Joe

2

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='192.168.10.51';"

To delete the remaining queries for 192.168.10.51 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='192.168.10.51');"

4 Likes
3

Thanks. I'll give it a shot shortly.

Joe

4

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

Joe

5

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?

6

Try restating FTL.

7

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='example.com';" ; pihole restartdns

But example.com still shows on the web gui.

Reboot does not help either.

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

8

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 'example.com');" ; pihole restartdns
9

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