Database Locked in newest Beta V6.0 no Clients Listed

Starfoxfs · October 24, 2024, 4:26pm

Hi,
i have installed the latest DEV Branch of Pihole V6 Beta and my Clients are not listed anymore and the Network Table not working.

FTL.log says Database locked:

2024-10-24 16:09:57.551 DEBUG_RESOLVER Trying to obtain host name of "::" from network_addresses table
2024-10-24 16:09:57.553 DEBUG_RESOLVER Check for a host name associated with IP address ::
2024-10-24 16:09:57.554 DEBUG_RESOLVER  ---> not found
2024-10-24 16:09:57.554 DEBUG_RESOLVER Checking for a host name associated with the same device (but another IP address)
2024-10-24 16:09:57.554 DEBUG_RESOLVER  ---> not found
2024-10-24 16:10:01.071 ERROR export_queries_to_disk(): Failed to export queries: database is locked
2024-10-24 16:10:01.071 INFO     SQL query was: "INSERT INTO disk.query_storage SELECT * FROM query_storage WHERE id > ? AND timestamp < ?"
2024-10-24 16:10:01.071 INFO     with parameters: id = 209, timestamp = 1729786170.069461
2024-10-24 16:10:01.071 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.domain_by_id SELECT * FROM domain_by_id): Cannot export subtable: database is locked
2024-10-24 16:10:01.071 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.client_by_id SELECT * FROM client_by_id): Cannot export subtable: database is locked
2024-10-24 16:10:01.071 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.forward_by_id SELECT * FROM forward_by_id): Cannot export subtable: database is locked
2024-10-24 16:10:01.071 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.addinfo_by_id SELECT * FROM addinfo_by_id): Cannot export subtable: database is locked
2024-10-24 16:10:01.071 ERROR export_queries_to_disk(disk.UPDATE disk.sqlite_sequence SET seq = (SELECT seq FROM sqlite_sequence WHERE disk.sqlite_sequence.name = sqlite_sequence.name)): Cannot export subtable: database is locked
2024-10-24 16:10:01.071 ERROR ERROR: SQL query "INSERT OR REPLACE INTO disk.ftl (id, value) VALUES ( 1, 1729786194.931933 );" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:10:01.071 ERROR export_queries_to_disk(): Cannot update timestamp: database is locked
2024-10-24 16:10:01.072 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 328 WHERE id = 0;" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:10:01.072 ERROR export_queries_to_disk(): Cannot update total queries counter: database is locked
2024-10-24 16:10:01.072 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 3 WHERE id = 1;" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:10:01.072 ERROR export_queries_to_disk(): Cannot update blocked queries counter: database is locked
2024-10-24 16:10:02.074 ERROR ERROR: SQL query "DELETE FROM query_storage WHERE id IN (SELECT id FROM query_storage WHERE timestamp <= 1721923801 LIMIT (SELECT COUNT(*)/100 FROM query_storage));" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:10:02.074 WARNING Database busy in delete_old_queries_in_DB()!
2024-10-24 16:10:03.076 ERROR ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1721923802;" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:11:01.085 ERROR export_queries_to_disk(): Failed to export queries: database is locked
2024-10-24 16:11:01.085 INFO     SQL query was: "INSERT INTO disk.query_storage SELECT * FROM query_storage WHERE id > ? AND timestamp < ?"
2024-10-24 16:11:01.085 INFO     with parameters: id = 210, timestamp = 1729786230.084357
2024-10-24 16:11:01.086 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.domain_by_id SELECT * FROM domain_by_id): Cannot export subtable: database is locked
2024-10-24 16:11:01.086 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.client_by_id SELECT * FROM client_by_id): Cannot export subtable: database is locked
2024-10-24 16:11:01.086 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.forward_by_id SELECT * FROM forward_by_id): Cannot export subtable: database is locked
2024-10-24 16:11:01.086 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.addinfo_by_id SELECT * FROM addinfo_by_id): Cannot export subtable: database is locked
2024-10-24 16:11:01.086 ERROR export_queries_to_disk(disk.UPDATE disk.sqlite_sequence SET seq = (SELECT seq FROM sqlite_sequence WHERE disk.sqlite_sequence.name = sqlite_sequence.name)): Cannot export subtable: database is locked
2024-10-24 16:11:01.086 ERROR ERROR: SQL query "INSERT OR REPLACE INTO disk.ftl (id, value) VALUES ( 1, 1729786215.801979 );" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:11:01.086 ERROR export_queries_to_disk(): Cannot update timestamp: database is locked
2024-10-24 16:11:01.086 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 350 WHERE id = 0;" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:11:01.086 ERROR export_queries_to_disk(): Cannot update total queries counter: database is locked
2024-10-24 16:11:01.086 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 3 WHERE id = 1;" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:11:01.086 ERROR export_queries_to_disk(): Cannot update blocked queries counter: database is locked
2024-10-24 16:11:02.089 ERROR ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1721923861;" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:12:01.017 ERROR export_queries_to_disk(): Failed to export queries: database is locked

