Ran arpflush, now network table is corrupted

General Beta 5.0
1

Please follow the below template, it will help us to help you!

Please ensure that you are running the latest version of the beta code.
Run pihole -up to update to the latest, then verify that the problem still exists before reporting it.

Pi-hole version is release/v5.0 v4.3.5-482-gd42785a (Latest: v4.4)
AdminLTE version is release/v5.0 v4.3.2-472-gfec4550 (Latest: v4.3.3)
FTL version is release/v5.0 vDev-5ad55e8 (Latest: v4.3.1)

Problem with Beta 5.0:
Ran "pihole -flush" and pihole -arpflush" to clean up dashboard to test changes made to Unifi configuration. After Pihole commands were ran, noticed the network table in the Admin UI was no longer populating. Checked the "pihole-FTL" log and noticed this:

[2020-04-27 17:25:00.061 7609] SQLite3 message: database corruption at line 65570 of [18db032d05] (11)
   [2020-04-27 17:25:00.061 7609] SQLite3 message: statement aborts at 3: [SELECT MAX(ID) FROM queries] database disk image is malformed (11)
   [2020-04-27 17:25:00.062 7609] Encountered step error in get_max_query_ID(): database disk image is malformed
   [2020-04-27 17:25:00.062 7609] Encountered error while trying to close database: database is locked
   [2020-04-27 17:25:00.062 7609] SQLite3 message: database corruption at line 65570 of [18db032d05] (11)
   [2020-04-27 17:25:00.062 7609] SQLite3 message: statement aborts at 3: [INSERT INTO queries VALUES (NULL,?,?,?,?,?,?)] database disk image is malformed (11)
   [2020-04-27 17:25:00.062 7609] Encountered error while trying to store queries in long-term database: database disk image is malformed
   [2020-04-27 17:25:00.062 7609] dbquery("END TRANSACTION") called but database is not available!
   [2020-04-27 17:25:00.062 7609] END TRANSACTION failed when trying to store queries to long-term database
   [2020-04-27 17:25:00.063 7609] dbquery("BEGIN TRANSACTION IMMEDIATE") called but database is not available!
   [2020-04-27 17:25:00.063 7609] ERROR: Storing devices in network table ("BEGIN TRANSACTION IMMEDIATE") failed

After seeing the db corruption, attempted to run "pihole -reconfingure" and selected the "Repair" option but that did not correct the issue.

Debug Token:
https://tricorder.pi-hole.net/c6wsemo2ll

2

What is the output of

sqlite3 /etc/pihole/pihole-FTL.db "PRAGMA integrity_check;"

(it may run a long time, this is not an issue)?

3

Here is the results

/etc/pihole# sqlite3 /etc/pihole/pihole-FTL.db "PRAGMA integrity_check;"
*** in database main ***
On tree page 161729 cell 0: invalid page number 162187
Error: database disk image is malformed
4

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> .output dump_all.sql
sqlite> .dump

(this will backup whatever is still readable from your database)
Then,

sudo rm /etc/pihole/pihole-FTL.db 
sudo sqlite3 /etc/pihole/pihole-FTL.db

with:

sqlite> .read dump_all.sql

Good luck!

5

Does the pihole-FTL service need to be stopped?

/var/log# sudo sqlite3 /etc/pihole/pihole-FTL.db REINDEX
Error: database is locked
6

It should not need to, but with a corrupted database, everything is possible.

7

Neither of these methods worked.

The first suggested method failed instantly saying the database is "malformed"

The second suggested method fails on the read command. It will populate the db about 75% of the way and then the read command stops. The pihole-FTL.db file will grow in size the whole time but as soon as the read command stops, the db file goes back to 4K size.

I have made sure that the permissions on the pihole-FTL.db are pihole:pihole 664 before doing the read command.

Any other suggestions?

Is there a way to just create a clean FTL db? I couldn't care less about any historical data.

8

sudo service pihole-FTL stop

sudo rm /etc/pihole/pihole-FTL.db

sudo service pihole-FTL start