Hello all,
Can someone help me figure out why the subject is occuring when I try to execute the below query:
I am logged into home assistant through my ddns setup through nginx proxy. I don’t think that matters but I may be wrong.
SELECT
entity_id,
COUNT(*) AS cnt
FROM states
GROUP BY
entity_id
ORDER BY
COUNT(*) DESC;
Also, I have the below in my config.yaml (also not sure if that matters)
http:
use_x_forwarded_for: true
trusted_proxies:
- 172.30.33.0/24
jumo
(jumo)
November 14, 2023, 1:18pm
2
I dont know if it helps, but I had the same error because entity_id
is actually NULL.
tholu
November 27, 2023, 8:39am
3
I have same issue. I found that when removing all LF/CR chars in the query and replace them by a space so all is in a single row, the query works.
To me it looks like the query prompt does not accept anymore line breaks!?!
tholu
December 8, 2023, 5:27pm
4
Actually it is an issue with the SQlite Addon that is heavily discussed in Github and not fixed yet with V4.0.0. There is a link to an older 3.9.2 version that still works with multiple lines. Solution is to restore the old Addon version, disable auto-updates of the Add on and wait until it is fixed in future:
opened 03:11PM - 20 Oct 23 UTC
I'm failing to run any queries from the SQLite Web interface, everything returns… "400: Bad Request", e.g. the queries listed [here](https://community.home-assistant.io/t/how-to-keep-your-recorder-database-size-under-control/295795/126) such as:
```sql
SELECT
COUNT(*) AS cnt,
COUNT(*) * 100 / (SELECT COUNT(*) FROM states) AS cnt_pct,
states_meta.entity_id
FROM states
INNER JOIN states_meta ON states.metadata_id=states_meta.metadata_id
GROUP BY states_meta.entity_id
ORDER BY cnt DESC
```
The addon itself seems to be configured properly, there's nothing in the addon log that looks like an error to me and the web console shows it found /config/home-assistant_v2.db with 13 tables, 20 indexes and 2GB size.
But the HA Core logs show:
```
Logger: homeassistant.components.http.security_filter
Source: components/http/security_filter.py:66
Integration: HTTP (documentation, issues)
First occurred: 10:02:23 (3 occurrences)
Last logged: 10:04:34
Filtered a request with unsafe byte query string: /query/?sql=SELECT%0D%0A++COUNT%28*%29+AS+cnt%2C%0D%0A++COUNT%28*%29+*+100+%2F+%28SELECT+COUNT%28*%29+FROM+states%29+AS+cnt_pct%2C%0D%0A++states_meta.entity_id%0D%0AFROM+states%0D%0AINNER+JOIN+states_meta+ON+states.metadata_id%3Dstates_meta.metadata_id%0D%0AGROUP+BY+states_meta.entity_id%0D%0AORDER+BY+cnt+DESC
```
Log:
```
s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service base-addon-banner: starting
-----------------------------------------------------------
Add-on: SQLite Web
Explore your SQLite database
-----------------------------------------------------------
Add-on version: 3.10.0
You are running the latest version of this add-on.
System: Home Assistant OS 11.0 (aarch64 / raspberrypi4-64)
Home Assistant Core: 2023.10.3
Home Assistant Supervisor: 2023.10.0
-----------------------------------------------------------
Please, share the above information when looking for help
or support in, e.g., GitHub, forums or the Discord chat.
-----------------------------------------------------------
s6-rc: info: service base-addon-banner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service base-addon-log-level: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service base-addon-log-level successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service init-sqlite-web: starting
s6-rc: info: service init-nginx: starting
s6-rc: info: service init-sqlite-web successfully started
s6-rc: info: service sqlite-web: starting
s6-rc: info: service sqlite-web successfully started
s6-rc: info: service init-nginx successfully started
s6-rc: info: service nginx: starting
s6-rc: info: service nginx successfully started
s6-rc: info: service legacy-services: starting
[09:50:48] INFO: Starting SQLite Web...
s6-rc: info: service legacy-services successfully started
* Serving Flask app 'sqlite_web.sqlite_web'
* Debug mode: off
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
* Running on http://127.0.0.1:8080
Press CTRL+C to quit
[09:50:50] INFO: Starting NGINX...
127.0.0.1 - - [19/Oct/2023 09:50:54] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:00] "GET /event_data/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:22] "GET /states/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:27] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:31] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:02] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:14] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:37] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:47] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:54:28] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:54:38] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:34] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:36] "GET /states/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:37] "GET /event_data/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:38] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:02:19] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:02:33] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:04:31] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:05:00] "GET / HTTP/1.1" 200 -
```
Thanks!
2 Likes