I have created a Debug Log:
https://tricorder.pi-hole.net/wrbhd7po/

rdwebdesign · October 24, 2024, 5:10pm

Starfoxfs:

2024-10-24 16:11:02.089 ERROR ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1721923861;" failed: database is locked (SQLITE_BUSY)
2024-10-24 16:12:01.017 ERROR export_queries_to_disk(): Failed to export queries: database is locked

The messages were being generated around 16:12, but the end of your log (at 18:24) no longer shows "database is locked" messages. Those messages were happening because the database was busy (failed: database is locked (SQLITE_BUSY)).

Apparently this was a temporary issue.

Are you still not able to see clients in the Network page? Do you still see SQLITE_BUSY messages?

Starfoxfs · October 24, 2024, 5:33pm

Yes, i tried to flush ARP Cache and deleted pihole-FTL.db:

sudo service pihole-FTL stop
sudo ip -s -s neigh flush all && echo '--arp--' && arp && echo '--sleep a bit--' && sleep 30 && arp
sudo rm /etc/pihole/pihole-FTL.db
sudo service pihole-FTL start

The FTL.log says this every time:

2024-10-24 17:28:00.085 ERROR add_netDB_network_address(-1, "192.168.47.52"): Failed to step (error 19): constraint failed
2024-10-24 17:28:00.086 ERROR Database error in ARP cache processing loop
2024-10-24 17:28:00.086 ERROR Error while trying to close database: database is locked
2024-10-24 17:29:01.040 ERROR export_queries_to_disk(): Failed to export queries: database is locked

I have deleted all Blocklists excepted Steven Blacks and Updated Gravity Database but Problem still exists.

Still no Clients in Network Page and no Clients in Clients Page

DL6ER · October 24, 2024, 7:25pm

That's the issue. This -1 shouldn't be there. It seems like we failed to insert the client into the database and then subsequent issues follow.

Please run

sudo pihole-FTL --config debug.arp true

and check again you logs. There should be new lines starting in DEBUG_ARP around these errors you have been quoting. With them, we should see clearer.

Starfoxfs · October 25, 2024, 7:47am

Good Morning,

i have deleted pihole-FTL.db again and run your command line (debug.arp true)

This is what is shown so far:



