Unable to prepare statement: 1, no such table: query_storage in /var/www/html/admin/api_db.php on line 94

Expected Behaviour:

I expected reporting stuff from long-term database to work.

Actual Behaviour:

Using pihole in a Docker container on Synology. Set it up and it runs well. Got Error while loading data from the long-term database. Increased PHP memory from 128M -> 512M. Now I'm getting an error in the log that says Unable to prepare statement: 1, no such table: query_storage.

Debug Token:

https://tricorder.pi-hole.net/qrWcAnO7/

Re-run the debug log with the following command (this will check database integrity):

pihole -d --check_database

And upload this log and post the new token.

https://tricorder.pi-hole.net/OX6m7L2a/

How did you start your container?

Please post your compose file or docker run command used to start it.

I have no docker run or docker compose. I'm on Synology and I used their GUI. It's not like I haven't used docker run or compose before, I just didn't for this container.

Synology does have an export option for containers but it exports a JSON file. Here's the file:

{
   "CapAdd" : null,
   "CapDrop" : null,
   "cmd" : "",
   "cpu_priority" : 50,
   "enable_publish_all_ports" : false,
   "enable_restart_policy" : false,
   "enable_service_portal" : null,
   "enabled" : true,
   "env_variables" : [
      {
         "key" : "PATH",
         "value" : "/opt/pihole:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
      },
      {
         "key" : "phpver",
         "value" : "php"
      },
      {
         "key" : "PHP_ERROR_LOG",
         "value" : "/var/log/lighttpd/error-pihole.log"
      },
      {
         "key" : "IPv6",
         "value" : "True"
      },
      {
         "key" : "S6_KEEP_ENV",
         "value" : "1"
      },
      {
         "key" : "S6_BEHAVIOUR_IF_STAGE2_FAILS",
         "value" : "2"
      },
      {
         "key" : "S6_CMD_WAIT_FOR_SERVICES_MAXTIME",
         "value" : "0"
      },
      {
         "key" : "FTLCONF_LOCAL_IPV4",
         "value" : "0.0.0.0"
      },
      {
         "key" : "FTL_CMD",
         "value" : "no-daemon"
      },
      {
         "key" : "DNSMASQ_USER",
         "value" : "pihole"
      },
      {
         "key" : "WEBPASSORD",
         "value" : "**********"
      },
      {
         "key" : "WEB_PORT",
         "value" : "8001"
      },
      {
         "key" : "DNSMASQ_LISTENING",
         "value" : "local"
      },
      {
         "key" : "ServerIP",
         "value" : "192.168.86.31"
      }
   ],
   "exporting" : false,
   "id" : "1cdacf78cbf22cac958371588ea796240318ebad54ce01e6522a924b9a0b5ff9",
   "image" : "pihole/pihole:latest",
   "is_ddsm" : false,
   "is_package" : false,
   "links" : [],
   "memory_limit" : 0,
   "name" : "pihole",
   "network" : [
      {
         "driver" : "host",
         "name" : "host"
      }
   ],
   "network_mode" : "host",
   "port_bindings" : [],
   "privileged" : false,
   "shortcut" : {
      "enable_shortcut" : false,
      "enable_status_page" : false,
      "enable_web_page" : false,
      "web_page_url" : ""
   },
   "use_host_network" : true,
   "volume_bindings" : [
      {
         "host_volume_file" : "/docker/pihole/dnsmasq.d",
         "mount_point" : "/etc/dnsmasq.d",
         "type" : "rw"
      },
      {
         "host_volume_file" : "/docker/pihole/pilhole",
         "mount_point" : "/etc/pihole",
         "type" : "rw"
      },
      {
         "host_volume_file" : "/docker/pihole/php/user.ini",
         "mount_point" : "/var/www/html/.user.ini",
         "type" : "rw"
      }
   ]
}

Why are you mounting a file at /var/www/html/.user.ini?

Usually PHP reads user.ini files, not .user.ini.
(I think this file will simply be ignored, unless you make changes to the php.ini file)

I saw somewhere where it said it was /var/www/html/.user.ini. I thought it was strange too but went with it. When I did mount that file the verbiage about memory disappeared from the error message and it pointed to the next problem about the missing table so I figured I got it right. I could remove the '.' if you think that will help. The file only contained:

; Note this should be "mounted" to /var/www/html/.user.ini in the Docker
; configuration.
;
; Maximum amount of memory a script may consume
; http://php.net/memory-limit
memory_limit = 512M

It doesn't seem you need this .user.ini file. There are no memory issues on the log.


Was the host directory empty when you started the container or did it have files from a previous installation?
If you have files from a previous version, what Pi-hole version were you using before?

Should that pilhole be there or is it a typo?

1 Like

I think this is the only version of pihole I have used.

