Attempt to write a readonly database

Help Community Help
1

The issue I am facing:
Adding white/black list through the UI results in an error in red:

Error, something went wrong!
While executing INSERT OR IGNORE: attempt to write a readonly database
Added 0 out of 1 domains

Details about my system:
Pihole container is running in nomad using the docker driver. Config directory (/etc/pihole) is a persistent volume on glusterfs.

Debug log: https://tricorder.pi-hole.net/wF4dx0gb/

Relevant part of the nomad job config:

      task "pihole" {
        driver = "docker"
        shutdown_delay = "${instance.value.shutdown_delay}"

        volume_mount {
          volume = "config"
          destination = "/etc/pihole"  # <-- this is glusterfs
        }
        config {
          image        = "pihole/pihole:2023.11.0"
          volumes = [
            "local/etc-dnsmasq.d/00-custom.conf:/etc/dnsmasq.d/00-custom.conf",
            "local/dnsmasq.local.hosts:/etc/dnsmasq.local.hosts",
          ]
        }

        env {
          WEB_PORT          = "80"
          WEBPASSWORD       = "${instance.value.password}"
          PIHOLE_DNS_       = "8.8.8.8;8.8.4.4"
        }

What I have changed since installing Pi-hole:
Nothing - I am using the job configuration and have changed nothing else.

More information
I have tried running the pihole commands in the container directly and they are successful:

As user pihole, I can run pihole -w XXX successfully.

As user www-data I can run sudo /usr/local/bin/pihole -w YYY successfully.

When adding a domain through the UI, I get the following

1701719145|172.17.17.190|172.17.17.50|POST /admin/scripts/pi-hole/php/groups.php HTTP/1.1|200|174
2023-12-04 20:45:45: mod_fastcgi.c.487) FastCGI-stderr:PHP Warning:  SQLite3Stmt::execute(): Unable to execute statement: attempt to write a readonly database in /var/www/html/admin/scripts/pi-hole/php/groups.php on line 661

The daemons appear to be running as the correct users:

root@503d1d9e64d5:/var/log# ps auwwxf
USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root      139122  0.0  0.0   3952  3120 pts/0    Ss   20:35   0:00 /bin/bash
root      146226  0.0  0.0   6440  2524 pts/0    R+   21:33   0:00  \_ ps auwwxf
root           1  0.0  0.0    200    68 ?        Ss   Dec03   0:00 /package/admin/s6/command/s6-svscan -d4 -- /run/service
root          18  0.0  0.0    204    68 ?        S    Dec03   0:00 s6-supervise s6-linux-init-shutdownd
root          20  0.0  0.0    192     4 ?        Ss   Dec03   0:00  \_ /package/admin/s6-linux-init/command/s6-linux-init-shutdownd -c /run/s6/basedir -g 3000 -C -B
root          26  0.0  0.0    204    64 ?        S    Dec03   0:00 s6-supervise lighttpd-error-log
root          27  0.0  0.0    204    68 ?        S    Dec03   0:00 s6-supervise s6rc-oneshot-runner
root          39  0.0  0.0    180     4 ?        Ss   Dec03   0:00  \_ /package/admin/s6/command/s6-ipcserverd -1 -- /package/admin/s6/command/s6-ipcserver-access -v0 -E -l0 -i data/rules -- /package/admin/s6/command/s6-sudod -t 30000 -- /package/admin/s6-rc/command/s6-rc-oneshot-run -l ../.. --
root          28  0.0  0.0    204    68 ?        S    Dec03   0:00 s6-supervise lighttpd
root         227  0.0  0.0   3688  2680 ?        Ss   Dec03   0:00  \_ bash ./run
www-data     251  0.0  0.0   5252  3308 ?        S    Dec03   0:07      \_ lighttpd -D -f /etc/lighttpd/lighttpd.conf
www-data     289  0.0  0.2 198920 20876 ?        Ss   Dec03   0:00          \_ /usr/bin/php-cgi
www-data     293  0.0  0.2 199284 17632 ?        S    Dec03   0:00              \_ /usr/bin/php-cgi
www-data     294  0.0  0.2 199276 18176 ?        S    Dec03   0:00              \_ /usr/bin/php-cgi
www-data     295  0.0  0.2 199416 18844 ?        S    Dec03   0:01              \_ /usr/bin/php-cgi
www-data     296  0.0  0.2 199396 16884 ?        S    Dec03   0:03              \_ /usr/bin/php-cgi
root          29  0.0  0.0    204    68 ?        S    Dec03   0:00 s6-supervise lighttpd-access-log
root          30  0.0  0.0    204    68 ?        S    Dec03   0:00 s6-supervise cron
root          57  0.0  0.0   3688  2536 ?        Ss   Dec03   0:00  \_ bash ./run
root          61  0.0  0.0   3688  2164 ?        S    Dec03   0:00      \_ /usr/sbin/cron -f
root          31  0.0  0.0    204    64 ?        S    Dec03   0:00 s6-supervise s6rc-fdholder
root          32  0.0  0.0    204    68 ?        S    Dec03   0:00 s6-supervise pihole-FTL
root         222  0.0  0.0   3688  2584 ?        Ss   Dec03   0:00  \_ bash ./run
pihole       275  0.0  0.0   3688  2480 ?        S    Dec03   0:00      \_ /bin/bash -c /usr/bin/pihole-FTL no-daemon >/dev/null 2>&1
pihole       276  0.2  0.1 580840 13364 ?        Sl   Dec03   3:34          \_ /usr/bin/pihole-FTL no-daemon

