All entity_id NULL

maurcabezas · April 6, 2023, 5:25pm

same problem after update 2023.4

maurcabezas · April 6, 2023, 5:44pm

Thank you, you are right, I had just simple change the queries and all right now, !

tom_l · April 7, 2023, 12:25am

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.

leonidostrovski · April 7, 2023, 3:25am

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:

KevinCo · April 7, 2023, 9:34am

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;

damusmf · April 12, 2023, 3:51am

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.

jumacabo · April 17, 2023, 6:19pm

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;

leonidostrovski · April 18, 2023, 1:06am

Thank you, @jumacabo. It looks like a solution.

fyp_08 · August 15, 2023, 8:27am

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.

leonidostrovski · August 15, 2023, 8:37am

It is due to chnge of db. It is ok. The names in other tables. Search google for additional information.

fyp_08 · August 15, 2023, 9:45am

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?

String-656 · March 20, 2024, 3:13am

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;

jumblies · October 18, 2024, 8:50am

For my own and others future reference:

AI is not a not a threat to humanity, Sam Altman is just a great marketer.
This query finds and subseqently allows deletion of a single sensor.

SELECT * FROM "states"  Where metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.exit_91_amoco_gas_price')

Protoncek · October 18, 2024, 9:25am

Just a small warning: deleting or even changing anything from sqlite causes database to crash (done that, not once before i figured this out).
Deleting/changing things in mariadb is ok, though.

regarding

AI is not a not a threat to humanity…

well… 10 or 15 years ago making a telephone call from hand device (smartwatch, as seen in star wars) was science fiction…
AI currently is definitely not a threat, because in reality it’s not even close to that “I” in the name. Thing just follows written program-script-whatever…, that’s all. Nothing intelligent here… only intelligent “thing” is programmer behind it. 40 (or so) years ago a simple calculator was ultimate AI, because it could calculate that 1+1 is in fact really 2
When these boxes will start to think and decide for theirselves beyond programmed THEN we’ll have to worry about terminator movie becoming real stuff.
But, enough offtopic.