2024-10-25 07:40:27.149 INFO PID of FTL process: 9542
2024-10-25 07:40:27.150 INFO listening on 0.0.0.0 port 53
2024-10-25 07:40:27.150 INFO listening on :: port 53
2024-10-25 07:40:27.152 INFO PID of FTL process: 9542
2024-10-25 07:40:27.152 WARNING No database file found, creating new (empty) database
2024-10-25 07:40:27.177 INFO Database version is 1
2024-10-25 07:40:27.177 INFO Updating long-term database to version 2
2024-10-25 07:40:27.183 INFO Updating long-term database to version 3
2024-10-25 07:40:27.188 INFO Updating long-term database to version 4
2024-10-25 07:40:27.192 INFO Updating long-term database to version 5
2024-10-25 07:40:27.199 INFO Updating long-term database to version 6
2024-10-25 07:40:27.204 INFO Updating long-term database to version 7
2024-10-25 07:40:27.208 INFO Updating long-term database to version 8
2024-10-25 07:40:27.217 INFO Updating long-term database to version 9
2024-10-25 07:40:27.221 INFO Updating long-term database to version 10
2024-10-25 07:40:27.230 INFO Updating long-term database to version 11
2024-10-25 07:40:27.237 INFO Updating long-term database to version 12
2024-10-25 07:40:27.242 INFO Updating long-term database to version 13
2024-10-25 07:40:27.247 INFO Updating long-term database to version 14
2024-10-25 07:40:27.251 INFO Updating long-term database to version 15
2024-10-25 07:40:27.257 INFO Updating long-term database to version 16
2024-10-25 07:40:27.261 INFO Updating long-term database to version 17
2024-10-25 07:40:27.267 INFO Updating long-term database to version 18
2024-10-25 07:40:27.271 INFO Updating long-term database to version 19
2024-10-25 07:40:27.276 INFO Database successfully initialized
2024-10-25 07:40:27.286 INFO Imported 0 queries from the on-disk database (it has 0 rows)
2024-10-25 07:40:27.286 INFO Parsing queries in database
2024-10-25 07:40:27.286 INFO Imported 0 queries from the long-term database
2024-10-25 07:40:27.286 INFO  -> Total DNS queries: 0
2024-10-25 07:40:27.286 INFO  -> Cached DNS queries: 0
2024-10-25 07:40:27.286 INFO  -> Forwarded DNS queries: 0
2024-10-25 07:40:27.286 INFO  -> Blocked DNS queries: 0
2024-10-25 07:40:27.286 INFO  -> Unknown DNS queries: 0
2024-10-25 07:40:27.287 INFO  -> Unique domains: 0
2024-10-25 07:40:27.287 INFO  -> Unique clients: 0
2024-10-25 07:40:27.287 INFO  -> DNS cache records: 0
2024-10-25 07:40:27.287 INFO  -> Known forward destinations: 0
2024-10-25 07:40:27.465 INFO FTL is running as user pihole (UID 999)
2024-10-25 07:40:27.465 INFO Reading certificate from /etc/pihole/tls.pem ...
2024-10-25 07:40:27.465 INFO Using SSL/TLS certificate file /etc/pihole/tls.pem
2024-10-25 07:40:27.466 INFO Restored 0 API sessions from the database
2024-10-25 07:40:27.470 INFO Blocking status is enabled
2024-10-25 07:40:27.567 INFO Compiled 0 allow and 0 deny regex for 2 clients in 0.2 msec
2024-10-25 07:40:31.638 INFO Received 8/8 valid NTP replies from pool.ntp.org
2024-10-25 07:40:31.638 INFO Time offset: 1.853079e+00 ms (excluded 0 outliers)
2024-10-25 07:40:31.638 INFO Round-trip delay: 2.023619e+01 ms (excluded 0 outliers)
2024-10-25 07:40:31.639 INFO NTP server listening on 0.0.0.0:123 (IPv4)
2024-10-25 07:40:31.639 INFO NTP server listening on :::123 (IPv6)
2024-10-25 07:41:00.049 ERROR add_netDB_network_address(-1, "192.168.47.6"): Failed to step (error 19): constraint failed
2024-10-25 07:41:00.049 ERROR Database error in ARP cache processing loop
2024-10-25 07:41:00.049 DEBUG_ANY dnsmasq received signal 17
2024-10-25 07:41:00.049 ERROR Error while trying to close database: database is locked
2024-10-25 07:42:01.015 ERROR export_queries_to_disk(): Failed to export queries: database is locked
2024-10-25 07:42:01.015 INFO     SQL query was: "INSERT INTO disk.query_storage SELECT * FROM query_storage WHERE id > ? AND timestamp < ?"
2024-10-25 07:42:01.015 INFO     with parameters: id = 14, timestamp = 1729842090.013287
2024-10-25 07:42:01.015 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.domain_by_id SELECT * FROM domain_by_id): Cannot export subtable: database is locked
2024-10-25 07:42:01.015 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.client_by_id SELECT * FROM client_by_id): Cannot export subtable: database is locked
2024-10-25 07:42:01.015 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.forward_by_id SELECT * FROM forward_by_id): Cannot export subtable: database is locked
2024-10-25 07:42:01.016 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.addinfo_by_id SELECT * FROM addinfo_by_id): Cannot export subtable: database is locked
2024-10-25 07:42:01.016 ERROR export_queries_to_disk(disk.UPDATE disk.sqlite_sequence SET seq = (SELECT seq FROM sqlite_sequence WHERE disk.sqlite_sequence.name = sqlite_sequence.name)): Cannot export subtable: database is locked
2024-10-25 07:42:01.016 ERROR ERROR: SQL query "INSERT OR REPLACE INTO disk.ftl (id, value) VALUES ( 1, 1729842090.572036 );" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:42:01.016 ERROR export_queries_to_disk(): Cannot update timestamp: database is locked
2024-10-25 07:42:01.016 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 38 WHERE id = 0;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:42:01.016 ERROR export_queries_to_disk(): Cannot update total queries counter: database is locked
2024-10-25 07:42:01.016 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 20 WHERE id = 1;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:42:01.016 ERROR export_queries_to_disk(): Cannot update blocked queries counter: database is locked
2024-10-25 07:42:02.018 ERROR ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1721979721;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:43:01.063 ERROR export_queries_to_disk(): Failed to export queries: database is locked
2024-10-25 07:43:01.063 INFO     SQL query was: "INSERT INTO disk.query_storage SELECT * FROM query_storage WHERE id > ? AND timestamp < ?"
2024-10-25 07:43:01.063 INFO     with parameters: id = 8, timestamp = 1729842150.061579
2024-10-25 07:43:01.063 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.domain_by_id SELECT * FROM domain_by_id): Cannot export subtable: database is locked
2024-10-25 07:43:01.063 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.client_by_id SELECT * FROM client_by_id): Cannot export subtable: database is locked
2024-10-25 07:43:01.063 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.forward_by_id SELECT * FROM forward_by_id): Cannot export subtable: database is locked
2024-10-25 07:43:01.063 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.addinfo_by_id SELECT * FROM addinfo_by_id): Cannot export subtable: database is locked
2024-10-25 07:43:01.063 ERROR export_queries_to_disk(disk.UPDATE disk.sqlite_sequence SET seq = (SELECT seq FROM sqlite_sequence WHERE disk.sqlite_sequence.name = sqlite_sequence.name)): Cannot export subtable: database is locked
2024-10-25 07:43:01.063 ERROR ERROR: SQL query "INSERT OR REPLACE INTO disk.ftl (id, value) VALUES ( 1, 1729842146.411840 );" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:43:01.063 ERROR export_queries_to_disk(): Cannot update timestamp: database is locked
2024-10-25 07:43:01.063 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 40 WHERE id = 0;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:43:01.063 ERROR export_queries_to_disk(): Cannot update total queries counter: database is locked
2024-10-25 07:43:01.064 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 20 WHERE id = 1;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:43:01.064 ERROR export_queries_to_disk(): Cannot update blocked queries counter: database is locked
2024-10-25 07:43:02.067 ERROR ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1721979781;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:44:01.099 ERROR export_queries_to_disk(): Failed to export queries: database is locked
2024-10-25 07:44:01.100 INFO     SQL query was: "INSERT INTO disk.query_storage SELECT * FROM query_storage WHERE id > ? AND timestamp < ?"
2024-10-25 07:44:01.100 INFO     with parameters: id = 8, timestamp = 1729842210.098593
2024-10-25 07:44:01.100 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.domain_by_id SELECT * FROM domain_by_id): Cannot export subtable: database is locked
2024-10-25 07:44:01.100 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.client_by_id SELECT * FROM client_by_id): Cannot export subtable: database is locked
2024-10-25 07:44:01.100 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.forward_by_id SELECT * FROM forward_by_id): Cannot export subtable: database is locked
2024-10-25 07:44:01.100 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.addinfo_by_id SELECT * FROM addinfo_by_id): Cannot export subtable: database is locked
2024-10-25 07:44:01.100 ERROR export_queries_to_disk(disk.UPDATE disk.sqlite_sequence SET seq = (SELECT seq FROM sqlite_sequence WHERE disk.sqlite_sequence.name = sqlite_sequence.name)): Cannot export subtable: database is locked
2024-10-25 07:44:01.100 ERROR ERROR: SQL query "INSERT OR REPLACE INTO disk.ftl (id, value) VALUES ( 1, 1729842201.414977 );" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:44:01.100 ERROR export_queries_to_disk(): Cannot update timestamp: database is locked
2024-10-25 07:44:01.100 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 81 WHERE id = 0;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:44:01.100 ERROR export_queries_to_disk(): Cannot update total queries counter: database is locked
2024-10-25 07:44:01.100 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 23 WHERE id = 1;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:44:01.100 ERROR export_queries_to_disk(): Cannot update blocked queries counter: database is locked
2024-10-25 07:44:02.103 ERROR ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1721979841;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:45:01.074 ERROR export_queries_to_disk(): Failed to export queries: database is locked
2024-10-25 07:45:01.074 INFO     SQL query was: "INSERT INTO disk.query_storage SELECT * FROM query_storage WHERE id > ? AND timestamp < ?"
2024-10-25 07:45:01.074 INFO     with parameters: id = 8, timestamp = 1729842270.073120
2024-10-25 07:45:01.075 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.domain_by_id SELECT * FROM domain_by_id): Cannot export subtable: database is locked
2024-10-25 07:45:01.075 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.client_by_id SELECT * FROM client_by_id): Cannot export subtable: database is locked
2024-10-25 07:45:01.075 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.forward_by_id SELECT * FROM forward_by_id): Cannot export subtable: database is locked
2024-10-25 07:45:01.075 ERROR export_queries_to_disk(disk.INSERT OR IGNORE INTO disk.addinfo_by_id SELECT * FROM addinfo_by_id): Cannot export subtable: database is locked
2024-10-25 07:45:01.075 ERROR export_queries_to_disk(disk.UPDATE disk.sqlite_sequence SET seq = (SELECT seq FROM sqlite_sequence WHERE disk.sqlite_sequence.name = sqlite_sequence.name)): Cannot export subtable: database is locked
2024-10-25 07:45:01.075 ERROR ERROR: SQL query "INSERT OR REPLACE INTO disk.ftl (id, value) VALUES ( 1, 1729842265.926734 );" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:45:01.075 ERROR export_queries_to_disk(): Cannot update timestamp: database is locked
2024-10-25 07:45:01.075 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 94 WHERE id = 0;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:45:01.075 ERROR export_queries_to_disk(): Cannot update total queries counter: database is locked
2024-10-25 07:45:01.075 ERROR ERROR: SQL query "UPDATE disk.counters SET value = value + 32 WHERE id = 1;" failed: database is locked (SQLITE_BUSY)
2024-10-25 07:45:01.075 ERROR export_queries_to_disk(): Cannot update blocked queries counter: database is locked
2024-10-25 07:45:02.077 ERROR ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1721979901;" failed: database is locked (SQLITE_BUSY)

