Hi All,
I’m hoping this is an easy fix, I have my recorder logging to MariaDB so everything is in a SQL database. After upgrading to 2022.6 it would appear I can’t filter logbook by entity. When I go onto the logbook I can see all entries but can’t filter down and it logs the below in the logs. I have upgraded to 2022.6.4 and still have the same issue.
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:95
Integration: Recorder (documentation, issues)
First occurred: 09:21:38 (1 occurrences)
Last logged: 09:21:38
Error executing query: (MySQLdb._exceptions.OperationalError) (1176, "Key 'ix_states_entity_id_last_updated' doesn't exist in table 'states'") [SQL: WITH anon_1 AS (SELECT anon_2.context_id AS context_id FROM (SELECT events.context_id AS context_id FROM events LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id WHERE events.time_fired > %s AND events.time_fired < %s AND events.event_type IN (%s, %s, %s) AND (JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) IN (%s) OR JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) IN (%s)) UNION ALL SELECT states.context_id AS context_id FROM states FORCE INDEX (ix_states_entity_id_last_updated) WHERE states.last_updated > %s AND states.last_updated < %s AND states.entity_id IN (%s)) AS anon_2 GROUP BY anon_2.context_id) SELECT events.event_id AS event_id, events.event_type AS event_type, events.event_data AS event_data, events.time_fired AS time_fired, events.context_id AS context_id, events.context_user_id AS context_user_id, events.context_parent_id AS context_parent_id, event_data.shared_data AS shared_data, %s AS state_id, %s AS state, %s AS entity_id, %s AS icon, %s AS old_format_icon, %s AS context_only FROM events LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id WHERE events.time_fired > %s AND events.time_fired < %s AND events.event_type IN (%s, %s, %s) AND (JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) IN (%s) OR JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) IN (%s)) UNION ALL SELECT %s AS event_id, %s AS event_type, %s AS event_data, states.last_updated AS time_fired, states.context_id AS context_id, states.context_user_id AS context_user_id, states.context_parent_id AS context_parent_id, %s AS shared_data, states.state_id AS state_id, states.state AS state, states.entity_id AS entity_id, CASE JSON_EXTRACT(CAST(state_attributes.shared_attrs AS CHAR), %s) WHEN 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(CAST(state_attributes.shared_attrs AS CHAR), %s)) END AS icon, CASE JSON_EXTRACT(CAST(states.attributes AS CHAR), %s) WHEN 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(CAST(states.attributes AS CHAR), %s)) END AS old_format_icon, %s AS context_only FROM states FORCE INDEX (ix_states_entity_id_last_updated) LEFT OUTER JOIN states AS old_state ON states.old_state_id = old_state.state_id LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id WHERE states.last_updated > %s AND states.last_updated < %s AND old_state.state_id IS NOT NULL AND states.state != old_state.state AND states.state IS NOT NULL AND ((states.entity_id NOT LIKE %s AND states.entity_id NOT LIKE %s AND states.entity_id NOT LIKE %s) OR ((states.entity_id LIKE %s) AND (state_attributes.shared_attrs NOT LIKE %s OR states.attributes NOT LIKE %s))) AND (states.last_updated = states.last_changed OR states.last_changed IS NULL) AND states.entity_id IN (%s) UNION ALL SELECT events.event_id AS event_id, events.event_type AS event_type, events.event_data AS event_data, events.time_fired AS time_fired, events.context_id AS context_id, events.context_user_id AS context_user_id, events.context_parent_id AS context_parent_id, event_data.shared_data AS shared_data, %s AS state_id, %s AS state, %s AS entity_id, %s AS icon, %s AS old_format_icon, %s AS context_only FROM anon_1 LEFT OUTER JOIN events FORCE INDEX (ix_events_context_id) ON anon_1.context_id = events.context_id LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id UNION ALL SELECT %s AS event_id, %s AS event_type, %s AS event_data, states.last_updated AS time_fired, states.context_id AS context_id, states.context_user_id AS context_user_id, states.context_parent_id AS context_parent_id, %s AS shared_data, states.state_id AS state_id, states.state AS state, states.entity_id AS entity_id, %s AS icon, %s AS old_format_icon, %s AS context_only FROM anon_1 LEFT OUTER JOIN states FORCE INDEX (ix_states_context_id) ON anon_1.context_id = states.context_id ORDER BY time_fired] [parameters: (datetime.datetime(2022, 6, 8, 7, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 6, 8, 8, 21, 38, 969347, tzinfo=datetime.timezone.utc), 'logbook_entry', 'script_started', 'automation_triggered', '$."entity_id"', '"group.adults"', '$."entity_id"', '"group.adults"', datetime.datetime(2022, 6, 8, 7, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 6, 8, 8, 21, 38, 969347, tzinfo=datetime.timezone.utc), 'group.adults', None, None, None, None, None, None, datetime.datetime(2022, 6, 8, 7, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 6, 8, 8, 21, 38, 969347, tzinfo=datetime.timezone.utc), 'logbook_entry', 'script_started', 'automation_triggered', '$."entity_id"', '"group.adults"', '$."entity_id"', '"group.adults"', None, None, None, None, '$."icon"', '$."icon"', '$."icon"', '$."icon"', None, datetime.datetime(2022, 6, 8, 7, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 6, 8, 8, 21, 38, 969347, tzinfo=datetime.timezone.utc), 'counter.%', 'proximity.%', 'sensor.%', 'sensor.%', '%"unit_of_measurement":%', '%"unit_of_measurement":%', 'group.adults', None, None, None, None, None, '1', None, None, None, None, None, None, '1')] (Background on this error at: https://sqlalche.me/e/14/e3q8)
