ALL adlist connections refused, No cached lists available

sean311 · December 9, 2023, 12:23am

Please follow the below template, it will help us to help you!

  Pi-hole version is v5.17.2 (Latest: v5.17.2)
  web version is v5.21 (Latest: v5.21)
  FTL version is v5.23 (Latest: v5.23)

Expected Behaviour:

Using pihole -g -f or the via the web interface, should update Adlists that are already present. Should download new Adlists if never been downloaded before.

Actual Behaviour:

[Sat Dec 09] root@sandalpi: ~$ pihole -g -f
-- Loading resources from /root/.sqliterc
  [✓] Deleting existing list cache
  [i] Neutrino emissions detected...
  [✓] Pulling blocklist source list into range

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

  [i] Target: adlist               domainlist_by_group  vw_gravity
  [✗] Invalid Target

  [i] Target: adlist_by_group      gravity              vw_regex_blacklist
  [✗] Invalid Target

  [i] Target: client               group                vw_regex_whitelist
  [✗] Invalid Target

  [i] Target: client_by_group      info                 vw_whitelist
  [✗] Invalid Target

  [i] Target: domain_audit         vw_adlist
  [✗] Invalid Target

  [i] Target: domainlist           vw_blacklist
  [✗] Invalid Target

  [i] Target: address
  [✗] Invalid Target

  [i] Target: ------------------------------------------------------------
  [i] Status: Pending...Invalid option: ------------------------------------------------------------
Usage:  dig [@global-server] [domain] [q-type] [q-class] {q-opt}
            {global-d-opt} host [@local-server] {local-d-opt}
            [ host [@local-server] {local-d-opt} [...]]

Use "dig -h" (or "dig -h | more") for complete list of options
  [✗] Status: ------------------------------------------------------------ ()
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/StevenBlack/hosts/master/h
  [✗] Status: Not found
  [✗] List download failed: no cached list available

  [i] Target: osts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/PolishFiltersTeam/KADhosts
  [✗] Status: https://raw.githubusercontent.com/PolishFiltersTeam/KADhosts (400)
  [✗] List download failed: no cached list available

  [i] Target: /master/KADhosts.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast
  [✗] Status: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast (400)
  [✗] List download failed: no cached list available

  [i] Target: er/add.Spam/hosts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://adaway.org/hosts.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/anudeepND/blacklist/master
  [✗] Status: https://raw.githubusercontent.com/anudeepND/blacklist/master (400)
  [✗] List download failed: no cached list available

  [i] Target: /adservers.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/bigdargon/hostsVN/master/h
  [✗] Status: Not found
  [✗] List download failed: no cached list available

  [i] Target: osts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast
  [✗] Status: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast (400)
  [✗] List download failed: no cached list available

  [i] Target: er/add.2o7Net/hosts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/crazy-max/WindowsSpyBlocke
  [✗] Status: https://raw.githubusercontent.com/crazy-max/WindowsSpyBlocke (400)
  [✗] List download failed: no cached list available

  [i] Target: r/master/data/hosts/spy.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://hostfiles.frogeye.fr/firstparty-trackers-hosts.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/DandelionSprout/adfilt/mas
  [✗] Status: https://raw.githubusercontent.com/DandelionSprout/adfilt/mas (400)
  [✗] List download failed: no cached list available

  [i] Target: ter/Alternate%20versions%20Anti-Malware%20List/AntiMalwareHo
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: sts.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast
  [✗] Status: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast (400)
  [✗] List download failed: no cached list available

  [i] Target: er/add.Risk/hosts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://urlhaus.abuse.ch/downloads/hostfile/
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://zerodot1.gitlab.io/CoinBlockerLists/hosts_browser
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://big.oisd.nl
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://nsfw.oisd.nl
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://video.orea.ph/oisd_big_abp.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://video.orea.ph/oisd_nsfw_abp.txt
  [✗] Invalid Target

  [✓] Building tree
  [✓] Swapping databases
  [✓] The old database remains available
-- Loading resources from /root/.sqliterc
-- Loading resources from /root/.sqliterc
  [i] Number of gravity domains: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