@2024-10-25 07:41:00.049 same Error

My Pihole Version:

Core
Version is v5.18.3-538-g1a487069 (Latest: null)
Branch is development
Hash is 1a487069 (Latest: 1a487069)
Web
Version is v5.21-986-gcd916b17 (Latest: null)
Branch is development
Hash is cd916b17 (Latest: cd916b17)
FTL
Version is vDev-8a95ce5 (Latest: null)
Branch is development
Hash is 8a95ce59 (Latest: 8a95ce59)

:Edit: No entrys in Network Table or Client Page

Starfoxfs · October 25, 2024, 11:08am

I have uninstalled V6 Development and went back to V5 and it works fine.

Then i changed to V6 and the above shown Problem occurs again "No Network Table and No Clients"

Im on Debian 12 Bookworm Kernel 6.6 on a Raspberry Pi 4B for System Information

I use Conditional Forwarding because my Fritzbox is the DHCP Server and Pihole the DNS Server with Unbound.

malformed · October 25, 2024, 11:14am

Chiming in here, I see the same behaviour on a newly installed pi-hole v6 on bookworm.

I installed v6 directly based on the comment here (but did have issues with folder ownership)

Debian GNU/Linux 12 (bookworm)"
Linux 6.6.51+rpt-rpi-v8 aarch64

