Long Term Data not showing up despite Dashboard showing details

[rossum]

Expected Behaviour:

Long Term Data should appear when selected.

Actual Behaviour:

I'm running v5.0, freshly upgraded from 4.x. Long-term data didn't show up before the upgrade and still doesn't show up. I'm on a Raspberry Pi 2 Model B Rev 1.1. Yes, I have checked to make sure I'm selecting a date range. No data appears in Query Log, Top Lists, or Graphics.

Debug Token:

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

[Coro]

Check the permissions of the folder /etc/pihole and the file /etc/pihole/pihole-FTL.db. It should be owned by pihole pihole. Use

ls /etc/pihole/pihole-FTL.db -lh
ls /etc -lh | grep pihole

[rossum]

Thanks for the response!

It looks as expected:
pi@pihole:~ $ ls /etc/pihole/pihole-FTL.db -lh
-rw-rw-r-- 1 pihole pihole 29M May 25 22:19 /etc/pihole/pihole-FTL.db
pi@pihole:~ $ ls /etc -lh | grep pihole
drwxrwxr-x 3 pihole pihole 4.0K Jun 30 20:45 pihole

[Coro]

@rossum

May 25 22:19

The last data storing was a long time ago. When you run pihole restartdns, does this update the time of the .db file? If not, are there complaints in the /var/log/pihole-FTL.log file?

[rossum]

No, the timestamp doesn't change.

I'm a little out of my depth here with SQLite, but things look wrong:

pi@pihole:~ $ tail /var/log/pihole-FTL.log
[2020-07-01 00:43:00.009 12412] SQLite3 message: statement aborts at 3: [SELECT MAX(ID) FROM queries] database disk image is malformed (11)
[2020-07-01 00:43:00.010 12412] Encountered step error in get_max_query_ID(): database disk image is malformed
[2020-07-01 00:43:00.010 12412] Encountered error while trying to close database: database is locked
[2020-07-01 00:43:00.010 12412] SQLite3 message: database corruption at line 65591 of [18db032d05] (11)
[2020-07-01 00:43:00.010 12412] SQLite3 message: statement aborts at 3: [INSERT INTO queries VALUES (NULL,?,?,?,?,?,?)] database disk image is malformed (11)
[2020-07-01 00:43:00.010 12412] Encountered error while trying to store queries in long-term database: database disk image is malformed
[2020-07-01 00:43:00.010 12412] dbquery("END TRANSACTION") called but database is not available!
[2020-07-01 00:43:00.011 12412] END TRANSACTION failed when trying to store queries to long-term database
[2020-07-01 00:43:00.013 12412] dbquery("BEGIN TRANSACTION IMMEDIATE") called but database is not available!
[2020-07-01 00:43:00.013 12412] ERROR: Storing devices in network table ("BEGIN TRANSACTION IMMEDIATE") failed

Is there a way to recover/fix the database?

[yubiuser]

@rossum

[SELECT MAX(ID) FROM queries] database disk image is malformed (11)

sudo service pihole-FTL stop

sudo mv /etc/pihole/pihole-FTL.db /etc/pihole/pihole-FTL-old.db

sudo service pihole-FTL start

Corrupted database can indicate disk errors caused by power failures. Check if you use a proper power supply.

[Coro]

@rossum

Is there a way to recover/fix the database?

See

Ran arpflush, now network table is corrupted

This typically only happens either on power losses during a write or when you use a cheap power supply which was causing IO errors during the write. The data may be lost, you can try the following to try to revive the data, but no promises (this will, at least, not make it worse): sudo sqlite3 /etc/pihole/pihole-FTL.db REINDEX sudo sqlite3 /etc/pihole/pihole-FTL.db VACUUM If this doesn't work, no panic yet, try: sudo sqlite3 /etc/pihole/pihole-FTL.db and then: sqlite> .mode insert sqlite> …

and following posts

[rossum]

@yubiuser FYI, my uptime is 20 days. It's possible that the database was older than that from v4 and had some corruption from a previous power outage.