In the past, however, I was trying to use this bash script and cronjob in an attempt to use pihole to block YouTube videos. Essentially it examined the pihole log and extracted hostnames that matched a regex for googlevideo.com (something like /rr.*googlevideo.com/. It then added them to a file that was in dnsmasq.d so that those would be blocked too. It didn't work very well and after determining that this would never work well I removed it from the docker container. I had gone into the docker container and installed cron and vim to do this and I had mounted the /etc/pihole/dnsmasq.d and /etc/pihole/pihole directories as well as this /var/www/html/.user.ini file so I could manage my changes from the host. I may have taken a copy of what was in /etc/pihole and /etc/dnsmasq.d and copied them out of the container so I could mount those directories. I thought I that I removed all of my changes after deciding to not use this YouTube blocker script and I signed up for YouTube Premium (admitting defeat on blocking those ads).

If there is some method to get back to stock for these directories then let me know.

I think you have a database issue:

  • The web server log is showing errors like:

    Unable to prepare statement: 1, no such table: query_storage

  • And FTL.log has many errors like:

    SQLite3 message: no such column: aliasclient_id in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)

These messages should never happen in normal circumstances.

I only see 2 possibilities:

  • you have a broken database (but the log shows the database integrity is OK); or
  • you are trying to use an old database (from very old Pi-hole) with a current Pi-hole version.
    The query_storage table didn't exist in old Pi-hole database, but this was changed a long time ago (I think around 3 years ago).

Are you using old files (maybe from an old backup)?

I only installed this pihole docker thing a few days ago. As I said I tried to insert this YouTube ad blocker script thing and decided to back that out. I think I did a docker pull to update the container at one point. I definitely did not do anything with pihole 3 years ago... perhaps 1 or 2 weeks ago was the first time I started playing with pihole. As such I have no backups of any dbs.

IIRC neither /etc/pihole nor /etc/dnsmasq.d were previously mounted. So then if I remove the volume mounts from the docker container I should be back to normal (perhaps just missing the adlists I configured) since docker volume mounts are essentially mount overs of a directory. OK did that and I see the old contents of /etc/pihole and /etc/dnsmasq.d in the container. I re-added my adlists and I'm back up. But I still see the error about PHP memory limit. I'm gonna re-add my mount for the php.ini file.

OK, still getting the PHP memory error.

Here's the new config in JSON:

{
   "CapAdd" : null,
   "CapDrop" : null,
   "cmd" : "",
   "cpu_priority" : 50,
   "enable_publish_all_ports" : false,
   "enable_restart_policy" : false,
   "enable_service_portal" : null,
   "enabled" : true,
   "env_variables" : [
      {
         "key" : "PATH",
         "value" : "/opt/pihole:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
      },
      {
         "key" : "phpver",
         "value" : "php"
      },
      {
         "key" : "PHP_ERROR_LOG",
         "value" : "/var/log/lighttpd/error-pihole.log"
      },
      {
         "key" : "IPv6",
         "value" : "True"
      },
      {
         "key" : "S6_KEEP_ENV",
         "value" : "1"
      },
      {
         "key" : "S6_BEHAVIOUR_IF_STAGE2_FAILS",
         "value" : "2"
      },
      {
         "key" : "S6_CMD_WAIT_FOR_SERVICES_MAXTIME",
         "value" : "0"
      },
      {
         "key" : "FTLCONF_LOCAL_IPV4",
         "value" : "0.0.0.0"
      },
      {
         "key" : "FTL_CMD",
         "value" : "no-daemon"
      },
      {
         "key" : "DNSMASQ_USER",
         "value" : "pihole"
      },
      {
         "key" : "WEBPASSORD",
         "value" : "***redacted***"
      },
      {
         "key" : "WEB_PORT",
         "value" : "8001"
      },
      {
         "key" : "DNSMASQ_LISTENING",
         "value" : "local"
      },
      {
         "key" : "ServerIP",
         "value" : "192.168.86.31"
      }
   ],
   "exporting" : false,
   "id" : "1cdacf78cbf22cac958371588ea796240318ebad54ce01e6522a924b9a0b5ff9",
   "image" : "pihole/pihole:latest",
   "is_ddsm" : false,
   "is_package" : false,
   "links" : [],
   "memory_limit" : 0,
   "name" : "pihole",
   "network" : [
      {
         "driver" : "host",
         "name" : "host"
      }
   ],
   "network_mode" : "host",
   "port_bindings" : [],
   "privileged" : false,
   "shortcut" : {
      "enable_shortcut" : false,
      "enable_status_page" : false,
      "enable_web_page" : false,
      "web_page_url" : ""
   },
   "use_host_network" : true,
   "volume_bindings" : [
      {
         "host_volume_file" : "/docker/pihole/php/user.ini",
         "mount_point" : "/var/www/html/user.ini",
         "type" : "rw"
      }
   ]
}

And user.ini contains:

Jupiter:docker exec -it pihole cat /var/www/html/user.ini
; Note this should be "mounted" to /var/www/html/user.ini in the Docker
; configuration.
;
; Maximum amount of memory a script may consume
; http://php.net/memory-limit
memory_limit = 512M
Jupiter:

Should/Can I just reinitialize the database somehow?

Adding on here. I decided to rebuild this pihole from scratch since I don't have that much history in the database and obviously something is screwy. The only volume mount I'm using is for the php.ini file to set the memory to 512M. Seems to be working.

I also don't have a lot of adlists so I've re-created them and am presently out looking for a few more good lists.

Ah yes. Now it working. Thanks guys!

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