Closed dowden20 closed 1 year ago
Thanks for the report. I forgot to update the internal version string after I tagged the last release. There difference between both versions is only the fix of a typo, all other changes affect only the handling here on github. But of course this needs to be fixed.
For the other error I need to investigate a bit further...
Experiencing the same issue. I don't know anything about SQLite, but I think the issue may be here, specifically line 603 (being line 14 of the here-document).
I am happy to do further testing/investigation (with some direction) or to tests patches.
Nothing in that part of the code has been changed in the last 2 years. It's strange that the column does not exist, it's created just a few lines above 562. Let's first check, if your pihole-FTL.db
contains that column (this is where we get the data from).
pihole-FTL sqlite3 -h /etc/pihole/pihole-FTL.db "Select * from queries limit 1;"
$sudo sqlite3 /etc/pihole/pihole-FTL.db
SQLite version 3.34.1 2021-01-20 14:10:07
sqlite> .tables
counters ftl queries
sqlite> select sql from sqlite_master where name = 'queries';
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 )
sqlite> select * from queries limit 1;
sqlite> select * from queries;
That looks suspicious too. There should be much more tables. Please generate a Pi-hole debug log and post the token.
sqlite> .tables
addinfo_by_id counters ftl network_addresses
aliasclient domain_by_id message queries
client_by_id forward_by_id network query_storage
Your debug token is: https://tricorder.pi-hole.net/feYd7qeu/
Ran another one with database integrity check Your debug token is: https://tricorder.pi-hole.net/MSjhhAV2/
I un-install pi-hole and re-install Then go to >Teleporter >Restore Select Whitelist, Blacklist, Adlists un-check 'Clear existing data'
Previously I have all option checked including 'Clear existing data'
This seems to resolve the error.
$sudo sqlite3 /etc/pihole/pihole-FTL.db SQLite version 3.34.1 2021-01-20 14:10:07 Enter ".help" for usage hints.
sqlite> .tables counters message network_addresses ftl network queries
sqlite> select sql from sqlite_master where name = 'queries'; 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 , additional_info TEXT)
pihole-FTL sqlite3 -h /etc/pihole/pihole-FTL.db "Select * from queries limit 1;"
returns nothing with exit code 0.
Unfortunately, I'm running the ArchLinux distribution of Pi-Hole and the debug token has been inconveniently disabled (though I'm sure it keeps upstream from being hassled).
@dowden20
Despite the integrity check did not report any issues, there were quite a few hints that the database was corrupted.
2023-05-28 09:25:52: mod_fastcgi.c.487) FastCGI-stderr:PHP Warning: SQLite3::query(): Unable to prepare statement: 1, no such table: message in /var/www/html/admin/api_db.php on line 385
2023-05-28 09:57:29: mod_fastcgi.c.487) FastCGI-stderr:PHP Warning: SQLite3::query(): Unable to prepare statement: 1, no such table: message in /var/www/html/admin/api_db.php on line 385
2023-05-28 09:57:36: mod_fastcgi.c.487) FastCGI-stderr:PHP Warning: SQLite3::query(): Unable to prepare statement: 1, no such table: message in /var/www/html/admin/api_db.php on line 385
2023-05-28 17:42:56: mod_fastcgi.c.487) FastCGI-stderr:PHP Warning: SQLite3::query(): Unable to prepare statement: 1, no such table: message in /var/www/html/admin/api_db.php on line 385
2023-05-28 17:42:57: mod_fastcgi.c.487) FastCGI-stderr:PHP Warning: SQLite3::query(): Unable to prepare statement: 1, no such table: message in /var/www/html/admin/api_db.php on line 385
[2023-05-31 10:02:00.043 203694/T203709] SQLite3 message: no such table: network_addresses in "DELETE FROM network_addresses WHERE lastSeen < 1654005720;" (1)
[2023-05-31 10:02:00.044 203694/T203709] ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1654005720;" failed: SQL logic error (SQLITE_ERROR)
[2023-05-31 10:03:00.012 203694/T203709] SQLite3 message: no such table: network_addresses in "DELETE FROM network_addresses WHERE lastSeen < 1654005780;" (1)
[2023-05-31 10:03:00.012 203694/T203709] ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1654005780;" failed: SQL logic error (SQLITE_ERROR)
[2023-05-31 10:03:27.012 203694M] 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-05-31 10:03:27.012 203694M] getMACfromIP("192.168.1.103") - SQL error prepare: SQL logic error
[2023-05-31 10:03:27.012 203694M] SQLite3 message: no such table: network_addresses in "SELECT name FROM network_addresses WHERE name IS NOT NULL AND ip = ?;" (1)
[2023-05-31 10:03:27.012 203694M] getNameFromIP("192.168.1.103") - SQL error prepare: SQL logic error
[2023-05-31 10:03:27.012 203694M] 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-05-31 10:03:27.012 203694M] getIfaceFromIP("192.168.1.103") - SQL error prepare: SQL logic error
It might have been enough to move the database and re-start FTL
to create a new one. But glad it's working now.
@Bernie-McGee
As the sqlite
command yield nothing it seems there aren't any queries in your long-term database. Did you disable logging/database?
Seems the issue probably is corrupted databases. After confirming I had logging on, I stopped the pihole-FTL service, deleted the database file, and restarted the service to create a new database. The sqlite
command now returns output, and pihole_adlist_tool
is working with no parsing errors.
It seems I can close this issue - it turns out the reason was not the tool but corrupted databases.
I downloaded 2.6.4, but it contains 2.6.3
And it error-ed out as below:
$./pihole_adlist_tool -d 0 -s total
Pihole Adlist Tool 2.6.3
++++++++ Info ++++++++ [i] PIHOLE_DOCKER: No [i] PIHOLE_DNSMASQ_VERSION: v2.89-9461807 [i] SQLITE_VERSION: 3.42.0 [i] DAYS_REQUESTED: all time [i] TOP: Not shown [i] SORT_ORDER: total_domains DESC [i] UNIQUE: Not shown [i] REGEX_MODE: Disabled [i] There is an update available: 2.6.4 ++++++++++++++++++++++
Would you like to analyze your current adlist configuration or first enable all adlists (current can be restored later)?
1) Current adlist configuration 2) Enable all adlists (runs pihole -g)
Please select: 1
[i] Keeping current adlist configuration
[i] Calculating..... [i] This might take some time - please be patient. Parse error near line 14: no such column: additional_info INSERT INTO cname(additional_info, hits) SELECT additional_info, COUNT(domain) error here ---^
`