Core Version is v5.18.3-528-g16af216b
Web Version is v5.21-986-gcd916b17
FTL Version is vDev-148e558

debug log with arp debug enabled as recommended above: https://tricorder.pi-hole.net/gh9nsMBB

Maybe this adds value to the "hunt"…

-edit-
removed the db and restarted ftl, I also see this:

2024-10-25 13:26:00.080 CEST [24741/T24744] ERROR: add_netDB_network_address(-1, "192.168.0.168"): Failed to step (error 19): constraint failed

DL6ER · October 25, 2024, 4:42pm

It is rather unclear what is happening. So I went through the entire (lengthy...) source code of the network table processing and ensured error handling works in every case. The code has grown over half a decade and could very well take some unification and slight simplifications in a few places.

In case you are not using Pi-hole v6 docker but have a native install, please run

pihole checkout ftl tweak/network-table-error-handling

and see if this improves the error situation. Even if it doesn't (likeliness is maybe 20%), the improved error handling should hopefully give us more details about what is exactly going wrong here.

In case you are using Pi-hole v6 docker, you'd need to build a customized image yourself, see README.md/Building the image locally. Your command would be:

./build.sh -f tweak/network-table-error-handling

resulting in a new image you can thereafter use like image: pihole:local in your docker compose file.

Still somewhat grasping at straws as to what the exact cause is - but we will get there! Thank you for your help!

