same problem after update 2023.4
Thank you, you are right, I had just simple change the queries and all right now, ! 
Second last paragraph of this section of the release notes https://www.home-assistant.io/blog/2023/04/05/release-20234/#database-scalability
That section also explains why the changes were made.
Thank to all. I happy know it is not problem on my db.
But still have my original problem.
I have sensor flood problem that increase my db size to GIGABYTES in 1-2 days. As result the HA stop normal response.
In past i ran:
SELECT entity_id, COUNT(*) as count FROM states GROUP BY entity_id ORDER BY count DESC LIMIT 60;
for detect what sensor did a problem, and manually delete all data from it.
What now ?
How detect what sensor have a lot of data???
I think many users in same problem:
1 Like
I had to modify some statements today to grab the data. Below is example of join of states with states_meta.
select states_meta.entity_id, state, last_updated_ts from homeassistant.states
inner join homeassistant.states_meta on states.metadata_id = states_meta.metadata_id
where states_meta.entity_id = ‘sensor.xxxxxx’
order by last_updated_ts desc;
1 Like
having a similar problem when using phpmyadmin. I have figured out the new syntax to find what i am looking for, but whenever i try to sort by state i just get this error message
and in the homeassistant logs themselves
Filtered a request with unsafe byte query string:
/api/hassio_ingress/xxxxx/index.php?route=/sql&db=homeassistant&table=states&sql_query=SELECT+%2A+FROM+states+WHERE+metadata_id+%3D+%28SELECT+metadata_id+FROM+states_meta+WHERE+entity_id+%3D+%27sensor.basement_th_temp%27%29++%0AORDER+BY+%60states%60.%60state%60+ASC&sql_signature=xxxxxb&session_max_rows=25&is_browse_distinct=0&ajax_request=true&ajax_page_request=true&_nocache=xxxxx&token=xxxxx
I have some finnicky sensors that report bogus data every time i restart HA. they are the acurite 433mhz temp sensor, but every reboot they say they are outrageous numbers, 400+ degrees and it messes with the graphs, just become a habit to quick search in phpmyadmin and adjust them since i dont reboot all that regularly. I tried to figure out why they do it to begin with but couldn’t so this workaround has been fine but now its a pain with sorting not working. I can click through the pages and will eventually find it, but that is not nearly as friendly as just sorting the state column.
With the new database schema you could use the following queries to detect which are the entities consuming most of the space in the 2 most crowded tables states and statistics
States
SELECT
states_meta.entity_id,
count(*) cnt
FROM
states
LEFT JOIN states_meta ON (
states.metadata_id = states_meta.metadata_id
)
GROUP BY
states_meta.entity_id
ORDER BY
cnt DESC;
Statistics
SELECT
statistics_meta.statistic_id,
count(*) cnt
FROM
statistics
LEFT JOIN statistics_meta ON (
statistics.metadata_id = statistics_meta.id
)
GROUP BY
statistics_meta.statistic_id
ORDER BY
cnt DESC;
19 Likes
Hi, I’m using Home Assistant to collect power consumption data for analysis with network devices as my final year project. Although it was working fine before, I’ve suddenly noticed that most of the columns in the states table now contain NULL values. I’m new to phpMyAdmin and SQL, and I need to extract the power consumption (W) and relative time instances. This information would be really helpful for advancing my project. Your responses would be invaluable.
It is due to chnge of db. It is ok. The names in other tables. Search google for additional information.
These are the databases currently in Home Assistant. Since I’m new to this, could you give me a reference point to rectify this issue?
This works, just change to “sensor.xxxx”
select states_meta.entity_id, state, last_updated_ts from harepository.states
inner join harepository.states_meta on states.metadata_id = states_meta.metadata_id
where states_meta.entity_id = "sensor.scale_1_percent"
order by last_updated_ts desc;