Undefined array key "client_id" in api_db.php

Help
1

Found another issue on the same installation, but this issue happens on LightHTTPD:

Access Long Term Data > Query Log
Select a time range - whatever selection fails
Throws javascript alert error

Undefined array key "client_id" in /var/www/html/admin/api_db.php on line 127

Environment

Server

Ubuntu 22.04 (5.15.0-41-generic x86_64)
PHP 8.1 FPM (from package php8.1-fpm 8.1.2-1ubuntu2)
Lightttpd 1.4.63-1ubuntu3
Docker 20.10.13~3-0~ubuntu-jammy (package docker-ce from docker repo)

Pi-hole v5.11.4
FTL v5.16.1
Web Interface v5.13

Docker

caddy v2.5.1 (from caddy latest image)

1 Like
2

I cannot reproduce your issue.
Accessing the Long Term Query Log works for me even if I select all availabe data (which results in about 400,000 queries for my configuration). There indeed is a limitation imposed by PHP memory size, but that would result in a respective different error message when triggered.

Your descripton in your original topic seems to suggest that your are trying to have Caddy serve the Pi-hole's UI from the same files while Pi-hole's lighttpd is still active at the same time. What you observe may be a side effect of that configuration. I am unsure how lighttdp or Caddy would react to those shared and probably mixed-up responsibilities.

It may be cleaner to have Caddy redirect, rewrite or proxy the HTTP request URIs so that they would be handled by lighttpd.

Alternatively, you could opt to install Pi-hole with its web UI, but without its lighttpd webserver. That way, you could avoid potential conflicts by having to share web resources among two webservers.

3

This will remove the warning:

But it seems you have another (unrelated) issue.

4

Correct, this will not only remove the warning but also return data. So this is not only a warning, it's a plain out critical error.

I made changes to the code to echo the SQL query, and run it manually on Linux.

sqlite3 /etc/pihole/pihole-FTL.db "SELECT timestamp, type, CASE typeof(domain) WHEN 'integer' THEN (SELECT domain FROM domain_by_id d WHERE d.id = q.domain) ELSE domain END domain, CASE typeof(client) WHEN 'integer' THEN (   SELECT CASE TRIM(name) WHEN '' THEN c.ip ELSE c.name END name FROM client_by_id c WHERE c.id = q.client ) ELSE client END client, CASE typeof(forward) WHEN 'integer' THEN (SELECT forward FROM forward_by_id f WHERE f.id = q.forward) ELSE forward END forward, status, reply_type, reply_time, dnssec FROM query_storage q WHERE timestamp >= 1658026800 AND timestamp <= 1658113199 AND status IN (1,2,14,3,4,5,6,7,8,9,10,11,12,13,15,16) ORDER BY timestamp ASC"

Which confirms that data should have been displayed in the UI - it didn't.

So next I compared the SQL to the columns being selected in the code block - scroll below.

SELECT 
	timestamp,
	type,
	CASE typeof(domain) WHEN 'integer' THEN (SELECT domain FROM domain_by_id d WHERE d.id = q.domain) ELSE domain END domain, 
	CASE typeof(client) WHEN 'integer' THEN (   SELECT CASE TRIM(name) WHEN '' THEN c.ip ELSE c.name END name FROM client_by_id c WHERE c.id = q.client ) ELSE client END client,
	CASE typeof(forward) WHEN 'integer' THEN (SELECT forward FROM forward_by_id f WHERE f.id = q.forward) ELSE forward END forward, 
	status, 
	reply_type, 
	reply_time, 
	dnssec 
FROM query_storage q 
WHERE 
	timestamp >= :from AND 
	timestamp <= :until AND 
	status IN (1,2,14,3,4,5,6,7,8,9,10,11,12,13,15,16) 
ORDER BY timestamp ASC

As you can see, the SQL ends on DNSSec column selection, but the code block has an additional client_id (lines 117-127 of api_db.php), which is btw not being JSON encoded on line 130.

// Format, encode, transform each field (if necessary).
$time = $row["timestamp"];
$query_type = getQueryTypeStr($row["type"]); // Convert query type ID to name
$domain = utf8_encode(str_replace("~"," ",$row["domain"]));
$client = $row["client"];
$status = $row["status"];
$destination = utf8_encode($row["forward"]);
$reply_type = $row["reply_type"];
$reply_time = $row["reply_time"];
$dnssec = $row["dnssec"];
$client_id = $row["client_id"];

// Insert into array and output it in JSON format
echo json_encode([$time, $query_type, $domain, $client, $status, $destination, $reply_type, $reply_time, $dnssec]);

Commenting out line 127 resolves this issue.

5

Which is exactly what I did (except deleting instead of commenting) to fix the issue :slightly_smiling_face:

I got data all the time despite the offending client_id.

But glad it's solved now.

6

While the code clearly suggests that there is something amiss, I still cannot reproduce your issue via my Pi-hole's UI:
My Long-term Data Query Log fully populates each and every time, regardless what date and time range I select.

I wonder what is different in your environment that would trigger that specific bug in your UI?

Also, I wonder why for you, server-side api_db.php would trigger a client-side...

7

It has to do with this config on php.ini

display_errors = On

If turned off, the javascript alert goes away and data is displayed.

2 Likes
8

To complement this:
Grepping for Undefined index: client_id in my /var/log/lighttpd/error-pihole.log yielded a hit count of 7,462. :wink:

9

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.