Expected Behaviour:
New network devices show up, diagnosis page entries can be deleted
Actual Behaviour:
No new devices showing up, diagnosis page entries can not be deleted
To shorten things up: database is locked once again. Pretty similar to
- Database locked (one symptom: deleting ONE entry in Pi-hole diagnosis triggers deleetion of 1616 (invisible) messages which fails)
- Database lock messages in FTL log
- Database locked (dashboard and query log are empty)
Found out accidentally because of:
/var/log/pihole/FTL.log
:
[2023-07-01 15:00:00.193 29056/T29083] Notice: Database size is 3216.38 MB, deleted 9 rows
[2023-07-01 15:05:59.642 29056/T29084] WARNING: Long-term load (15min avg) larger than number of processors: 4.8 > 4
[2023-07-01 15:10:00.213 29056/T29083] Notice: Database size is 3216.42 MB, deleted 35 rows
[2023-07-01 15:11:00.024 29056/T29084] WARNING: Long-term load (15min avg) larger than number of processors: 7.2 > 4
[2023-07-01 15:11:00.052 29056/T29083] Encountered error while trying to store client in long-term database
[2023-07-01 15:11:00.053 29056/T29083] Statement finalization failed when trying to store queries to long-term database
[2023-07-01 15:11:00.053 29056/T29083] Error while trying to close database: database is locked
[2023-07-01 15:11:00.096 29056/T29083] SQLite3 message: database is locked in "DELETE FROM network_addresses WHERE lastSeen < 1593609060;" (5)
[2023-07-01 15:11:00.096 29056/T29083] ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1593609060;" failed: database is locked (SQLITE_BUSY)
[2023-07-01 15:11:01.051 29056/T29084] add_message(type=6, message=excessive load) - SQL error step DELETE: database is locked
[2023-07-01 15:11:01.052 29056/T29084] Error while trying to close database: database is locked
[2023-07-01 15:12:01.214 29056/T29083] ERROR: SQL query "END TRANSACTION" failed: database is locked (SQLITE_BUSY)
[2023-07-01 15:12:01.215 29056/T29083] END TRANSACTION failed when trying to store queries to long-term database
[2023-07-01 15:12:01.215 29056/T29083] Keeping queries in memory for later new attempt
[2023-07-01 15:12:02.383 29056/T29083] ERROR: SQL query "END TRANSACTION" failed: database is locked (SQLITE_BUSY)
...
...
...
Matching that time:
sudo ll /etc/pihole/pihole-FTL.db
-rw-rw-r-- 1 pihole pihole 3,0G Jul 1 15:10 /etc/pihole/pihole-FTL.db
What I did to "fix" it (again):
- Checked it's really the well-known "Database locked" issue with
sudo cat /var/log/pihole/FTL.log | grep -c "database is locked"
giving617
which is > 0... - Ran
sqlite3 /etc/pihole/pihole-FTL.db "PRAGMA integrity_check;"
givingok
- Ran
pihole -r
and did a repair (not yet, waiting if lucky and a dev reads this first so we can probably test something) - Checked if deleting stuff from diagnosis log is working, new network devices show up in tools/network and if graphs are being updated. Yes to all of that:
(HOW TO) AVOID THIS IN FUTURE!
We don't know why this happens from time to time. But we know it happens regularly for plenty of users. Which is very frustrating! How to make any progress here and regain stability? Is it the size of the database getting critical (~ 3 GB)? Let's please shed some light on this. I don't want to continue running Pi-Hole that way.
I'd like to repost the very same words as in Database locked (one symptom: deleting ONE entry in Pi-hole diagnosis triggers deleetion of 1616 (invisible) messages which fails) - #3 by bcutter half a year ago:
I'm just wondering how I could detect this issue in future (right in time instead of many days later by accident!)... maybe
- grep-ing the
pihole-FTL.log
regularly for the appearance ofdatabase is locked
?- Can Pi-hole run some kind of self-check and announce this issue somehow? Possibly by replacing the "Active" with "Error!" or something like that?
I'd like to add another idea:
3. I could monitor if /etc/pihole/pihole-FTL.db
did not update within the last x minutes and use that as alarm trigger. I think idea 1 is better than that.
Not sure if there's a non-interactive way of doing a repair (pihole -r
stops and needs a decision if repair or reinstall is desired) so I could automate it in this way:
- detect if database is locked
- run a repair of pi-hole
That's my short-term target here to re-gain stability. Thank for supporting this as well as the long-term goal of avoiding this to happen at all
Update: ran pihole -r
meanwhile and updated this post. Could not wait any longer due to urgent tasks needing a fully working Pi-hole.