@Coro That may be the fix!

pi@pihole:~ $ sudo service pihole-FTL stop
pi@pihole:~ $ sudo sqlite3 /etc/pihole/pihole-FTL.db REINDEX
Error: database disk image is malformed
pi@pihole:~ $ sudo sqlite3 /etc/pihole/pihole-FTL.db VACUUM
Error: UNIQUE constraint failed: queries.id
pi@pihole:~ $ uptime
21:29:35 up 20 days, 11:55, 1 user, load average: 0.11, 0.03, 0.01
pi@pihole:~ $ sudo sqlite3 /etc/pihole/pihole-FTL.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .mode insert
sqlite> .output dump_all.sql
sqlite> .dump
sqlite> .exit
pi@pihole:~ $ sudo mv /etc/pihole/pihole-FTL.db-corrupt
mv: missing destination file operand after '/etc/pihole/pihole-FTL.db-corrupt'
Try 'mv --help' for more information.
pi@pihole:~ $ sudo mv /etc/pihole/pihole-FTL.
pihole-FTL.conf pihole-FTL.db
pi@pihole:~ $ sudo mv /etc/pihole/pihole-FTL.db /etc/pihole/pihole-FTL.db-corrupt
pi@pihole:~ $ sudo sqlite3 /etc/pihole/pihole-FTL.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .read dump_all.sql
Error: near line 414090: NOT NULL constraint failed: queries.status
Error: near line 414091: NOT NULL constraint failed: queries.status
Error: near line 414092: NOT NULL constraint failed: queries.status
Error: near line 414093: NOT NULL constraint failed: queries.status
Error: near line 414094: NOT NULL constraint failed: queries.status
Error: near line 414095: NOT NULL constraint failed: queries.status
Error: near line 414096: NOT NULL constraint failed: queries.status
Error: near line 414097: NOT NULL constraint failed: queries.status
Error: near line 414098: NOT NULL constraint failed: queries.status
Error: near line 414099: NOT NULL constraint failed: queries.status
Error: near line 414100: NOT NULL constraint failed: queries.status
Error: near line 414101: NOT NULL constraint failed: queries.status
Error: near line 414102: NOT NULL constraint failed: queries.status
Error: near line 414103: NOT NULL constraint failed: queries.status
Error: near line 414104: NOT NULL constraint failed: queries.status
Error: near line 414105: NOT NULL constraint failed: queries.status
Error: near line 414106: NOT NULL constraint failed: queries.status
Error: near line 414107: NOT NULL constraint failed: queries.status
Error: near line 414108: NOT NULL constraint failed: queries.status
Error: near line 414109: NOT NULL constraint failed: queries.status
Error: near line 414110: NOT NULL constraint failed: queries.status
Error: near line 414111: NOT NULL constraint failed: queries.status
Error: near line 414112: NOT NULL constraint failed: queries.status
Error: near line 414113: NOT NULL constraint failed: queries.status
Error: near line 414114: NOT NULL constraint failed: queries.status
Error: near line 414115: NOT NULL constraint failed: queries.status
Error: near line 414116: NOT NULL constraint failed: queries.status
Error: near line 414117: NOT NULL constraint failed: queries.status
Error: near line 414118: NOT NULL constraint failed: queries.status
sqlite> .exit
pi@pihole:~ $ sudo service pihole-FTL start

The errors concern me slightly, but for the first time in memory, I have long-term data showing! It does look like I lost historical data, but traffic is generating pretty graphs and I'll have to check on it tomorrow to see how it looks.

I appreciate your insight! Thank you!

[yubiuser]

@rossum

Error: near line 414090: NOT NULL constraint failed: queries.status

The error means that at this particular line in the status field of the table queries no value is entered (NULL) which is not allowed by the declaration of the table (NOT NULL constraint). This is probably a leftover from the malformated database.

[Coro]

@yubiuser

This is probably a leftover from the malformated database.

I second this conclusion. It is forbidden to add such entries to the database, so this cannot have been added in any regular way.