MySQL error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"

Configuration
1

Hi everyone,

I have a couple automations that send Telegram messages with Unicode characters:

- alias: 'HASS started'
  trigger:
    platform: homeassistant
    event: start
  action:
    - service: notify.telegram
      data_template:
        message:
          "Home Assistant started 🥾"

This always worked. But recently, it throws errors like this:

2022-07-22 08:42:54 ERROR (Recorder) [homeassistant.components.recorder.core] Unhandled database error while processing task EventTask(event=<Event call_service[L]: domain=notify, service=telegram, service_data=message=Home Assistant started 🥾
Version: 2022.7.6
Uptime: 2022-07-22T06:42:47+00:00>): (MySQLdb.OperationalError) (1267, "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")
[SQL: SELECT event_data.data_id 
FROM event_data 
WHERE event_data.hash = %s AND event_data.shared_data = %s]
[parameters: (2893548747, '{"domain":"notify","service":"telegram","service_data":{"message":"Home Assistant started 🥾\\nVersion: 2022.7.6\\nUptime: 2022-07-22T06:42:47+00:00"}}')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1267, "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")

The database is MariaDB 10 on a Synology NAS:

image

image

I do understand that it is a problem with Unicode encoded characters. But I do not understand how to get rid of this problem…

Have a nice day!

Thorsten

2

Found the solution. Charset within the connection string must be set to utf8mb4:

mysql://user:password@SERVER_IP/DB_NAME?charset=utf8mb4

Docs: Recorder - Home Assistant
Github issue: https://github.com/home-assistant/core/issues/75126

3 Likes