Long Term Data Not Displaying

#1

DeHakkelaar,

I have another issue which i have just discovered.

It seems my Long Term data is not populating even though my dashboard is.

Screen shots.

I have followed all the advice from this post

This is the output from what i have done.
pi@raspberrypi:~ $ ls -al /etc/pihole/pihole-FTL.db
-rw-r–r-- 1 pihole pihole 5263360 Mar 17 09:06 /etc/pihole/pihole-FTL.db
pi@raspberrypi:~ $ sudo chmod 664 /etc/pihole/pihole-FTL.db
pi@raspberrypi:~ $ ls -al /etc/pihole/pihole-FTL.db
-rw-rw-r-- 1 pihole pihole 5263360 Mar 17 09:06 /etc/pihole/pihole-FTL.db
pi@raspberrypi:~ $ sudo service pihole-FTL stop
pi@raspberrypi:~ $ sudo chown root:pihole /etc/pihole/pihole-FTL.db
pi@raspberrypi:~ $ sudo service pihole-FTL start
pi@raspberrypi:~ $ stat /etc/pihole/pihole-FTL.db
File: /etc/pihole/pihole-FTL.db
Size: 5271552 Blocks: 10304 IO Block: 4096 regular file
Device: b307h/45831d Inode: 298718 Links: 1
Access: (0664/-rw-rw-r–) Uid: ( 0/ root) Gid: ( 996/ pihole)
Access: 2019-03-12 19:00:21.225856045 +0000
Modify: 2019-03-17 09:07:05.479764298 +0000
Change: 2019-03-17 09:07:16.149733329 +0000
Birth: -
pi@raspberrypi:~ $

It hasn’t fixed the problem. Can you offer any advice?

Thanks

Dan

0 Likes

SqLite3 and json_encode() fault
#2

Ownership should be pihole/pihole and permissions 644:

pi@noads:~ $ stat /etc/pihole/pihole-FTL.db
  File: /etc/pihole/pihole-FTL.db
  Size: 101023744       Blocks: 197320     IO Block: 4096   regular file
Device: b302h/45826d    Inode: 135081      Links: 1
Access: (0644/-rw-r--r--)  Uid: (  999/  pihole)   Gid: (  996/  pihole)
Access: 2018-08-14 18:55:48.409037135 +0200
Modify: 2019-03-17 11:21:03.736205585 +0100
Change: 2019-03-17 11:21:03.736205585 +0100
 Birth: -

Are the DNS queries logged (was question during install I believe) ?

grep QUERY_LOGGING /etc/pihole/setupVars.conf

How is privacy level configured:

grep PRIVACYLEVEL /etc/pihole/pihole-FTL.conf

Privacy level can be configured here:

http://pi.hole/admin/settings.php?tab=privacy

Is the queries table populated in the main dbase ?

echo '.dump queries' | sqlite3 /etc/pihole/pihole-FTL.db | grep ',15528'

Above I am grepping for UNIX epoch date/time for today…ish:

https://www.epochconverter.com/

0 Likes

#3

pi@raspberrypi:~ $ sudo chmod 644 /etc/pihole/pihole-FTL.db
pi@raspberrypi:~ $ ls -al /etc/pihole/pihole-FTL.db
-rw-r–r-- 1 root pihole 5664768 Mar 17 12:50 /etc/pihole/pihole-FTL.db
pi@raspberrypi:~ $ grep QUERY_LOGGING /etc/pihole/setupVars.conf
QUERY_LOGGING=true
pi@raspberrypi:~ $ grep PRIVACYLEVEL /etc/pihole/pihole-FTL.conf
PRIVACYLEVEL=0
pi@raspberrypi:~ $

When i issued that last command it dumped loads of data.

0 Likes

#4

Ownership should be pihole/pihole:

sudo chown pihole:pihole /etc/pihole/pihole-FTL.db

sudo service pihole-FTL restart

stat /etc/pihole/pihole-FTL.db

Check date/time:

date

The rest looks good.
Try post a token here for the devs to have a looksee:

0 Likes

#5

