Long-Term Data weirdness

Expected Behaviour:

Long Term Data should be storing all requests from all clients

Actual Behaviour:

Long term Data only shows data for approx 1 day, then stops storing activity. If I re-boot the pi, it works, but again for only approx 1 day, then stops

Iv’e searches many topics on this and can confirm…

PHP-SQLite3 is installed
the pi has 664 permissions on /etc/pihole/pihole-FTL.db
the pi has write permissions on same

If I use the “Query Log” and click “Show all”, then I can see everything, it’s just when using “Long Term Data” I get this weirdness.

Help please - it’s driving me nuts…

Welcome to the Pi-hole community, devbrodie. :slight_smile:

Please supply the token generated by

pihole -d

or do it through the Web interface:

Tools > Generate Debug Log



Illegal characters in some of your device names seem to cause database write errors, e.g. heth’s-tablet.
Note that a couple of Internet standards restrict hostnames to only contain letters ‘a’ through ‘z’ (in a case-insensitive manner), the digits ‘0’ through ‘9’ and the hyphen/minus character (’-’).

As this seems to disable the database connection in return, it may well be the root cause for lack of long-term database entries.

Removing the apostrophe from the name (as in heths-tablet) should fix this.

Pi-hole’s web UI does actively safeguard against invalid hostnames.
How did you provide the names for your devices?

OK - I’ll try renaming those devices - Thanks!

I named the devices by editing the hosts file on the Pi

Update - I renamed all devices I could find to remove any illegal characters.

My last entry in long term data was 3 days ago, yet in the query log, I see everything upto the last few minutes. Additionally, If I try to run a Long Term Data Query for a long date range (i.e. “This year”) I get an “Unexpected error”

Any ideas?

New token…


Three possible solution:

  1. Reject invalid host names, do never store them in the database
  2. Modify invalid host names, remove invalid characters and store what is left
  3. Accept invalid host names, store them in the database (and fix the bug we currently see)

We can achieve all three with small to medium code changes. No. 2 will be the most complicated one.


Hostnames with ' are considered valid by dnsmasq, so we should probably accept them as well. Working on no. 3 now…

Number 3 is now implemented in branch new/all_clients_network_table where we are doing some work on the code around the network table, anyways. The connected PR is

This will be fixed in Pi-hole v5.0 where we ensure that all data can contain correctly encoded UTF-8 characters.

So we allow and store invalid data? Seems to be the wrong approach in my view.

The chain of thing is as follows:

  1. FTL sees a request from some client
  2. FTL sends a PTR for this IP
  3. FTL receives a reply from someone (typically the embedded dnsmasq, could be some other source as well)
  4. FTL stores what it got in the database

I think it is okay to store what we received as reply to our PTR earlier? When we receive something “invalid”, the replying DNS server seems to be the one to blame.

If the PTR data (payload) is RFC compliant then store it. If the data is invalid then I wouldn’t keep it. There’s no use for it and the invalid payload may contain something we don’t want.

Next iteration: Host names are now validated for…

  1. Length (maximum length is given by MAXHOSTNAMELEN given by the system through asm-generic/param.h, defaulting to 64, can be overwritten during compilation)

    If the maximum length is exceeded, FTL truncates the hostname and logs:

    [2020-03-23 14:59:00.475 14511] WARN: Hostname of client too long, truncating to 64 chars!

    Note: The truncated version of the host name is used

  2. Valid characters (we accept A-Z a-z 0-9 _ - .)

    If invalid characters or binary blobs are found, FTL does not import the hostname and logs:

    [2020-03-23 14:59:00.745 14511] WARN: Hostname of client contains invalid character: � (char code 195)

    (I added the German umlaut ö as example here)

    Note: Any host name containing invalid characters is discarded entirely (as if no host name is available)

I pushed my changes (branch new/all_clients_network_table).

The Valid Characters seems logical as long as IDNs are converted to punycode before validation.

That’s in the responsibility of the client If I recall correctly what was in the RFC. I just checked, Firefox correctly does this (try www.allestörungen.de). If you directly fire this query through dig, it will not be converted. However, FTL A.K.A. dnsmasq is still able to handle them and we’re only talking about client host names here. This validation does not touch the regular DNS server operation in any way.

This code has been merged into the regular beta code so everyone will benefit. Please re-checkout the beta branches to continue receiving updates and new features:

pihole checkout ftl release/v5.0

Thanks for all your contributions!