Starfoxfs · October 25, 2024, 5:34pm

Hi @DL6ER ,
today i have tested something Interesting. I have fully uninstalled Pihole and reinstalled it to latest stable V5.
Network Table and Client Page working fine here.

Then i have updated to Development Channel for all Instances (WEB, CORE and FTL)
Now Network Table and Clients are already Up (i think from V5)

After i Flush the Network Table the Error Occurs again but only if i Flush the Network Table

I have also tried the Channel "tweak/network-table-error-handling"

Now that works !! Network Table is fine now Clients are shown up

I hope you can find the Bug with that Information (I dont use Docker only native Installation)

Heres the FTL.log:



2024-10-25 17:29:13.186 INFO PID of FTL process: 1725
2024-10-25 17:29:13.187 INFO listening on 0.0.0.0 port 53
2024-10-25 17:29:13.187 INFO listening on :: port 53
2024-10-25 17:29:13.190 INFO PID of FTL process: 1725
2024-10-25 17:29:13.191 ERROR SQLite3: recovered 68 frames from WAL file /etc/pihole/pihole-FTL.db-wal (283)
2024-10-25 17:29:13.192 INFO Database version is 19
2024-10-25 17:29:13.201 INFO Database successfully initialized
2024-10-25 17:29:13.443 INFO Imported 11156 queries from the on-disk database (it has 11156 rows)
2024-10-25 17:29:13.443 INFO Parsing queries in database
2024-10-25 17:29:13.590 INFO   10000 queries parsed...
2024-10-25 17:29:13.605 INFO Imported 11156 queries from the long-term database
2024-10-25 17:29:13.605 INFO  -> Total DNS queries: 11156
2024-10-25 17:29:13.605 INFO  -> Cached DNS queries: 3979
2024-10-25 17:29:13.605 INFO  -> Forwarded DNS queries: 5122
2024-10-25 17:29:13.605 INFO  -> Blocked DNS queries: 1967
2024-10-25 17:29:13.605 INFO  -> Unknown DNS queries: 12
2024-10-25 17:29:13.605 INFO  -> Unique domains: 1567
2024-10-25 17:29:13.605 INFO  -> Unique clients: 9
2024-10-25 17:29:13.605 INFO  -> DNS cache records: 2948
2024-10-25 17:29:13.605 INFO  -> Known forward destinations: 2
2024-10-25 17:29:13.790 INFO FTL is running as user pihole (UID 999)
2024-10-25 17:29:13.790 INFO Reading certificate from /etc/pihole/tls.pem ...
2024-10-25 17:29:13.791 INFO Using SSL/TLS certificate file /etc/pihole/tls.pem
2024-10-25 17:29:13.792 INFO Restored 0 API sessions from the database
2024-10-25 17:29:13.795 INFO Blocking status is enabled
2024-10-25 17:29:13.892 INFO Compiled 0 allow and 0 deny regex for 9 clients in 0.2 msec
2024-10-25 17:29:17.906 INFO Received 8/8 valid NTP replies from pool.ntp.org
2024-10-25 17:29:17.906 INFO Time offset: -1.891681e+00 ms (excluded 1 outliers)
2024-10-25 17:29:17.906 INFO Round-trip delay: 1.302174e+01 ms (excluded 1 outliers)
2024-10-25 17:29:17.906 INFO NTP server listening on 0.0.0.0:123 (IPv4)
2024-10-25 17:29:17.906 INFO NTP server listening on :::123 (IPv6)