Based on some entries in my debug log, I think I may be affected by sqlite's incompatibility with network file systems (from Write-Ahead Logging)

   [2023-12-04 04:00:02.358 276/T324] SQLite3 message: database is locked in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (5)
   [2023-12-04 04:00:02.358 276/T324] getNameFromIP("172.17.17.186") - SQL error prepare: database is locked
   [2023-12-04 04:22:00.353 276/T322] SQLite3 message: disk I/O error in "DELETE FROM network_addresses WHERE lastSeen < 1670124120;" (2058)
   [2023-12-04 04:22:00.353 276/T322] ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1670124120;" failed: disk I/O error (SQLITE_IOERR_UNLOCK)
   [2023-12-04 04:41:00.253 276/T322] SQLite3 message: disk I/O error in "DELETE FROM network_addresses WHERE lastSeen < 1670125260;" (2058)
   [2023-12-04 04:41:00.253 276/T322] ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1670125260;" failed: disk I/O error (SQLITE_IOERR_UNLOCK)

However, if is was a WAL issue, why would running pihole on the command line work correctly?

I am happy to test disabling the WAL, but am not sure how to do so. could it be a database path paramater maybe?

Any ideas on what else to look at?

2

This is a symptom of something else causing a readonly issue, not the problem itself.

Your log shows a disk I/O error (SQLITE_IOERR_UNLOCK):

   [2023-12-04 18:23:00.182 276/T322] SQLite3 message: disk I/O error in "DELETE FROM network_addresses WHERE lastSeen < 1670174580;" (2058)
   [2023-12-04 18:23:00.182 276/T322] ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1670174580;" failed: disk I/O error (SQLITE_IOERR_UNLOCK)

This seems to be an issue on the OS level, or maybe hardware level:

  • From SQLITE docs:

    The SQLITE_IOERR result code says that the operation could not finish because the operating system reported an I/O error

I'm not familiar with glusterfs. Are you using a remote mount to store the docker volume files?

3

I agree that this is an OS-level issue or more likely a filesystem capability issue. Glusterfs is a cluster network filesystem which I am using for persistent volumes, and I think it probably does not support some ioctls which sqlite uses.

I find it strange that cli commands always work correctly while the web UI always fails. This point towards slightly different handling of the sqlite DB connection between the two, which I why I would like to try experimenting with different DB options (like disabling the WAL as an example). Apart from modifying the code I can't (yet) see how to do this through the environment variables or settings.

4

The web interface commands are executed by PHP, but PHP needs to open a shell and execute pihole commands inside this shell.
I'm not sure how the permissions would be affected in your specific filesystem, specially inside a docker container. Docker probably uses some overlay filesystem in this case, making things more complex.

5

You probably should reconsider that choice for a SQLite3 database.

SQLite3's write locking is based on locking database files, and as such is depending on file locking routines of the underlying OS.
Unfortunately, OS level file locking is known to be somewhat buggy across all OSs when it comes to network shares, which means you are bound to provoke database corruption when storing SQLite3 database file on NFS shares.
I guess that similar would be true for a less common fs as glusterfs.

SQLite3's documentation for hosting database files on NFS shares reads as follows:

Your best defense is to not use SQLite for files on a network filesystem.

6

I agree with your assessment that using glusterfs as a sqlite backend store is probably going to cause issues. Ensuring that I only ever access it from a single host at a time (which is what I am doing) will mitigate that to some degree. I will investigate this further separately.

However, I am still looking into the difference in behavior between the UI and the CLI. @ rdwebdesign, you said above that the UI executes the cli command to manipulate the database, but this is not what I see. The UI actually directly attempts to write to the sqlite database, and this is probably where the mismatch in behavior is coming from.

When adding through the UI, it POSTs to /admin/scripts/pi-hole/php/groups.php, and this script (web/scripts/pi-hole/php/groups.php at be05b0f61d3fcf796dae4cc3f89f8540b2359325 · pi-hole/web · GitHub) does direct database manipulation. In fact, if I just ran chmod 666 gravity.db as a test, and the UI correctly can add a domain to the database.

This points to some potentially odd filesystem permission problems because:

  • gravity.db permissions allow group pihole to write:

    • # getfacl gravity.db 

# file: gravity.db
# owner: pihole
# group: pihole
user::rw-
group::rw-
other::r--

  • www-data is in the pihole group:

    • # getent group pihole
pihole:x:1000:www-data

  • the lighttpd and php-cgi processes have pihole as a supplementary group:

    • # ps -o group,supgrp `pidof php-cgi lighttpd`
GROUP    SUPGRP
www-data www-data,pihole
www-data www-data,pihole
www-data www-data,pihole
www-data www-data,pihole
www-data www-data,pihole
www-data www-data,pihole

However the web server cannot open gravity.db read-write (unless I chmod 666 the file)

I thought that this may be something specific to my setup, but solving this may help other people in a similar situation, however, this is looking like a glusterfs-specific issue. A bit of searching has lead me to a similar unanswered question (https://www.linuxquestions.org/questions/centos-111/user-supplementary-groups-and-glusterfs-mounted-via-fuse-4175718404/), so I guess I will continue looking into this as well by myself.

My solution to the problem will likely be to move to local host-only volumes, which on startup copy from a seed volume that contains my white/blacklist customizations.

7

You are correct. Some other commands use the command line to execute pihole and I thought this was related, but that's not the case here.

I still can't say what is causing the different behavior.

As Bucking_Horn said, the best solution would be to move your volumes to a different file system.

8

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