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

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:~ $

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

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.

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:

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