DL6ER · October 25, 2024, 7:54pm

This is already sufficient. I used this special branch to iron out quite a few edges that appeared on the over the past few years. Let's see if it fixes the issue also for the others.

malformed · October 25, 2024, 9:11pm

Thanks for the quick tweak - for me it is "partly" fixed I suppose. I can now see clients in the drop down, but the "Dashboard" gui still saysClients: 0

In case it would still be valuable, i removed the db again and restarted ftl, and created a new diagnostic too: https://tricorder.pi-hole.net/uebaA1HR/

rdwebdesign · October 26, 2024, 5:21am

This is expected.

This dropdown is populated with data from Network table to help you use this page, ...

... but this page will only contain entries manually added (by you) to some group.

Your latest debug log shows you didn't add any clients, so zero is the correct count.

malformed · October 26, 2024, 9:53am

What an embarrassing pebkac moment, I hang my head in shame!

DL6ER · October 26, 2024, 8:04pm

The branch has been merged into development.

Please run

sudo pihole checkout ftl development

to get back on track and receive future updates. The branch we used here for testing will not receive any further updates.

Thanks for helping confirming the fix!

Starfoxfs · October 27, 2024, 8:43am

Works fine now, i have changed to development yesterday.

Great Work

malformed · October 27, 2024, 1:18pm

It is normal this requires sudo, right ... right?

Starfoxfs · October 27, 2024, 2:45pm

Yes since Pihole V6 it requires Supderuser Rights

yubiuser · October 27, 2024, 3:09pm

... also on v5 already. But it was checked at a later stage.

github.com

pi-hole/pi-hole/blob/7128c18b4c5607a0eb99009c06da6d3246842112/pihole#L525-L543


      
          # we need to add all arguments that require sudo power to not trigger the * argument
          "-w" | "whitelist"            ) ;;
          "-b" | "blacklist"            ) ;;
          "--wild" | "wildcard"         ) ;;
          "--regex" | "regex"           ) ;;
          "--white-regex" | "white-regex" ) ;;
          "--white-wild" | "white-wild"   ) ;;
          "-f" | "flush"                ) ;;
          "-up" | "updatePihole"        ) ;;
          "-r"  | "reconfigure"         ) ;;
          "-g" | "updateGravity"        ) ;;
          "-l" | "logging"              ) ;;
          "uninstall"                   ) ;;
          "enable"                      ) ;;
          "disable"                     ) ;;
          "-d" | "debug"                ) ;;
          "restartdns"                  ) ;;
          "-a" | "admin"                ) ;;
          "checkout"                    ) ;;