Using sqlite3 to remove entries for pihole-FTL.db is not longer working

General
1

In the past, using sqlite3 like the following worked to remove entries but when I tried now, I get the error shown below. Is this a bug or did the syntax change?

# sqlite3 /etc/pihole/pihole-FTL.db "Delete from queries where client like '192.168.2.%'"
Error: in prepare, cannot modify queries because it is a view

@jfb @PromoFaux

2

I can confirm the tables have changed.

Old:

pi@ph5a:~ $ sqlite3 /etc/pihole/pihole-FTL.db ".tables"
aliasclient        ftl                network            queries
counters           message            network_addresses

pi@ph5a:~ $ sqlite3 /etc/pihole/pihole-FTL.db ".schema queries"
CREATE TABLE queries ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp INTEGER NOT NULL, type INTEGER NOT NULL, status INTEGER NOT NULL, domain TEXT NOT NULL, client TEXT NOT NULL, forward TEXT , additional_info TEXT);
CREATE INDEX idx_queries_timestamps ON queries (timestamp);

New/current:

pi@ph5b:~ $ sqlite3 /etc/pihole/pihole-FTL.db ".tables"
addinfo_by_id      counters           ftl                network_addresses
aliasclient        domain_by_id       message            queries
client_by_id       forward_by_id      network            query_storage

pi@ph5b:~ $ sqlite3 /etc/pihole/pihole-FTL.db ".schema queries"
CREATE VIEW queries AS SELECT id, timestamp, type, status, CASE typeof(domain) WHEN 'integer' THEN (SELECT domain FROM domain_by_id d WHERE d.id = q.domain) ELSE domain END domain,CASE typeof(client) WHEN 'integer' THEN (SELECT ip FROM client_by_id c WHERE c.id = q.client) ELSE client END client,CASE typeof(forward) WHEN 'integer' THEN (SELECT forward FROM forward_by_id f WHERE f.id = q.forward) ELSE forward END forward,CASE typeof(additional_info) WHEN 'integer' THEN (SELECT content FROM addinfo_by_id a WHERE a.id = q.additional_info) ELSE additional_info END additional_info, reply_type, reply_time, dnssec FROM query_storage q
/* queries(id,timestamp,type,status,domain,client,forward,additional_info,reply_type,reply_time,dnssec) */;

pi@ph5b:~ $ sqlite3 /etc/pihole/pihole-FTL.db ".schema query_storage"
CREATE TABLE IF NOT EXISTS "query_storage" (id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp INTEGER NOT NULL, type INTEGER NOT NULL, status INTEGER NOT NULL, domain INTEGER NOT NULL, client INTEGER NOT NULL, forward INTEGER, additional_info INTEGER, reply_type INTEGER, reply_time REAL, dnssec INTEGER);
CREATE INDEX idx_queries_timestamps ON "query_storage" (timestamp);
3

I am not familiar with sqlite3 at all. Does the old command need to be updated to reflect some content in your post?

4

Yes.
With the old version, the queries were stored in the queries table.
Currently they are stored in the query_storage table instead.
So just change queries into query_storage to get the same effect when trying to DELETE.

EDIT: You can check which ones get deleted if run below one first:

sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM query_storage WHERE client LIKE '192.168.2.%'"

1 Like
5

This won't be enough. query_tables does not save the ip in client anymore, but has an integer which links to client_by_id. What you need now is

SELECT * from query_storage where client in (SELECT id from client_by_id where ip like '10.0.1.%')

2 Likes
6

That comment outputs a ton of data but does not remove the entries:

sqlite3 /etc/pihole/pihole-FTL.db "SELECT * from query_storage where client in (SELECT id from client_by_id where ip like '10.9.7.209')"
7

Sure, you need to replace Select * with Delete

1 Like
8

Thank you @yubiuser and @deHakkelaar - this worked as intended:

sqlite3 /etc/pihole/pihole-FTL.db "DELETE from query_storage where client in (SELECT id from client_by_id where ip like '10.97.209')"

systemctl restart pihole-FTL
2 Likes
9

Dont think you need to do above.
Those queries are only called upon when checking the webGUI.
They dont effect the pihole-FTL daemon.

EDIT: Ow I'll give you some heads up, the sqlite3 package is not installed anymore as a dependency:

pi@ph5b:~ $ curl -sSL install.pi-hole.net | grep -i deps=
        OS_CHECK_DEPS=(grep dnsutils)
        INSTALLER_DEPS=(git iproute2 dialog ca-certificates)
        PIHOLE_DEPS=(cron curl iputils-ping psmisc sudo unzip idn2 libcap2-bin dns-root-data libcap2 netcat-openbsd procps jq)
        PIHOLE_WEB_DEPS=(lighttpd "${phpVer}-common" "${phpVer}-cgi" "${phpVer}-sqlite3" "${phpVer}-xml" "${phpVer}-intl")
        OS_CHECK_DEPS=(grep bind-utils)
        INSTALLER_DEPS=(git dialog iproute newt procps-ng which chkconfig ca-certificates)
        PIHOLE_DEPS=(cronie curl findutils sudo unzip libidn2 psmisc libcap nmap-ncat jq)
        PIHOLE_WEB_DEPS=(lighttpd lighttpd-fastcgi php-common php-cli php-pdo php-xml php-json php-intl)

Instead you can use the pihole-FTL binary for that now:

pi@ph5b:~ $ pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db ".tables"
addinfo_by_id      counters           ftl                network_addresses
aliasclient        domain_by_id       message            queries
client_by_id       forward_by_id      network            query_storage
1 Like
10

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