Whitelisting doesn't work, freezes pihole when attempting a second time whitelisting same item

[zilog256]

In raising this issue, I confirm the following: {please fill the checkboxes, e.g: [X]}

• I have read and understood the contributors guide.
• The issue I am reporting can be replicated.
• The issue I am reporting isn't a duplicate (see FAQs, closed issues, and open issues).

How familiar are you with the the source code relevant to this issue?:
1

---

Expected behavior:

Whitelisting an item doesn't work with pihole 5.x (last version). when trying to whitelist a second time, pihole becomes totally unresponsible and has to be rebooted to work again.

Actual behavior:

1. I want to whitelist a blocked request
2. I click on the corresponding whitelist button, and try to reload the blocked page on my PC :nothing changes, still blocked.
3. I refresh the "Queries" page, see that the request was still blocked, so click again on "Whitelist" for the request
4. That still changes nothing, and then the Pihole Web/Interface becomes unaccessible, and the pihole stops processing requests = no internet/DNS unless I reboot the RaspberryPi running pihole via SSH, or if I configure my modem/router with direct DNS instead of using the pihole device
5. this can be reproduced each time so it's not an acceptable solution
6. the only solution I've found was reverting to my (thankfully) saved image with pihole 4.x before upgrading to 5.x ; everything works fine

Steps to reproduce:

1. whitelist a blocked request: nothing changes
2. whitelist it a second time
3. unresponsible pihole, reboot needed

[yubiuser]

Are there any errors in /var/log/lighttpd/error.log

[zilog256]

I realize I've been a bit rash/quick in my handling of this (first post ever on github, my last programming skills were on Z80 and MC680x0 :) ) => I have reverted to a working 4.x version ; so now that is this done, I'll retry with full 5.x and give you back the needed technical log feedback ASAP

[zilog256]

There a dozen error logs, I guess the latest is "error.log" and all the gz'd ones are archives.

I just restored a 5.x image on my SDcard, updated to latest 5.1, rebooted.

Now I've reproduced the whitelist bug and the pihole is stuck, I'll reboot it.

So here is what the error log says:

2020-08-01 21:46:58: (server.c.1759) logfiles cycled UID = 0 PID = 619
2020-08-01 21:48:24: Wrong token! Please re-login on the Pi-hole dashboard.
2020-08-01 21:52:13: (server.c.2059) server stopped by UID = 0 PID = 1
2020-08-01 21:52:14: (server.c.1464) server started (lighttpd/1.4.53)
2020-08-01 21:52:14: (server.c.1493) WARNING: unknown config-key: alias.url (ignored)
2020-08-01 22:08:28: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:08:32: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:09:09: (server.c.2059) server stopped by UID = 0 PID = 1
2020-08-01 22:09:58: (server.c.1464) server started (lighttpd/1.4.53)
2020-08-01 22:09:58: (server.c.1493) WARNING: unknown config-key: alias.url (ignored)
2020-08-01 22:10:28: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:10:33: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:11:44: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:11:49: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:12:19: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:12:24: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:12:51: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:12:56: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:13:00: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11: 
2020-08-01 22:13:00: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:13:05: (mod_fastcgi.c.421) FastCGI-stderr: PHP Warning:  SQLite3::query(): Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418
2020-08-01 22:13:05: (mod_fastcgi.c.421) FastCGI-stderr: PHP Notice:  Undefined index: type in /var/www/html/admin/scripts/pi-hole/php/groups.php on line 507
2020-08-01 22:13:11: (mod_fastcgi.c.421) FastCGI-stderr: PHP Notice:  Undefined index: type in /var/www/html/admin/scripts/pi-hole/php/groups.php on line 507
2020-08-01 22:13:15: (mod_fastcgi.c.421) FastCGI-stderr: PHP Notice:  Undefined index: type in /var/www/html/admin/scripts/pi-hole/php/groups.php on line 507

[zilog256]

I rebooted, and done a debug, here is the debug token : https://tricorder.pi-hole.net/ovjr02uho9

EDIT: the .gz error logs are all old : the only recent error log is the one I posted above.

[yubiuser]

You got two problems

Undefined index: type in /var/www/html/admin/scripts/pi-hole/php/groups.php on line 507

See https://discourse.pi-hole.net/t/whitelist-button-doesnt-work-in-query-log-page/35695/23?u=yubiuser. It has likely the same underlying isse as #1536 and #1533

Unable to prepare statement: 11, database disk image is malformed in /var/www/html/admin/api_db.php on line 418

That's a problem. Your /etc/pihole/pihole-FTL.db is malformated (maybe a corrupted SD card after a power outage?). You could try to repair it: https://stackoverflow.com/questions/13675615/svn-cleanup-sqlite-database-disk-image-is-malformed/60152319#60152319

[zilog256]

Thanks for your answer.

I'm not very "at ease" with linux, just enough to setup a Raspberry Pi, do some maintenance etc.

The simplest thing would be to reinstall from scratch I guess.

2 questions then:

• would exporting/importing back my settings be OK ? (or will it also migrate some database corruption?)

• any idea why it works flawlessly with 4.3 and not after upgrading (successfully) to 5.1?

[yubiuser]

would exporting/importing back my settings be OK ? (or will it also migrate some database corruption?)

Should work, as the database which contains adlists, group management, etc. (gravity.db) is not affected. I recommend, to do a teleporter backup in any case.

any idea why it works flawlessly with 4.3 and not after upgrading (successfully) to 5.1?

No. As described above the issue might be a corrupted SD card and I wouldn't recommend to re-install on the same card. To search the logs for under-voltage with grep -ni "voltage" /var/log/syslog*

The simplest thing would be to reinstall from scratch I guess.

You could try to move/delete the long-term database (/etc/pihole/pihole-FTL.db) and see if at least the error on line 418 vanishes

sudo service pihole-FTL stop

mv /etc/pihole/pihole-FTL.db /etc/pihole/pihole-FTL_old.db (or rm /etc/pihole/pihole-FTL.db)

sudo service pihole-FTL start

[zilog256]

Hi there : I reinstalled from scratch, Raspbian latest version + update, Pihole, and Log2RAM.

I haven't added my black/whitelist settings and everything works fine.

You were right about data corruption : I've been playing with two32 Gb SDcards (to switch between the old working 4.3 and be able to test the "flawed" 5.1 version) : I can't remember which card was the one initially working but while imaging/reinstalling, one the 2 cards is completely dead and cannot be formatted/wiped/tested, freezes the USB port of the PC etc. So I've ditched it and am now using the second one which is OK ; I've been using this setup for > 6 months but hadn't installed Log2RAM til recently = apparently those SDcards wear out, indeed.

I'll give it a try with my exported settings eventually, but for now everything is fine, so thanks for you time, and sorry for the bug being hardware and not software :)

[zilog256]

Solved / corrupted SD card.