Network Error

tomwilson · October 18, 2023, 12:45pm

Not sure if this has been reported yet, but when going to Network under tools, the following error:

DL6ER · October 18, 2023, 2:37pm

Please check your /var/log/pihole/FTL.log file for additional details about the SQL error and tell us what you found there

tomwilson · October 18, 2023, 9:01pm

Bunch of SQL errors, looks like the database is not there, here is a snippet:

2023-10-18 12:08:24.579 [73159/T73171] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 12:08:24.580 [73159/T73171] ERR: getNameFromIP("192.168.0.165") - SQL error prepare: SQL logic error
2023-10-18 12:08:24.816 [73159M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 12:08:24.816 [73159M] ERR: getMACfromIP("192.168.0.210") - SQL error prepare: SQL logic error
2023-10-18 12:08:24.818 [73159M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 12:08:24.818 [73159M] ERR: getNameFromIP("192.168.0.210") - SQL error prepare: SQL logic error
2023-10-18 12:08:24.819 [73159M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 12:08:24.820 [73159M] ERR: getIfaceFromIP("192.168.0.210") - SQL error prepare: SQL logic error
2023-10-18 12:08:25.065 [73159M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 12:08:25.065 [73159M] ERR: getMACfromIP("192.168.0.2") - SQL error prepare: SQL logic error
2023-10-18 12:08:25.067 [73159M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 12:08:25.067 [73159M] ERR: getNameFromIP("192.168.0.2") - SQL error prepare: SQL logic error
2023-10-18 12:08:25.068 [73159M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 12:08:25.068 [73159M] ERR: getIfaceFromIP("192.168.0.2") - SQL error prepare: SQL logic error
2023-10-18 12:08:26.228 [73159M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 12:08:26.228 [73159M] ERR: getAliasclientIDfromIP("192.168.0.3") - SQL error prepare: SQL logic error
2023-10-18 12:08:26.231 [73159M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 12:08:26.232 [73159M] ERR: getMACfromIP("192.168.0.3") - SQL error prepare: SQL logic error
2023-10-18 12:08:26.234 [73159M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 12:08:26.235 [73159M] ERR: getNameFromIP("192.168.0.3") - SQL error prepare: SQL logic error
2023-10-18 12:08:26.236 [73159M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 12:08:26.237 [73159M] ERR: getIfaceFromIP("192.168.0.3") - SQL error prepare: SQL logic error

DL6ER · October 18, 2023, 9:14pm

This is strange. Please run

pihole-FTL sqlite3 -h /etc/pihole/pihole-FTL.db "SELECT * FROM ftl;"

and also

pihole-FTL sqlite3 -h /etc/pihole/pihole-FTL.db ".schema"

and show us the output

tomwilson · October 18, 2023, 9:15pm

tom@ubuntu:~$ sudo pihole-FTL sqlite3 -h /etc/pihole/pihole-FTL.db "SELECT * FROM ftl;"
id  value
--  -----
0   1
1   0
tom@ubuntu:~$ sudo pihole-FTL sqlite3 -h /etc/pihole/pihole-FTL.db ".schema"
CREATE TABLE queries ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp INTEGER NOT NULL, type INTEGER NOT NULL, status INTEGER NOT NULL, domain TEXT NOT NULL, client TEXT NOT NULL, forward TEXT );
CREATE TABLE sqlite_sequence(name,seq);
CREATE INDEX idx_queries_timestamps ON queries (timestamp);
CREATE TABLE ftl ( id INTEGER PRIMARY KEY NOT NULL, value BLOB NOT NULL );
CREATE TABLE counters ( id INTEGER PRIMARY KEY NOT NULL, value INTEGER NOT NULL );
tom@ubuntu:~$

DL6ER · October 18, 2023, 9:19pm

Yeah, so this is a problem. This is the database version and you should actually have a 13 as value here.

Please restart pihole-FTL and check the log for errors right after start. There should be some as FTL will try to upgrade your database to the latest version during startup when it finds an old database.

tomwilson · October 18, 2023, 9:28pm

Looks like the following errors happen after restart:

2023-10-18 21:24:45.536 [12890M] ERR: SQLite3 message: table counters already exists in "CREATE TABLE counters ( id INTEGER PRIMARY KEY NOT NULL, value INTEGER NOT NULL );" (1)
2023-10-18 21:24:45.536 [12890M] ERR: ERROR: SQL query "CREATE TABLE counters ( id INTEGER PRIMARY KEY NOT NULL, value INTEGER NOT NULL );" failed: SQL logic error (SQLITE_ERROR)
2023-10-18 21:24:45.537 [12890M] ERR: create_counter_table() failed!
2023-10-18 21:24:45.537 [12890M] ERR: Counter table not initialized, database not available
2023-10-18 21:24:45.546 [12890M] ERR: SQLite3 message: no such table: message in "DELETE FROM message;" (1)
2023-10-18 21:24:45.546 [12890M] ERR: ERROR: SQL query "DELETE FROM message;" failed: SQL logic error (SQLITE_ERROR)
2023-10-18 21:24:45.546 [12890M] ERR: flush_message_table() failed!
2023-10-18 21:24:45.547 [12890M] ERR: SQLite3 message: no such table: disk.query_storage in "INSERT INTO query_storage SELECT * FROM disk.query_storage WHERE timestamp >= ?" (1)
2023-10-18 21:24:45.547 [12890M] ERR: import_queries_from_disk(): SQL error prepare: SQL logic error
2023-10-18 21:24:45.549 [12890M] ERR: SQLite3 message: no such table: disk.query_storage in "SELECT MAX(id) FROM disk.query_storage" (1)
2023-10-18 21:24:45.549 [12890M] ERR: update_disk_db_idx(): Failed to get MAX(id) from disk.query_storage: SQL logic error
2023-10-18 21:24:45.758 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:45.758 [12890M] ERR: getAliasclientIDfromIP("192.168.0.196") - SQL error prepare: SQL logic error
2023-10-18 21:24:45.761 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:45.761 [12890M] ERR: getMACfromIP("192.168.0.196") - SQL error prepare: SQL logic error
2023-10-18 21:24:45.763 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:45.763 [12890M] ERR: getNameFromIP("192.168.0.196") - SQL error prepare: SQL logic error
2023-10-18 21:24:45.764 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:24:45.764 [12890M] ERR: getIfaceFromIP("192.168.0.196") - SQL error prepare: SQL logic error
2023-10-18 21:24:46.866 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:46.866 [12890M] ERR: getAliasclientIDfromIP("192.168.0.173") - SQL error prepare: SQL logic error
2023-10-18 21:24:46.869 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:46.870 [12890M] ERR: getMACfromIP("192.168.0.173") - SQL error prepare: SQL logic error
2023-10-18 21:24:46.872 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:46.872 [12890M] ERR: getNameFromIP("192.168.0.173") - SQL error prepare: SQL logic error
2023-10-18 21:24:46.874 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:24:46.875 [12890M] ERR: getIfaceFromIP("192.168.0.173") - SQL error prepare: SQL logic error
2023-10-18 21:24:46.914 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:46.914 [12890M] ERR: getAliasclientIDfromIP("::") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.571 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:47.572 [12890M] ERR: getAliasclientIDfromIP("127.0.0.1") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.574 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:47.575 [12890M] ERR: getMACfromIP("127.0.0.1") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.577 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:47.577 [12890M] ERR: getNameFromIP("127.0.0.1") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.579 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:24:47.580 [12890M] ERR: getIfaceFromIP("127.0.0.1") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.585 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:47.585 [12890/T12893] ERR: getNameFromIP("192.168.0.196") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.587 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:47.588 [12890/T12893] ERR: getNameFromIP("192.168.0.173") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.697 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:47.697 [12890M] ERR: getAliasclientIDfromIP("192.168.0.2") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.700 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:47.700 [12890M] ERR: getMACfromIP("192.168.0.2") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.702 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:47.702 [12890M] ERR: getNameFromIP("192.168.0.2") - SQL error prepare: SQL logic error
2023-10-18 21:24:47.703 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:24:47.704 [12890M] ERR: getIfaceFromIP("192.168.0.2") - SQL error prepare: SQL logic error
2023-10-18 21:24:48.596 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:48.597 [12890/T12893] ERR: getNameFromIP("192.168.0.2") - SQL error prepare: SQL logic error
2023-10-18 21:24:49.712 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:49.713 [12890M] ERR: getAliasclientIDfromIP("192.168.0.145") - SQL error prepare: SQL logic error
2023-10-18 21:24:49.715 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:49.716 [12890M] ERR: getMACfromIP("192.168.0.145") - SQL error prepare: SQL logic error
2023-10-18 21:24:49.718 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:49.718 [12890M] ERR: getNameFromIP("192.168.0.145") - SQL error prepare: SQL logic error
2023-10-18 21:24:49.720 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:24:49.721 [12890M] ERR: getIfaceFromIP("192.168.0.145") - SQL error prepare: SQL logic error
2023-10-18 21:24:50.606 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:50.607 [12890/T12893] ERR: getNameFromIP("192.168.0.145") - SQL error prepare: SQL logic error
2023-10-18 21:24:51.336 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:51.337 [12890M] ERR: getAliasclientIDfromIP("192.168.0.3") - SQL error prepare: SQL logic error
2023-10-18 21:24:51.338 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:24:51.339 [12890M] ERR: getMACfromIP("192.168.0.3") - SQL error prepare: SQL logic error
2023-10-18 21:24:51.341 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:51.341 [12890M] ERR: getNameFromIP("192.168.0.3") - SQL error prepare: SQL logic error
2023-10-18 21:24:51.342 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:24:51.343 [12890M] ERR: getIfaceFromIP("192.168.0.3") - SQL error prepare: SQL logic error
2023-10-18 21:24:51.615 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:24:51.615 [12890/T12893] ERR: getNameFromIP("192.168.0.3") - SQL error prepare: SQL logic error
2023-10-18 21:24:52.630 [12890/T12897] WARNING: API: Unauthorized
2023-10-18 21:25:00.008 [12890/T12891] ERR: SQLite3 message: statement aborts at 1: [BEGIN TRANSACTION] cannot start a transaction within a transaction (1)
2023-10-18 21:25:00.008 [12890/T12891] ERR: ERROR: SQL query "BEGIN TRANSACTION" failed: SQL logic error (SQLITE_ERROR)
2023-10-18 21:25:00.008 [12890/T12891] ERR: export_queries_to_disk() failed!
2023-10-18 21:25:00.010 [12890/T12891] ERR: SQLite3 message: no such table: network_addresses in "DELETE FROM network_addresses WHERE lastSeen < 1689801900;" (1)
2023-10-18 21:25:00.010 [12890/T12891] ERR: ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1689801900;" failed: SQL logic error (SQLITE_ERROR)
2023-10-18 21:25:02.600 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:02.601 [12890M] ERR: getAliasclientIDfromIP("192.168.0.179") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.603 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:02.604 [12890M] ERR: getMACfromIP("192.168.0.179") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.606 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:02.607 [12890M] ERR: getNameFromIP("192.168.0.179") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.609 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:25:02.609 [12890M] ERR: getIfaceFromIP("192.168.0.179") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.632 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:02.632 [12890/T12893] ERR: getNameFromIP("192.168.0.179") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.775 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:02.775 [12890M] ERR: getAliasclientIDfromIP("192.168.0.165") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.777 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:02.778 [12890M] ERR: getMACfromIP("192.168.0.165") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.779 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:02.779 [12890M] ERR: getNameFromIP("192.168.0.165") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.781 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:25:02.781 [12890M] ERR: getIfaceFromIP("192.168.0.165") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.989 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:02.989 [12890M] ERR: getAliasclientIDfromIP("192.168.0.205") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.991 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:02.992 [12890M] ERR: getMACfromIP("192.168.0.205") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.993 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:02.994 [12890M] ERR: getNameFromIP("192.168.0.205") - SQL error prepare: SQL logic error
2023-10-18 21:25:02.996 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:25:02.996 [12890M] ERR: getIfaceFromIP("192.168.0.205") - SQL error prepare: SQL logic error
2023-10-18 21:25:03.337 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:03.337 [12890M] ERR: getAliasclientIDfromIP("192.168.0.193") - SQL error prepare: SQL logic error
2023-10-18 21:25:03.340 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:03.341 [12890M] ERR: getMACfromIP("192.168.0.193") - SQL error prepare: SQL logic error
2023-10-18 21:25:03.342 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:03.343 [12890M] ERR: getNameFromIP("192.168.0.193") - SQL error prepare: SQL logic error
2023-10-18 21:25:03.345 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:25:03.345 [12890M] ERR: getIfaceFromIP("192.168.0.193") - SQL error prepare: SQL logic error
2023-10-18 21:25:03.641 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:03.641 [12890/T12893] ERR: getNameFromIP("192.168.0.165") - SQL error prepare: SQL logic error
2023-10-18 21:25:03.647 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:03.647 [12890/T12893] ERR: getNameFromIP("192.168.0.205") - SQL error prepare: SQL logic error
2023-10-18 21:25:03.650 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:03.651 [12890/T12893] ERR: getNameFromIP("192.168.0.193") - SQL error prepare: SQL logic error
2023-10-18 21:25:45.789 [12890M] ERR: SQLite3 message: no such table: network in "SELECT aliasclient_id FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? AND aliasclient_id IS NOT NULL GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:45.789 [12890M] ERR: getAliasclientIDfromIP("192.168.0.8") - SQL error prepare: SQL logic error
2023-10-18 21:25:45.792 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:45.792 [12890M] ERR: getMACfromIP("192.168.0.8") - SQL error prepare: SQL logic error
2023-10-18 21:25:45.794 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:45.795 [12890M] ERR: getNameFromIP("192.168.0.8") - SQL error prepare: SQL logic error
2023-10-18 21:25:45.797 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:25:45.797 [12890M] ERR: getIfaceFromIP("192.168.0.8") - SQL error prepare: SQL logic error
2023-10-18 21:25:46.705 [12890/T12893] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:46.705 [12890/T12893] ERR: getNameFromIP("192.168.0.8") - SQL error prepare: SQL logic error
2023-10-18 21:25:55.094 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:55.094 [12890M] ERR: getMACfromIP("192.168.0.196") - SQL error prepare: SQL logic error
2023-10-18 21:25:55.096 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:55.096 [12890M] ERR: getNameFromIP("192.168.0.196") - SQL error prepare: SQL logic error
2023-10-18 21:25:55.098 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:25:55.098 [12890M] ERR: getIfaceFromIP("192.168.0.196") - SQL error prepare: SQL logic error
2023-10-18 21:25:55.620 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:25:55.621 [12890M] ERR: getMACfromIP("192.168.0.145") - SQL error prepare: SQL logic error
2023-10-18 21:25:55.623 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:25:55.623 [12890M] ERR: getNameFromIP("192.168.0.145") - SQL error prepare: SQL logic error
2023-10-18 21:25:55.625 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:25:55.625 [12890M] ERR: getIfaceFromIP("192.168.0.145") - SQL error prepare: SQL logic error
2023-10-18 21:26:00.063 [12890/T12891] ERR: SQLite3 message: statement aborts at 4: [ATTACH ? AS ?] database disk is already in use (1)
2023-10-18 21:26:00.063 [12890/T12891] ERR: attach_database(): Failed to attach database: SQL logic error
2023-10-18 21:26:00.065 [12890/T12891] ERR: SQLite3 message: no such table: network_addresses in "DELETE FROM network_addresses WHERE lastSeen < 1689801960;" (1)
2023-10-18 21:26:00.066 [12890/T12891] ERR: ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1689801960;" failed: SQL logic error (SQLITE_ERROR)
2023-10-18 21:26:14.691 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:26:14.691 [12890M] ERR: getMACfromIP("192.168.0.165") - SQL error prepare: SQL logic error
2023-10-18 21:26:14.693 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:26:14.694 [12890M] ERR: getNameFromIP("192.168.0.165") - SQL error prepare: SQL logic error
2023-10-18 21:26:14.696 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:26:14.696 [12890M] ERR: getIfaceFromIP("192.168.0.165") - SQL error prepare: SQL logic error
2023-10-18 21:26:21.138 [12890M] ERR: SQLite3 message: no such table: network in "SELECT hwaddr FROM network WHERE id = (SELECT network_id FROM network_addresses WHERE ip = ? GROUP BY ip HAVING max(lastSeen));" (1)
2023-10-18 21:26:21.138 [12890M] ERR: getMACfromIP("192.168.0.179") - SQL error prepare: SQL logic error
2023-10-18 21:26:21.141 [12890M] ERR: SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
2023-10-18 21:26:21.141 [12890M] ERR: getNameFromIP("192.168.0.179") - SQL error prepare: SQL logic error
2023-10-18 21:26:21.143 [12890M] ERR: SQLite3 message: no such table: network in "SELECT interface FROM network JOIN network_addresses ON network_addresses.network_id = network.id WHERE network_addresses.ip = ? AND interface != 'N/A' AND interface IS NOT NULL;" (1)
2023-10-18 21:26:21.143 [12890M] ERR: getIfaceFromIP("192.168.0.179") - SQL error prepare: SQL logic erro

DL6ER · October 18, 2023, 9:34pm

tomwilson:

2023-10-18 21:24:45.536 [12890M] ERR: SQLite3 message: table counters already exists in "CREATE TABLE counters ( id INTEGER PRIMARY KEY NOT NULL, value INTEGER NOT NULL );" (1)
2023-10-18 21:24:45.536 [12890M] ERR: ERROR: SQL query "CREATE TABLE counters ( id INTEGER PRIMARY KEY NOT NULL, value INTEGER NOT NULL );" failed: SQL logic error (SQLITE_ERROR)
2023-10-18 21:24:45.537 [12890M] ERR: create_counter_table() failed!

So something must have gone wrong during the early initialization of the database and now you are stuck in some intermediate state. I can check at making the upgrade more tolerant against half-done upgrade steps but really we have two options here:

You stop pihole-FTL and remove /etc/pihole/pihole-FTL.db and start pihole-FTL
It should create a new database and - this time - it should not fail, or
You wait until tomorrow (no promises, maybe it takes longer) until I made the upgrade step more resistant and see if you can then upgrade.

No. 1 is the preferred method as it ensures you have a consistent database.

When did you create this Pi-hole for the first time? If it was within the last days, you might still have the initial log files in /var/log/pihole/FTL.log.* - I'd be interested in the very first start of FTL to see why the upgrade went wrong in the very beginning.

tomwilson · October 18, 2023, 9:46pm

No. 1 Worked.

I also forgot.. I originally installed in Docker and moved to bare metal, this is most likely the issue!

Thanks for the help.

DL6ER · October 20, 2023, 5:40pm

This will be prevented in the future due to

https://github.com/pi-hole/FTL/pull/1682