Parse error near line 14: no such column: additional_info

[dowden20]

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 ---^

`

[yubiuser]

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...

[Bernie-McGee]

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.

[yubiuser]

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;"

[dowden20]

$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;
 

[yubiuser]

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  

[dowden20]

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/

[dowden20]

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)

[Bernie-McGee]

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).

[yubiuser]

@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.

[yubiuser]

@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?

[Bernie-McGee]

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.

[yubiuser]

It seems I can close this issue - it turns out the reason was not the tool but corrupted databases.