All entity_id NULL

leonidostrovski · April 3, 2023, 8:51am

Hi
I use mariadb on synology.
Maybe someone can explain, why all values in column entity_id is NULL ???
In past I seen here names of sensors…

Thanks
Leonid

tom_l · April 3, 2023, 9:18am

Do a purge AND repack of your database.

leonidostrovski · April 3, 2023, 6:57pm

I spent a lot of time (days) for try take my DB size under control.
Many time all data removed by me and start from zero.
The last steps was:

Delete the db from mariadb - did not help only “NONE” in state entity_id
Delete the db from mariadb and in same time restore image from backup from ~4 days before. It resolved a problem.
But after short time I see again only “NONE” (as in attached picture)

In past I did purge (0 days) and repack many times

PLEASE HELP!!!

Thanks
Leonid

leonidostrovski · April 5, 2023, 12:38am

I start use mariadb on Proxmox instead of Synology, and I got same problem.
I execute
SELECT entity_id, COUNT(*) as count FROM states GROUP BY entity_id ORDER BY count DESC LIMIT 60;
and get ~300k of “NULL” rows…
The Recorder purge + repack (keep 0 days), reduce rows to ~0, but not resolve problem…

leonidostrovski · April 5, 2023, 1:53am

Additional time I restored HA OS from image. For now I can see normal status of DB

I did OS update from 10.0.rc2 to 10.0.rc3 - all looks good

I did Core update from 2023.3.6 to 2023.4.0b7 - all values changed to NULL see below

tom_l · April 5, 2023, 3:30am

Please only discuss the beta releases in the Home Assistant beta release discord channel.

Additionally, the database maintainer hangs out there so you are more likely to get a response.

matt_ha · April 6, 2023, 6:31am

i have the same Problem in the 2023.4 final …

quartstudio · April 6, 2023, 7:48am

Have exactly the same problem. All entity_id states are NULL in the latest version 2023.4. I’ve purged everything, reinstall recorder… but didn’t help.

Resulting in failed SQL queries for ‘state_id’:

SELECT * FROM states WHERE entity_id = 'sensor.smart_plug_15min' ORDER BY last_updated_ts DESC  LIMIT 1

Protoncek · April 6, 2023, 8:13am

It’s happening again… global change of DB format without any notification, warning… in “states” table all entity_id’s are “null”… so, i wonder… where did they move all entities and values to…?

matt_ha · April 6, 2023, 9:18am

the entity_id is now stored in the table “states_meta”…
and in the table “states” you can found “metadata_id” …

wish I had a deeper look before I drop the tables …

tom_l · April 6, 2023, 9:51am

As per the release notes, there are updated examples here:

quartstudio · April 6, 2023, 9:53am

So I guess there is no more direct entity_id calls and we can no longer use:

SELECT state FROM states WHERE entity_id = 'sensor.your_sensor_here' WHERE last_changed_ts...

Does anyone have the correct syntax for updated database?

tom_l · April 6, 2023, 9:55am

Yes see the examples in the link I posted above.

quartstudio · April 6, 2023, 9:56am

thanks it works!

Protoncek · April 6, 2023, 11:06am

Hm…as i’m not sql pro i don’t have a clue how to correct this to working version: (it’s mariadb database, in synology): sensor should show min (and max alternatively) temperature TODAY, so from midnight to right now.

- name: minimum outside temperature
  db_url: !secret database_url
  unit_of_measurement: °C
  column: "min_temp"
  query: >
    SELECT MIN(CAST(state AS DECIMAL(3, 1))) AS min_temp
    FROM states
    WHERE entity_id = 'sensor.outside_temperature'
    AND state IS NOT NULL
    AND state NOT IN ('unknown', 'unavailable')
    AND FROM_UNIXTIME(last_updated_ts) >= CURRENT_DATE();

quartstudio · April 6, 2023, 11:33am

try something like:

SELECT MIN(CAST(state AS DECIMAL(3, 1))) AS min_temp FROM states LEFT JOIN state_attributes ON (states.attributes_id = state_attributes.attributes_id) WHERE metadata_id = ( SELECT metadata_id FROM states_meta where entity_id = 'sensor.outside_temperature' );

Protoncek · April 6, 2023, 11:54am

Thanks!
now i approximately see the point… i must replace “where entity_id = ----” with another sql sentence, where i extract entity via metadata_id…
thanks again!

Spartacus · April 6, 2023, 1:50pm

Hi all,

can someone please help me? I am not very experiance in SQL and I have round about 100SQL queries in Grafana and nothing works anymore.

Can someone please translate this example to the new format?

SELECT
  last_updated_ts AS "time",
  '°C' AS metric,
  CAST(state AS float) AS "InnenTemp"
FROM states
WHERE
  state <> 'unavailable' and
  state <> 'unknown' and
  entity_id = 'sensor.1w_ds2438t'
ORDER BY last_updated_ts

I never saw something like this, how is it possible to make so significant changes from one day to annother! I am very frustarated about this.

Thanks in advanced!
Spartacus

quartstudio · April 6, 2023, 2:13pm

Try this:

SELECT
  last_updated_ts AS "time",
  '°C' AS metric,
  CAST(state AS float) AS "InnenTemp"
FROM states
WHERE
  state <> 'unavailable' and
  state <> 'unknown' and
  metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.1w_ds2438t')
ORDER BY last_updated_ts;

The point is, we should stop using entity_id (cause they are saving disk space, lol). Instead we can reference with metadata_id and with another query.

So instead of:

WHERE entity_id = 'sensor.your_sensor'

use:

WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.your_sensor')

Spartacus · April 6, 2023, 2:31pm

Hi all,

many, many thanks for the very quick response. I also got it working! Now I have to change all the Grafana dashboard Querys. I do not understand, why people are changing a running system in such a significant way…but maybe I missed the announcements!