gravity.sh error caused by ~/.sqliterc

[jniggemann]

Versions

Pi-hole version is v5.9 (Latest: v5.9)
AdminLTE version is v5.11 (Latest: v5.11)
FTL version is v5.14 (Latest: v5.14)

Platform

Debian 11.2 in LXC on Proxmox 7

Expected behavior

pihole updateGravity runs without issues

Actual behavior / bug

pihole updateGravity fails with

  [i] Target: address
  [✗] Invalid Target

  [i] Target: ----------------------------------------------------------------
  [✗] Status: ---------------------------------------------------------------- ()
  [✗] List download failed: no cached list available

Steps to reproduce

The cause is an existing .sqliterc in the pihole users $home.

Steps to reproduce the behavior:

1. Create .sqliterc in pihole users $home with content .headers on
2. execute pihole updateGravity

Additional context

I did not find good documentation on how to prevent sqlite3 from loading ~/.sqliterc, but it can be overridden using --init parameter.
sqlite3 --init /dev/null worked for me (ex. on line 380 in gravity.sh).

[dschaper]

Debug log please.

[jniggemann]

Debug log please.

Thought this was clear, but here you go https://tricorder.pi-hole.net/bjC86KUS/

sqlite3 loads ~./sqliterc, example of error here (gravity.sh)

  # Retrieve source URLs from gravity database
  # We source only enabled adlists, SQLite3 stores boolean values as 0 (false) or 1 (true)
  mapfile -t sources <<< "$(pihole-FTL sqlite3 "${gravityDBfile}" "SELECT address FROM vw_adlist;" 2> /dev/null)"
  mapfile -t sourceIDs <<< "$(pihole-FTL sqlite3 "${gravityDBfile}" "SELECT id FROM vw_adlist;" 2> /dev/null)"

Remedy: Either do not load sqlite init files or explicitly set needed settings.

[yubiuser]

Thanks for the report.

Loading the ~/.sqliterc happens deep down in sqlite3 code and there is no config flag to disable it. As you pointed out, there is only one way to overwrite it: passing the --init flag.

Sure we could just remove the whole sqliterc code block when compiling the engine into FTL, but there will be users using pihole-FTL sqlite3 as a sqlite3 drop-in which also use ~/.sqliterc and therefore will be unhappy by our decision.

Adding --init /dev/null to every call could be a way to go ...

---

From the man pages

INIT FILE
       sqlite3 reads an initialization file to set the  configuration  of  the
       interactive  environment.   Throughout  initialization,  any previously
       specified setting can be overridden.  The sequence of initialization is
       as follows:

       o The default configuration is established as follows:

       mode            = LIST
       separator       = "|"
       main prompt     = "sqlite> "
       continue prompt = "   ...> "

       o  If the file ~/.sqliterc exists, it is processed first.  can be found
       in the user's home directory, it is read and processed.  It should gen‐
       erally only contain meta-commands.

       o If the -init option is present, the specified file is processed.

       o All 
```other command line options are processed.

[dschaper]

Can you explain why the pihole user has an rc file for sqlite to help us decide how to approach this configuration?

[jniggemann]

Loading the ~/.sqliterc happens deep down in sqlite3 code and there is no config flag to disable it. As you pointed out, there is only one way to overwrite it: passing the --init flag.

Yeah, the lack of a switch to disable loading the rcfile is really uncool.
As much as I like sqlite, I dislike the way the project is developed technically: I wanted to search upstream for a suitable feature request, but they don't even have a public bugtracker. Then they use this very unique SCM...

[jniggemann]

Can you explain why the pihole user has an rc file for sqlite to help us decide how to approach this configuration?

No reald good usecase, I had tested something where I intensively used sqlite and the rcfile was a leftover because it was convienient.

Nontheless I think we could add some resilience to the scripts if we include the --init flag.

[pralor-bot]

This issue has been mentioned on Pi-hole Userspace. There might be relevant details there:

https://discourse.pi-hole.net/t/all-adlist-connections-refused-no-cached-lists-available/66840/17

[yubiuser]

Will be fixed by pi-hole/FTL#1820 and corresponding core PR (to come)

[coder0107git]

Fixed by #5519.