Trying to delete adlist table, got Error: FOREIGN KEY constraint failed (19)

Help
1

Trying to delete the adlist table with the Pi-hole embedded sqlite3 i got the "FOREIGN KEY" error:
$ sudo pihole-FTL sqlite3 /etc/pihole/gravity.db "DELETE FROM adlist"
Error: stepping, FOREIGN KEY constraint failed (19)

but using the sqlite3 from:
$ sudo apt-get install sqlite3
everything is fine.

It seems like a bug in Pi-hole or whatever,,,
how can i properly delete the whole adlist table from a bash script just using the Pi-hole embedded sqlite3 (dont like to install any extra standalone sqlite3 for that purpose)?

Here is what i did:

pi@raspberrypi:~ $ pihole -v
  Pi-hole version is v5.10 (Latest: v5.10)
  AdminLTE version is v5.12 (Latest: v5.11)
  FTL version is v5.15 (Latest: v5.15)
pi@raspberrypi:~ $ sudo rm /etc/pihole/gravity.db
pi@raspberrypi:~ $ pihole -g
  [i] Creating new gravity database
  [i] Neutrino emissions detected...
  [✓] Pulling blocklist source list into range
  [i] No source list found, or it is empty


  [✓] Preparing new gravity database
  [i] Using libz compression

  [✓] Creating new gravity databases
  [✓] Storing downloaded domains in new gravity database
  [✓] Building tree
  [✓] Swapping databases
  [✓] The old database remains available.
  [i] Number of gravity domains: 0 (0 unique domains)
  [i] Number of exact blacklisted domains: 0
  [i] Number of regex blacklist filters: 0
  [i] Number of exact whitelisted domains: 0
  [i] Number of regex whitelist filters: 0
  [✓] Flushing DNS cache
  [✓] Cleaning up stray matter

  [✓] FTL is listening on port 53
     [✓] UDP (IPv4)
     [✓] TCP (IPv4)
     [✓] UDP (IPv6)
     [✓] TCP (IPv6)

  [✓] Pi-hole blocking is enabled
pi@raspberrypi:~ $ sudo pihole-FTL sqlite3 /etc/pihole/gravity.db "INSERT INTO adlist (address, enabled, comment) VALUES ('https://dbl.oisd.nl/', 1, 'Full List von oisd.nl');"
pi@raspberrypi:~ $ pihole -g
  [i] Neutrino emissions detected...
  [✓] Pulling blocklist source list into range

  [✓] Preparing new gravity database
  [i] Using libz compression

  [i] Target: https://dbl.oisd.nl/
  [✓] Status: Retrieval successful
  [i] Analyzed 1027673 domains
  [i] List stayed unchanged

  [✓] Creating new gravity databases
  [✓] Storing downloaded domains in new gravity database
  [✓] Building tree
  [✓] Swapping databases
  [✓] The old database remains available.
  [i] Number of gravity domains: 1027673 (1027673 unique domains)
  [i] Number of exact blacklisted domains: 0
  [i] Number of regex blacklist filters: 0
  [i] Number of exact whitelisted domains: 0
  [i] Number of regex whitelist filters: 0
  [✓] Flushing DNS cache
  [✓] Cleaning up stray matter

  [✓] FTL is listening on port 53
     [✓] UDP (IPv4)
     [✓] TCP (IPv4)
     [✓] UDP (IPv6)
     [✓] TCP (IPv6)

  [✓] Pi-hole blocking is enabled
pi@raspberrypi:~ $ sudo pihole-FTL sqlite3 /etc/pihole/gravity.db "DELETE FROM adlist"
Error: stepping, FOREIGN KEY constraint failed (19)
pi@raspberrypi:~ $ sudo apt-get install sqlite3
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Suggested packages:
  sqlite3-doc
The following NEW packages will be installed:
  sqlite3
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 1,115 kB of archives.
After this operation, 3,086 kB of additional disk space will be used.
Get:1 http://deb.debian.org/debian bullseye/main arm64 sqlite3 arm64 3.34.1-3 [1,115 kB]
Fetched 1,115 kB in 0s (3,642 kB/s)
Selecting previously unselected package sqlite3.
(Reading database ... 38703 files and directories currently installed.)
Preparing to unpack .../sqlite3_3.34.1-3_arm64.deb ...
Unpacking sqlite3 (3.34.1-3) ...
Setting up sqlite3 (3.34.1-3) ...
Processing triggers for man-db (2.9.4-2) ...
pi@raspberrypi:~ $ sudo sqlite3 /etc/pihole/gravity.db "DELETE FROM adlist"
pi@raspberrypi:~ $ pihole -g
  [i] Neutrino emissions detected...
  [✓] Pulling blocklist source list into range
  [i] No source list found, or it is empty


  [✓] Preparing new gravity database
  [i] Using libz compression

  [✓] Creating new gravity databases
  [✓] Storing downloaded domains in new gravity database
  [✓] Building tree
  [✓] Swapping databases
  [✓] The old database remains available.
  [i] Number of gravity domains: 0 (0 unique domains)
  [i] Number of exact blacklisted domains: 0
  [i] Number of regex blacklist filters: 0
  [i] Number of exact whitelisted domains: 0
  [i] Number of regex whitelist filters: 0
  [✓] Flushing DNS cache
  [✓] Cleaning up stray matter

  [✓] FTL is listening on port 53
     [✓] UDP (IPv4)
     [✓] TCP (IPv4)
     [✓] UDP (IPv6)
     [✓] TCP (IPv6)

  [✓] Pi-hole blocking is enabled
pi@raspberrypi:~ $
2

SQLite is telling you that it cannot delete a row because that would violate a FOREIGN KEY constraint.

Manually deleting a row from a table would require you to first manually delete all dependent data from other tables as well.

You are correct in pointing out that SQlite3 versions as used by Pi-hole and potentially available on system would exhibit a different behaviour. I can confirm your observation for Pi-hole's 3.38.2 vs. 3.27.2.

To allow manual interaction with the database as you are striving for, we may have to adopt the database.

But to better understand your use case, I wonder:
What's your motivation for scripting manual deletion of all adlists?

1 Like
4

Thanks Bucking_Horn for your answer & confirmation :slight_smile:
I have tons of block lists in several collections that i would like to use/change when i need. For that i use a bash script over SSH to mass insert and delete table rather than clicking by hand over the web interface.

6

It's not. On the contrary, the sqlite3 from your OS repos does not follow the sqlite maintainers which recommend to compile the binary with a flag that always respects FOREIGN KEY constraints.

Totally different issue, but same reason for failing with a lot more information about the foreign key constrains. Start reading the thread from here:

10

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