MariaDB - Illegal mix of collations for operation 'UNION' - Logbook won't load

[JustAmply]

The problem

Hey there!
The errorlog below appears since updating to 2022.6 in general. I have waited with creating this issue till #73191 was merged and released in 2022.6.4, but the issue sadly still appears.

I'm using the MariaDB-addon connected to the recorder, exactly like it's shown in the docs.

Since 2022.6 I can't access the logbook at all. The animation spins forever. Doesn't matter it's a singular entity logbook, or the logbook of all entities in the sidebar.
The history graph works without a problem though.

Illegal mix of collations for operation 'UNION' is the error, but I couldn't figure out why exactly.

Anybody have an idea what might be the problem here?

What version of Home Assistant Core has the issue?

core-2022.6.4

What was the last working version of Home Assistant Core?

core-2022.5.5

What type of installation are you running?

Home Assistant OS

Integration causing the issue

Recorder

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:95
Integration: Recorder (documentation, issues)
First occurred: 16:40:47 (1 occurrences)
Last logged: 16:40:47

Error executing query: (MySQLdb._exceptions.OperationalError) (1271, "Illegal mix of collations for operation 'UNION'") [SQL: 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, %s, %s, %s, %s, %s, %s, %s) AND ((JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) = %s OR JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) IS NULL) AND (JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) = %s OR JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) IS NULL) OR NOT (JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) IS NOT NULL AND CAST(JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) AS CHAR) LIKE %s OR JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) IS NOT NULL AND CAST(JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) AS CHAR) LIKE %s OR JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) IS NOT NULL AND CAST(JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) AS CHAR) LIKE %s OR JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) IS NOT NULL AND CAST(JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) AS CHAR) LIKE %s OR JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) IS NOT NULL AND CAST(JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) AS CHAR) LIKE %s OR JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) IS NOT NULL AND CAST(JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) AS CHAR) LIKE %s OR JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) IS NOT NULL AND CAST(JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), %s) AS CHAR) IN (%s, %s) OR JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) IS NOT NULL AND CAST(JSON_EXTRACT(CAST(events.event_data AS CHAR), %s) AS CHAR) IN (%s, %s) OR false = 1)) 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_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 NOT (states.entity_id IS NOT NULL AND CAST(states.entity_id AS CHAR) LIKE %s OR states.entity_id IS NOT NULL AND CAST(states.entity_id AS CHAR) LIKE %s OR states.entity_id IS NOT NULL AND CAST(states.entity_id AS CHAR) LIKE %s OR states.entity_id IS NOT NULL AND CAST(states.entity_id AS CHAR) IN (%s, %s) OR false = 1) ORDER BY time_fired] [parameters: (None, None, None, None, None, None, datetime.datetime(2022, 6, 8, 13, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 6, 8, 14, 40, 47, 450039, tzinfo=datetime.timezone.utc), 'call_service', 'logbook_entry', 'homeassistant_stop', 'homeassistant_start', 'script_started', 'homekit_state_change', 'ios.zone_entered', 'ios.zone_exited', 'automation_triggered', 'shelly.click', '$."entity_id"', 'null', '$."entity_id"', '$."entity_id"', 'null', '$."entity_id"', '$."entity_id"', '$."entity_id"', '"automation.%"', '$."entity_id"', '$."entity_id"', '"automation.%"', '$."entity_id"', '$."entity_id"', '"proximity.%"', '$."entity_id"', '$."entity_id"', '"proximity.%"', '$."entity_id"', '$."entity_id"', '"sun.%"', '$."entity_id"', '$."entity_id"', '"sun.%"', '$."entity_id"', '$."entity_id"', '"sensor.icloud3_event_log"', '"sensor.fritz_uptime"', '$."entity_id"', '$."entity_id"', '"sensor.icloud3_event_log"', '"sensor.fritz_uptime"', None, None, None, None, '$."icon"', '$."icon"', '$."icon"', '$."icon"', None, datetime.datetime(2022, 6, 8, 13, 0, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 6, 8, 14, 40, 47, 450039, tzinfo=datetime.timezone.utc), 'counter.%', 'proximity.%', 'sensor.%', 'sensor.%', '%"unit_of_measurement":%', '%"unit_of_measurement":%', 'automation.%', 'proximity.%', 'sun.%', 'sensor.icloud3_event_log', 'sensor.fritz_uptime')] (Background on this error at: https://sqlalche.me/e/14/e3q8)

Additional information

No response

[probot-home-assistant]

recorder documentation
recorder source
_{^{(message by IssueLinks)}}

[probot-home-assistant]

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!
_{^{(message by CodeOwnersMention)}}

[erkr]

Hi, I'm not experiencing this while running the recorder with The MariaDB add-on as well. So this combination by itself can work.

[bdraco]

Its likely the collations in your local database somehow differs between tables. When your tables were originally created they were likely created with the wrong character set and or collation. The recorder docs used to be quite unclear on what was needed. At some point in the past schema v21 migration was intended to get all your tables in the right collation if they were not created with the correct one since the old recorder docs did not provide much information on this. This likely failed due to a bug that was fixed in #70336 and your tables where never fixed to all have the same charset or collation

core/homeassistant/components/recorder/migration.py

Line 568 in 4435c64

if engine.dialect.name == SupportedDialect.MYSQL:

The events, states, event_data, and state_attributes all need need to use a charset of utf8mb4 and a collation of utf8mb4_unicode_ci or you'll get this error

core/homeassistant/components/recorder/db_schema.py

Line 257 in 4435c64

{"mysql_default_charset": "utf8mb4", "mysql_collate": "utf8mb4_unicode_ci"},

[bdraco]

Take a backup, check both the events, statistics_meta, and states tables to see if they need their collation changed (you can see this in phpmyadmin) then you can fix them manually by running the following:

events:
ALTER table events CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, LOCK=EXCLUSIVE

states:
ALTER table states CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, LOCK=EXCLUSIVE

statistics_meta
ALTER table statistics_meta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, LOCK=EXCLUSIVE

[bdraco]

I don't think it would be a good idea to force everyone to through another table collation change in a patch release so I'm dropping the milestone on this one

[JustAmply]

You are absolutely right!
The tables events and statistics_meta had a collation of utf8mb4_general_ci instead of utf8mb4_unicode_ci, but states had the correct collation.

After altering the collation, everything was fixed instantly!
You're a genius, bdraco! 🥇

My issue is fixed, but I don't know if there should maybe be a more robust and automatic check for this.

[bdraco]

Glad you got it sorted out!