Failed to truncate network_addresses table

[thejavascriptman]

Clicking the "Flush network table" button under settings does not work and shows a stepping, attempt to write a readonly database (8) error

This is a: Bug

Details

The full message as provided by Pi-hole's web UI is:

 Error
�[K [✗] Failed to truncate network_addresses table
Database location: /tmp/pihole-FTL.db
Output: Error: stepping, attempt to write a readonly database (8)

The issue happens when the pihole-FTL.db file is placed on a tmpfs directory like /dev/shm or /tmp

Related Issues

• I have searched this repository/Pi-hole forums for existing issues and pull requests that look similar

How to reproduce the issue

1. Environment data

• Operating System: Arch Linux
• Hardware: Desktop PC
• Kernel Architecture: x86/amd64
• Docker Install Info and version:
  • Software source: pacman
  • Supplimentary Software:
• Hardware architecture: x86

2. docker-compose.yml contents, docker run shell command, or paste a screenshot of any UI based configuration of containers here

docker run --rm --cap-add=NET_BIND_SERVICE --cap-add=NET_ADMIN --cap-add=CAP_SYS_NICE --cap-add=CHOWN -e WEBPASSWORD=1234 -e FTLCONF_DBFILE=/tmp/pihole-FTL.db -p 8080:80 --tmpfs /tmp --name pihole pihole/pihole:2022.09.4

3. any additional info to help reproduce
   Can be reproduced in 2 ways:

• Visit the web UI > go to Settings > Click "Flush network table"
• Run docker exec pihole pihole-FTL sqlite3 "/tmp/pihole-FTL.db" "DELETE FROM network_addresses"
  • If I add -u pihole the problem is gone

These common fixes didn't work for my issue

• I have tried removing/destroying my container, and re-creating a new container
• I have tried fresh volume data by backing up and moving/removing the old volume data
• I have tried running the stock docker run example(s) in the readme (removing any customizations I added)
• I have tried a newer or older version of Docker Pi-hole (depending what version the issue started in for me)
• I have tried running without my volume data mounts to eliminate volumes as the cause

If the above debugging / fixes revealed any new information note it here.
Add any other debugging steps you've taken or theories on root cause that may help.

• Tested pihole docker images: 2022.07.1, 2022.08.3, 2022.09.2, 2022.09.3, 2022.09.4
• The tmps path inside the container has more than enough space

  docker exec pihole df -h /tmp
  Filesystem      Size  Used Avail Use% Mounted on
  tmpfs            14G   80K   14G   1% /tmp
  

• Writing to tmp is allowed inside the container e.g. docker exec pihole touch /tmp/hello.txt works just fine
• As an additional precaution I made sure that Pi-hole was completely initialized (by checking the docker log) and I did a ls to the respective paths to check if the pihole-FTL.db existed

[rdwebdesign]

This looks like a permission error.
Apparently your user can't to access the directory, but pihole user can.

You probably need to set write permissions on the directory.

[thejavascriptman]

Thanks for the hint, indeed seems like a permission issue. The /tmp directory (mounted as tmpfs) is owned by root:root, but when I mount tmpfs at /run/pihole, the owner of that directory is pihole:pihole, and if store the FTL database at /run/pihole, the issue is gone.