Comments (12)
Bernie-McGee
commented on May 27, 2024
1
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.
from pihole_adlist_tool.
yubiuser
commented on May 27, 2024
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...
from pihole_adlist_tool.
Bernie-McGee
commented on May 27, 2024
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.
from pihole_adlist_tool.
yubiuser
commented on May 27, 2024
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.dbcontains that column (this is where we get the data from).
pihole-FTL sqlite3 -h /etc/pihole/pihole-FTL.db "Select * from queries limit 1;"
from pihole_adlist_tool.
dowden20
commented on May 27, 2024
$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;
from pihole_adlist_tool.
yubiuser
commented on May 27, 2024
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
from pihole_adlist_tool.
dowden20
commented on May 27, 2024
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/
from pihole_adlist_tool.
dowden20
commented on May 27, 2024
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)
from pihole_adlist_tool.
Bernie-McGee
commented on May 27, 2024
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).
from pihole_adlist_tool.
yubiuser
commented on May 27, 2024
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.
from pihole_adlist_tool.
yubiuser
commented on May 27, 2024
As the sqlite command yield nothing it seems there aren't any queries in your long-term database. Did you disable logging/database?
from pihole_adlist_tool.
yubiuser
commented on May 27, 2024
It seems I can close this issue - it turns out the reason was not the tool but corrupted databases.
from pihole_adlist_tool.
Related Issues (20)
- Regex analysis not supported on docker HOT 2
- Receiving an error "database disk image is malformed" HOT 1
- Starting with version 2.4.0, you get multiple 'environment: line 5: docker: command not found' messages HOT 3
- "SUDO_SQLITE: command not found" HOT 3
- Does regex checking account for Filter Lists? HOT 2
- Regex Mode does not work in docker HOT 2
- Wrong detection of PIHOLE_DOCKER?! HOT 8
- Shellcheck is complaining a lot about pihole_adlist_tool HOT 1
- Installation HOT 3
- Question HOT 3
- Can't read /etc/pihole/pihole-FTL.db HOT 5
- Running tool outside the Docker-container throws some errors HOT 7
- Error: stepping, database is locked HOT 1
- Possibility to Deactivate redundant Blocklists HOT 7
- Add assigned group(s) for adlist in result HOT 3
- Instructions HOT 2
- ABP style adlists HOT 4
- Please add a licence HOT 1
- List the domains which generated a hit HOT 4
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pihole_adlist_tool.