0        (adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
0        unique domains)
-- Loading resources from /root/.sqliterc
adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
Error: in prepare, near "domainlist_by_group": syntax error
  lue) VALUES ('gravity_count',adlist               domainlist_by_group  vw_grav
                                      error here ---^
-- Loading resources from /root/.sqliterc
  [i] Number of exact blacklisted domains: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
0
-- Loading resources from /root/.sqliterc
  [i] Number of regex blacklist filters: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
2
-- Loading resources from /root/.sqliterc
  [i] Number of exact whitelisted domains: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
1
-- Loading resources from /root/.sqliterc
  [i] Number of regex whitelist filters: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
2
  [✓] 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
[Sat Dec 09] root@sandalpi: ~$ pihole -v
  Pi-hole version is v5.17.2 (Latest: v5.17.2)
  web version is v5.21 (Latest: v5.21)
  FTL version is v5.23 (Latest: v5.23)
[Sat Dec 09] root@sandalpi: ~$ pihole -g -f
-- Loading resources from /root/.sqliterc
  [✓] Deleting existing list cache
  [i] Neutrino emissions detected...
  [✓] Pulling blocklist source list into range

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

  [i] Target: adlist               domainlist_by_group  vw_gravity
  [✗] Invalid Target

  [i] Target: adlist_by_group      gravity              vw_regex_blacklist
  [✗] Invalid Target

  [i] Target: client               group                vw_regex_whitelist
  [✗] Invalid Target

  [i] Target: client_by_group      info                 vw_whitelist
  [✗] Invalid Target

  [i] Target: domain_audit         vw_adlist
  [✗] Invalid Target

  [i] Target: domainlist           vw_blacklist
  [✗] Invalid Target

  [i] Target: address
  [✗] Invalid Target

  [i] Target: ------------------------------------------------------------
  [i] Status: Pending...Invalid option: ------------------------------------------------------------
Usage:  dig [@global-server] [domain] [q-type] [q-class] {q-opt}
            {global-d-opt} host [@local-server] {local-d-opt}
            [ host [@local-server] {local-d-opt} [...]]

Use "dig -h" (or "dig -h | more") for complete list of options
  [✗] Status: ------------------------------------------------------------ ()
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/StevenBlack/hosts/master/h
  [✗] Status: Not found
  [✗] List download failed: no cached list available

  [i] Target: osts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/PolishFiltersTeam/KADhosts
  [✗] Status: https://raw.githubusercontent.com/PolishFiltersTeam/KADhosts (400)
  [✗] List download failed: no cached list available

  [i] Target: /master/KADhosts.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast
  [✗] Status: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast (400)
  [✗] List download failed: no cached list available

  [i] Target: er/add.Spam/hosts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://adaway.org/hosts.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/anudeepND/blacklist/master
  [✗] Status: https://raw.githubusercontent.com/anudeepND/blacklist/master (400)
  [✗] List download failed: no cached list available

  [i] Target: /adservers.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/bigdargon/hostsVN/master/h
  [✗] Status: Not found
  [✗] List download failed: no cached list available

  [i] Target: osts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast
  [✗] Status: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast (400)
  [✗] List download failed: no cached list available

  [i] Target: er/add.2o7Net/hosts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/crazy-max/WindowsSpyBlocke
  [✗] Status: https://raw.githubusercontent.com/crazy-max/WindowsSpyBlocke (400)
  [✗] List download failed: no cached list available

  [i] Target: r/master/data/hosts/spy.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://hostfiles.frogeye.fr/firstparty-trackers-hosts.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/DandelionSprout/adfilt/mas
  [✗] Status: https://raw.githubusercontent.com/DandelionSprout/adfilt/mas (400)
  [✗] List download failed: no cached list available

  [i] Target: ter/Alternate%20versions%20Anti-Malware%20List/AntiMalwareHo
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: sts.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast
  [✗] Status: https://raw.githubusercontent.com/FadeMind/hosts.extras/mast (400)
  [✗] List download failed: no cached list available

  [i] Target: er/add.Risk/hosts
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://urlhaus.abuse.ch/downloads/hostfile/
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://zerodot1.gitlab.io/CoinBlockerLists/hosts_browser
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://big.oisd.nl
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://nsfw.oisd.nl
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://video.orea.ph/oisd_big_abp.txt
  [✗] Invalid Target

  [i] Target:
  [✗] Status: Connection Refused
  [✗] List download failed: no cached list available

  [i] Target: https://video.orea.ph/oisd_nsfw_abp.txt
  [✗] Invalid Target

  [✓] Building tree
  [✓] Swapping databases
  [✓] The old database remains available
-- Loading resources from /root/.sqliterc
-- Loading resources from /root/.sqliterc
  [i] Number of gravity domains: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
0        (adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
0        unique domains)
-- Loading resources from /root/.sqliterc
adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
Error: in prepare, near "domainlist_by_group": syntax error
  lue) VALUES ('gravity_count',adlist               domainlist_by_group  vw_grav
                                      error here ---^
-- Loading resources from /root/.sqliterc
  [i] Number of exact blacklisted domains: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
0
-- Loading resources from /root/.sqliterc
  [i] Number of regex blacklist filters: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
2
-- Loading resources from /root/.sqliterc
  [i] Number of exact whitelisted domains: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
1
-- Loading resources from /root/.sqliterc
  [i] Number of regex whitelist filters: adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
COUNT(*)
--------
2
  [✓] 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
[Sat Dec 09] root@sandalpi: ~$

Similar to this ticket: Adlist connections refused, no cached list downloaded - #7 by dgani

curl --connect-timeout 10 -s -L --compressed -w '%{http_code}\n' https://raw.githubusercontent.com/StevenBlack/hosts/master/hosts

Gives a 200 , all the other lists can be also be accessed cleanly via curl

jfb · December 9, 2023, 12:57am

Please upload a debug log and post just the token URL that is generated after the log is uploaded by running the following command from the Pi-hole host terminal:

pihole -d

or do it through the Web interface:

Tools > Generate Debug Log

sean311 · December 9, 2023, 1:22am

*****************************************************************
*****************************************************************

[✓] Your debug token is: https://tricorder.pi-hole.net/WFhXn3xi/
[i] Logs are deleted 48 hours after upload.

*****************************************************************
*****************************************************************

Thank you for fast reply !

jfb · December 9, 2023, 2:20am

What is the output of this command from the Pi terminal?

pihole-FTL sqlite3 gravity.db "PRAGMA integrity_check"

sean311 · December 9, 2023, 3:22am

[Sat Dec 09] root@sandalpi: ~$ pihole-FTL sqlite3 gravity.db "PRAGMA integrity_check"
-- Loading resources from /root/.sqliterc
integrity_check
---------------
ok
[Sat Dec 09] root@sandalpi: ~$

chrislph · December 9, 2023, 3:48am

When I try this on mine I get the single word 'ok' and can put anything in the filename and it still says 'ok'. If I put in the full path to the database it takes a few minutes then says 'ok'.

Could you retry it and explicitly name the database please?

pihole-FTL sqlite3 /etc/pihole/gravity.db "PRAGMA integrity_check"

I note your says that extra text, is there something else running sqlite on your install? May not be relevant, just curious, the devs will know more.

-- Loading resources from /root/.sqliterc
integrity_check
---------------

sean311 · December 9, 2023, 3:49am

Here with the full path name:

[Sat Dec 09] root@sandalpi: ~$ pihole-FTL sqlite3 /etc/pihole/gravity.db "PRAGMA integrity_check"
-- Loading resources from /root/.sqliterc
adlist               domainlist_by_group  vw_gravity
adlist_by_group      gravity              vw_regex_blacklist
client               group                vw_regex_whitelist
client_by_group      info                 vw_whitelist
domain_audit         vw_adlist
domainlist           vw_blacklist
integrity_check
---------------
ok
[Sat Dec 09] root@sandalpi: ~$

sean311 · December 9, 2023, 3:51am

also about SQLITE3

[Sat Dec 09] root@sandalpi: ~$ sqlite3  -version
-- Loading resources from /root/.sqliterc
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1
[Sat Dec 09] root@sandalpi: ~$

chrislph · December 9, 2023, 3:55am

It looks like that's where that text is coming from then. For reference mine gives:

$ pihole-FTL sqlite3 /etc/pihole/gravity.db "PRAGMA integrity_check"
ok

What is in that config file?

sudo cat /root/.sqliterc

sean311 · December 9, 2023, 3:58am

@chrisiph can you please show the sqlite3 -version for your system ?

sean311 · December 9, 2023, 3:59am

[Sat Dec 09] root@sandalpi: ~$ sudo cat /root/.sqliterc
.mode column
.headers on
.tables
[Sat Dec 09] root@sandalpi: ~$

chrislph · December 9, 2023, 4:09am

Same as yours

$ sqlite3 -version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1

However Pi-hole has its own embedded sqlite. What do you get for the command below? Do you see that reference to the config file as well as the below?

$ pihole-FTL sqlite3 -version
3.42.0 2023-05-16 12:36:15 831d0fb2836b71c9bc51067c49fee4b8f18047814f2ff22d817d25195cf350b0

I'm wondering if that .sqliterc file is being picked up by Pi-hole's sqlite3 and messing it up. Don't make any changes – this one is best diagnosed by a developer

sean311 · December 9, 2023, 4:14am

[Sat Dec 09] root@sandalpi: ~$ pihole-FTL sqlite3 -version
-- Loading resources from /root/.sqliterc
3.42.0 2023-05-16 12:36:15 831d0fb2836b71c9bc51067c49fee4b8f18047814f2ff22d817d25195cf350b0
[Sat Dec 09] root@sandalpi: ~$

Perhaps you are on right track about the .sqliterc . I do not know.

Those are setting prefs that other SQLITE apps use on my system. If pi-hole is using same it should EITHER tolerate whatever is set globally OR have it's own settings if it needs it to be a certain way. My understanding is that those settings only are used when interactive mode SQLITE is being used. It was interesting that .tables seems to return all the table names for every command "eg integrity". Perhaps what is being returned is confusing pi-hole

sean311 · December 9, 2023, 5:03am

Yes - that is the problem !

When I remove (temporarily - just for pi-hole to run ) those commands from .sqliterc , then the command pihole -g does run with no error and downloads the lists as it should.

But frankly I am AMAZED that pi-hole breaks (ie does not tolerate) such settings. I would think is relatively simple matter for pi-hole to explicitly set .headers off and .mode table or whatever is needed for pi-hole, rather than rely on the global defaults Especially if those global defaults can have the result of preventing the pi-hole gravity update from running properly

rdwebdesign · December 9, 2023, 5:13am

Thanks for testing.

I just reproduced the issue inside a docker container. I created a .sqliterc file and the debug log and gravity showed the same kind of output you saw.

Pinging @DL6ER to see what exactly is causing the issue.

chrislph · December 9, 2023, 5:21am

This may be related

https://github.com/pi-hole/pi-hole/issues/4650

rdwebdesign · December 9, 2023, 5:24am

I think it happens here because Pi-hole is running under root user.

sean311 · December 9, 2023, 5:34am

yes - that seems to be the reason

DL6ER · December 9, 2023, 12:37pm

Yes. You instruct sqlite3 to return all tables that exist in a database. Always. This confuses the scripts that are expecting a simple number being returned when they call

SELECT COUNT(1) FROM gravity;

or similar. Anything on your system using sqlite3 will fail in the same way, this is due to your particular local configuration and not generally a Pi-hole mistake.

But I do see we should be resistant against this exact case of strange user system configuration.

No. sqliterc is always processed. The only difference is that in interactive mode you also get a message being explicit about this:

-- Loading resources from <path>

Yes, we need to change all invocations of pihole-FTL sqlite3 to include -batch -init /dev/null, see

$ cat ~/.sqliterc 
.mode column
.headers on
.tables

$ sqlite3 /etc/pihole/gravity.db "SELECT COUNT(1) FROM gravity;"
adlist               domainlist           vw_antigravity     
adlist_by_group      domainlist_by_group  vw_blacklist      
antigravity          gravity              vw_gravity         
client               group                vw_regex_blacklist 
client_by_group      info                 vw_regex_whitelist 
domain_audit         vw_adlist            vw_whitelist       
COUNT(1)
--------
1483703

$ sqlite3 /etc/pihole/gravity.db -batch -init /dev/null "SELECT COUNT(1) FROM gravity;"
1483703

sean311 · December 9, 2023, 3:18pm

Right ... It is more than just .tables that cause a problem. Also .headers on and possibly even .mode column too.

So you are right, it seems that those extra parms -batch -init /dev/null should be included each time