pi@raspberrypi:~ $ sudo chown pihole:pihole /etc/pihole/pihole-FTL.db
pi@raspberrypi:~ $
pi@raspberrypi:~ $ stat /etc/pihole/pihole-FTL.db
File: /etc/pihole/pihole-FTL.db
Size: 5664768 Blocks: 11072 IO Block: 4096 regular file
Device: b307h/45831d Inode: 298718 Links: 1
Access: (0644/-rw-r–r--) Uid: ( 999/ pihole) Gid: ( 996/ pihole)
Access: 2019-03-12 19:00:21.225856045 +0000
Modify: 2019-03-17 12:50:00.294341861 +0000
Change: 2019-03-17 12:59:04.962671104 +0000
Birth: -
pi@raspberrypi:~ $
pi@raspberrypi:~ $ date
Sun 17 Mar 12:59:30 GMT 2019
pi@raspberrypi:~ $

All the settings you have changed back are settings i changed as per reading other posts. Its strange that all the data is there but not showing.

Thanks for your help.

Here is my debug token
[✓] Your debug token is: https://tricorder.pi-hole.net/7t7m3bo0lw!

How do i get a dev to look into it?

0 Likes

#6

Wait a bit :wink:

0 Likes

#7

Thanks mate

1 Like

#8

I didnt understand at first but below might explain why the chown:

pi@noads:~ $ ps -C pihole-FTL -o uid,user,gid,group,pid,cmd
  UID USER       GID GROUP      PID CMD
  999 pihole     996 pihole   12437 /usr/bin/pihole-FTL

And if by chance you have pihole-FTL running as the root user, it doesnt matter as root can do all.

0 Likes

#9

pi@raspberrypi:~ $ ps -C pihole-FTL -o uid,user,gid,group,pid,cmd
UID USER GID GROUP PID CMD
999 pihole 996 pihole 2472 /usr/bin/pihole-FTL

I have the same as you.

0 Likes

#10

What is the output of this command?

php -v

You may start seeing data in the database since you fixed the permissions issue.

0 Likes

#11

Of course.
Start/end epoch for date 16-3-2019 GMT is

1552694400
1552780799

So below one should show results for that date:

echo '.dump queries' | sqlite3 /etc/pihole/pihole-FTL.db | grep ',1552[6,7]' | head

If not, most likely pihole-FTL wasnt permitted to write to the db until the chown.

EDIT: I changed date!

0 Likes

#12

pi@raspberrypi:~ $ php -v
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib/php/20151012/pdo_sqlite.so’ - /usr/lib/php/20151012/pdo_sqlite.so: undefined symbol: php_pdo_unregister_driver in Unknown on line 0
PHP 7.0.33-0+deb9u3 (cli) (built: Mar 8 2019 10:01:24) ( NTS )
Copyright © 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright © 1998-2017 Zend Technologies
pi@raspberrypi:~ $

pi@raspberrypi:~ $ echo ‘.dump queries’ | sqlite3 /etc/pihole/pihole-FTL.db | grep ‘,1552[6,7]’ | head
INSERT INTO “queries” VALUES(31701,1552600002,1,1,‘mobile.pipe.aria.microsoft.com’,‘192.168.0.235’,NULL);
INSERT INTO “queries” VALUES(31702,1552600003,1,1,‘app-measurement.com’,‘192.168.0.235’,NULL);
INSERT INTO “queries” VALUES(31703,1552600013,1,1,‘app-measurement.com’,‘192.168.0.235’,NULL);
INSERT INTO “queries” VALUES(31704,1552600020,1,2,‘s3-eu-west-1.amazonaws.com’,‘192.168.0.246’,‘8.8.4.4’);
INSERT INTO “queries” VALUES(31705,1552600021,1,2,‘play.googleapis.com’,‘192.168.0.235’,‘8.8.4.4’);
INSERT INTO “queries” VALUES(31706,1552600023,1,2,‘www.google.com’,‘192.168.0.235’,‘8.8.4.4’);
INSERT INTO “queries” VALUES(31707,1552600025,1,1,‘app-measurement.com’,‘192.168.0.235’,NULL);
INSERT INTO “queries” VALUES(31708,1552600031,1,2,‘www.googleapis.com’,‘192.168.0.235’,‘8.8.4.4’);
INSERT INTO “queries” VALUES(31709,1552600034,1,1,‘mobile.pipe.aria.microsoft.com’,‘192.168.0.235’,NULL);
INSERT INTO “queries” VALUES(31710,1552600037,1,1,‘mobile.pipe.aria.microsoft.com’,‘192.168.0.235’,NULL);
pi@raspberrypi:~ $

