Upgrade to 2023.4.4 (with mariadb), no more history, slow queries on db, is it the migration?

Installation
21

Examples linked in the release notes and here https://data.home-assistant.io/

1 Like
22

Querying has gotten a bit more complex with these recent releases. Below is an example of getting the last day of changes to a sensor, regardless whether the state or attribute changed (old query shown below new). I use Postgresql, not Mariadb so your SQL syntax will probably be different. I have a lot of queries to update… Good hunting!

-- get freezer temperature for last 24 hours
SELECT
    states.state,
    to_char(to_timestamp(states.last_updated_ts), 'YYYY-MM-DD HH24:MI:SS') AS last_updated_ts,
    to_char(to_timestamp(states.last_changed_ts), 'YYYY-MM-DD HH24:MI:SS') AS last_changed_ts,
    state_attributes.shared_attrs,
    states.context_id_bin
FROM
    states
    LEFT JOIN states_meta ON (states.metadata_id = states_meta.metadata_id)
    LEFT JOIN state_attributes ON (states.attributes_id = state_attributes.attributes_id)
WHERE (states_meta.entity_id = 'sensor.freezer_govee_temperature'
    AND to_timestamp(states.last_updated_ts) > now() - interval '1 days')
ORDER BY
    states.last_updated_ts DESC;

SELECT
    *
FROM
    states
WHERE (entity_id = 'sensor.freezer_govee_temperature'
    AND last_updated > now() - interval '1 days')
ORDER BY
    last_updated DESC;

freezer-query
freezer-query1920×1551 253 KB

23

More complex, but I supposed that is the price to pay in order to reduce the DB size (more relational), by the way nice example! Thanks!

24

Wowwwww it’s everything!! Thanks a lot!

25

I’m experiencing the problem again after migrating from 2023.4.5 to 2023.4.6. That’s frustrating.

I’m rolling back to 2023.4.5

26

For anyone who has problem with mariadb recorded. I have to add indexes to tables (my schema version was at 45, but was missing indexes). I had missing ix_events_context_id_bin, ix_events_context_id in tables events,states and for some reason i have ix_events_event_type_id_time_fired_ts as non visible index, so it was not allowed to use directly in SQL.

So i have to:

  • add missing keys in states from schema above
  • make sure that keys are visible
  • read system logs for other database schema differences
27

Hi, could you share the SQL queries to fix this? I’ve got the same issue but I’m not up to date on how indexing is configured.

Thanks in advance!