0 Likes

#13

Ok the dbase appears populated before the chown pihole:pihole.
When did you alter ownership to root for that pihole-FTL.db file ?

See below link for info about that PHP warning:

What I can make up, either the pdo.so dependency isn’t installed or loaded in wrong order/priority or maybe a version conflict.
Whats output for below ones ?

pi@noads:~ $ dpkg -S pdo.
php7.0-common: /usr/share/php7.0-common/common/pdo.ini
php7.0-common: /usr/lib/php/20151012/pdo.so

pi@noads:~ $ ls -l /etc/php/7.0/c*i/conf.d/*pdo*
lrwxrwxrwx 1 root root 35 Aug 14  2018 /etc/php/7.0/cgi/conf.d/10-pdo.ini -> /etc/php/7.0/mods-available/pdo.ini
lrwxrwxrwx 1 root root 42 Aug 14  2018 /etc/php/7.0/cgi/conf.d/20-pdo_sqlite.ini -> /etc/php/7.0/mods-available/pdo_sqlite.ini
lrwxrwxrwx 1 root root 35 Aug 14  2018 /etc/php/7.0/cli/conf.d/10-pdo.ini -> /etc/php/7.0/mods-available/pdo.ini
lrwxrwxrwx 1 root root 42 Aug 14  2018 /etc/php/7.0/cli/conf.d/20-pdo_sqlite.ini -> /etc/php/7.0/mods-available/pdo_sqlite.ini

pi@noads:~ $ php-cgi -m
[..]
PDO
pdo_sqlite

Try reinstall that pdo.so module with below and post results after for above ones again:

sudo apt install --reinstall php7.0-common

EDIT: If that install fails, you could try dpkg purge again and reinstall:

sudo dpkg -P php7.0-common

sudo apt install --reinstall php7.0-common

EDIT2: I forgot you maybe need to enable the mod too and restart:

sudo phpenmod pdo

sudo service lighttpd restart

0 Likes

#14

Proper one liner (at least I think):

pi@noads:~ $ echo 'SELECT * FROM queries WHERE timestamp BETWEEN "'$(date --date="2019-03-16 00:00:00" +%s)'" AND "'$(date --date="2019-03-16 23:59:59" +%s)'";' | sqlite3 /etc/pihole/pihole-FTL.db
1479494|1552690836|1|2|api.nu.nl|10.0.0.70|8.8.4.4
1479495|1552690836|1|2|time-ios.apple.com|10.0.0.70|8.8.4.4
1479496|1552690836|1|2|safebrowsing.googleapis.com|10.0.0.70|8.8.4.4
[..]
1501102|1552776058|1|2|medianunl.aws.sanomaservices.nl|10.0.0.70|8.8.8.8
1501103|1552776990|1|2|api.nu.nl|10.0.0.70|8.8.4.4
1501104|1552776991|1|2|cl5.apple.com|10.0.0.70|8.8.8.8
0 Likes

#15

Sorry guys i have been away with work and have just got back.

pi@raspberrypi:~ $ dpkg -S pdo
ifupdown: /lib/ifupdown/settle-dad.sh
ifupdown: /lib/udev/ifupdown-hotplug
php7.0-sqlite3: /usr/share/php7.0-sqlite3/sqlite3/pdo_sqlite.ini
ifupdown: /usr/share/lintian/overrides/ifupdown
ifupdown: /usr/share/doc/ifupdown/copyright
php7.0-common: /usr/share/php7.0-common/common/pdo.ini
hostname: /bin/ypdomainname
php7.0-mysql: /usr/share/php7.0-mysql/mysql/pdo_mysql.ini
ifupdown: /usr/share/doc/ifupdown/examples/generate-interfaces.pl.gz
debhelper: /usr/share/man/de/man1/dh_installifupdown.1.gz
php7.0-mysql: /usr/lib/php/20151012/pdo_mysql.so
ifupdown: /usr/share/doc/ifupdown/contrib/ifstate-check
ifupdown: /usr/share/doc/ifupdown/examples/get-mac-address.sh
debhelper: /usr/share/man/man1/dh_installifupdown.1.gz
ifupdown: /usr/share/bug/ifupdown
ifupdown: /usr/share/doc/ifupdown/examples/check-mac-address.sh
debhelper: /usr/share/man/pt/man1/dh_installifupdown.1.gz
ifupdown: /usr/share/doc/ifupdown/NEWS.Debian.gz
ifupdown: /usr/share/doc/ifupdown/examples/pcmcia-compat.sh
ifupdown: /usr/share/doc/ifupdown/examples/bridge
ifupdown: /usr/share/doc/ifupdown/examples/ping-places.sh
php7.0-sqlite3: /usr/lib/php/20151012/pdo_sqlite.so
ifupdown: /lib/ifupdown
ifupdown: /usr/share/doc/ifupdown/contrib/ensureifup
debhelper: /usr/share/man/fr/man1/dh_installifupdown.1.gz
ifupdown: /usr/share/doc/ifupdown/changelog.gz
wpasupplicant: /etc/wpa_supplicant/ifupdown.sh
ifupdown: /lib/udev/rules.d/80-ifupdown.rules
ifupdown: /usr/share/doc/ifupdown/examples
debhelper: /usr/share/man/ja/man1/dh_installifupdown.1.gz
debhelper: /usr/share/man/es/man1/dh_installifupdown.1.gz
ifupdown: /usr/share/doc/ifupdown
php7.0-common: /usr/lib/php/20151012/pdo.so
libreoffice-common: /usr/lib/libreoffice/share/config/soffice.cfg/modules/swriter/ui/dropdownfielddialog.ui
hostname: /usr/share/man/man1/ypdomainname.1.gz
ifupdown: /usr/share/doc/ifupdown/contrib/ifstate
debhelper: /usr/bin/dh_installifupdown
ifupdown: /usr/share/doc/ifupdown/examples/network-interfaces.gz
ifupdown: /usr/share/doc/ifupdown/contrib
ifupdown: /lib/ifupdown/wait-for-ll6.sh
pi@raspberrypi:~ $ 
pi@raspberrypi:~ $ ls -l /etc/php/7.0/c*i/conf.d/*pdo*
lrwxrwxrwx 1 root root 42 Mar 14 18:28 /etc/php/7.0/cgi/conf.d/20-pdo_sqlite.ini -> /etc/php/7.0/mods-available/pdo_sqlite.ini
lrwxrwxrwx 1 root root 42 Mar 14 18:28 /etc/php/7.0/cli/conf.d/20-pdo_sqlite.ini -> /etc/php/7.0/mods-available/pdo_sqlite.ini
pi@raspberrypi:~ $ 
pi@raspberrypi:~ $ php-cgi -m
PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php/20151012/pdo_sqlite.so' - /usr/lib/php/20151012/pdo_sqlite.so: undefined symbol: php_pdo_unregister_driver in Unknown on line 0
[PHP Modules]
cgi-fcgi
Core
date
filter
hash
json
libxml
openssl
pcntl
pcre
Reflection
session
SPL
sqlite3
standard
zlib

[Zend Modules]

pi@raspberrypi:~ $ 
pi@raspberrypi:~ $ sudo apt install --reinstall php7.0-common
Reading package lists... Done
Building dependency tree       
Reading state information... Done
0 upgraded, 0 newly installed, 1 reinstalled, 0 to remove and 1 not upgraded.
Need to get 0 B/486 kB of archives.
After this operation, 0 B of additional disk space will be used.
(Reading database ... 119626 files and directories currently installed.)
Preparing to unpack .../php7.0-common_7.0.33-0+deb9u3_armhf.deb ...
Unpacking php7.0-common (7.0.33-0+deb9u3) over (7.0.33-0+deb9u3) ...
Setting up php7.0-common (7.0.33-0+deb9u3) ...
Not replacing deleted config file /etc/php/7.0/mods-available/calendar.ini
WARNING: Module calendar ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module calendar ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module calendar for cgi sapi
php_invoke: Enable module calendar for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/ctype.ini
WARNING: Module ctype ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module ctype ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module ctype for cgi sapi
php_invoke: Enable module ctype for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/exif.ini
WARNING: Module exif ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module exif ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module exif for cgi sapi
php_invoke: Enable module exif for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/fileinfo.ini
WARNING: Module fileinfo ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module fileinfo ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module fileinfo for cgi sapi
php_invoke: Enable module fileinfo for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/ftp.ini
WARNING: Module ftp ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module ftp ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module ftp for cgi sapi
php_invoke: Enable module ftp for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/gettext.ini
WARNING: Module gettext ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module gettext ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module gettext for cgi sapi
php_invoke: Enable module gettext for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/iconv.ini
WARNING: Module iconv ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module iconv ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module iconv for cgi sapi
php_invoke: Enable module iconv for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/pdo.ini
WARNING: Module pdo ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module pdo ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module pdo for cgi sapi
php_invoke: Enable module pdo for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/phar.ini
WARNING: Module phar ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module phar ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module phar for cgi sapi
php_invoke: Enable module phar for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/posix.ini
WARNING: Module posix ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module posix ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module posix for cgi sapi
php_invoke: Enable module posix for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/shmop.ini
WARNING: Module shmop ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module shmop ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module shmop for cgi sapi
php_invoke: Enable module shmop for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/sockets.ini
WARNING: Module sockets ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module sockets ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module sockets for cgi sapi
php_invoke: Enable module sockets for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/sysvmsg.ini
WARNING: Module sysvmsg ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module sysvmsg ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module sysvmsg for cgi sapi
php_invoke: Enable module sysvmsg for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/sysvsem.ini
WARNING: Module sysvsem ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module sysvsem ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module sysvsem for cgi sapi
php_invoke: Enable module sysvsem for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/sysvshm.ini
WARNING: Module sysvshm ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module sysvshm ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module sysvshm for cgi sapi
php_invoke: Enable module sysvshm for cli sapi
Not replacing deleted config file /etc/php/7.0/mods-available/tokenizer.ini
WARNING: Module tokenizer ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module tokenizer ini file doesn't exist under /etc/php/7.0/mods-available
php_invoke: Enable module tokenizer for cgi sapi
php_invoke: Enable module tokenizer for cli sapi
pi@raspberrypi:~ $ 
pi@raspberrypi:~ $ sudo phpenmod pdo
WARNING: Module pdo ini file doesn't exist under /etc/php/7.0/mods-available
WARNING: Module pdo ini file doesn't exist under /etc/php/7.0/mods-available
pi@raspberrypi:~ $ 

i realise this is a long reply just trying to do everything you asked.

This command dumps a load of data
echo ‘SELECT * FROM queries WHERE timestamp BETWEEN "’$(date --date=“2019-03-16 00:00:00” +%s)’" AND “’$(date --date=“2019-03-16 23:59:59” +%s)’”;’ | sqlite3 /etc/pihole/pihole-FTL.db

cheers

0 Likes

#16

Try:

sudo apt-get install php7.0-sqlite3
sudo phpdismod sqlite3
sudo phpenmod sqlite3
0 Likes

#17

pi@raspberrypi:~ $ sudo apt-get install php7.0-sqlite3
Reading package lists… Done
Building dependency tree
Reading state information… Done
php7.0-sqlite3 is already the newest version (7.0.33-0+deb9u3).
0 upgraded, 0 newly installed, 0 to remove and 1 not upgraded.
pi@raspberrypi:~ $ sudo phpdismod sqlite3
pi@raspberrypi:~ $ sudo phpenmod sqlite3
pi@raspberrypi:~ $

Done that mate and made no change.

0 Likes

#18

What do you have under /etc/php/7.0/mods-available?

0 Likes

#19

ls /etc/php/7.0/mods-available
json.ini pdo_sqlite.ini sqlite3.ini

0 Likes

#20

Your PHP 7 is very damaged. I don’t know of a way to fix it without just starting over on a new image.

This is from a stock install:

/etc/php/7.0/mods-available$ ls
calendar.ini  fileinfo.ini  iconv.ini    pdo.ini         posix.ini     sockets.ini  sysvsem.ini
ctype.ini     ftp.ini       json.ini     pdo_sqlite.ini  readline.ini  sqlite3.ini  sysvshm.ini
exif.ini      gettext.ini   opcache.ini  phar.ini        shmop.ini     sysvmsg.ini  tokenizer.ini
0 Likes