core: MySQL recorder fail on 2022.7 because libmysqlclient is missing the caching_sha2_password plugin

🤚 I have the same issue! 👇 Read below

Hi there! Sorry to read you are experiencing the same issue as more people here. Nobody likes having issues 😞

Finding an issue on a GitHub issue tracker that matches your problem is kinda nice: At least you know you are not alone. So, let’s leave a commit with: “Yeah, I have the same issue”, or “+1!!!”. 🚫 No!

Please do not create “I have the same issue” comments!

Not just this issue or this project, but anywhere on GitHub or any issue tracker on the internet even. It doesn’t add to triaging the issue. It only generates noise when reading / triaging the case.

What to do instead?

Instead, go to the top of the issue, and add a 👍 emoji reaction. That way, we can still see that you (and how many others) experience this issue while keeping the issue threads nice and clean.

Thanks! ❤️

…/Frenck

You can also add the plugin with

docker exec -it homeassistant /bin/bash
apk add mariadb-connector-c

You’ll need to turn off protected mode when using the ssh web terminal to do that or use https://developers.home-assistant.io/docs/operating-system/debugging/

Unrelated but I started getting following error with Recorder and MySQL right after updating to 2022.7:

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:743 
Integration: Recorder (documentation, issues) 
First occurred: 09:54:45 (11 occurrences) 
Last logged: 09:54:45

Unhandled database error while processing task EventTask(event=<Event call_service[L]: domain=notify, service=mobile_app_alexeys_macbook, service_data=message=🛡 Saturn alarm armed Night Set, data=group=alarm>): (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: (1010881915, '{"domain":"notify","service":"mobile_app_alexeys_macbook","service_data":{"message":"🛡 Saturn alarm armed Night Set","data":{"group":"alarm"}}}')] (Background on this error at: https://sqlalche.me/e/14/e3q8)
Unhandled database error while processing task EventTask(event=<Event call_service[L]: domain=notify, service=telegram_marina, service_data=message=🛡 Saturn alarm armed Night Set, data=group=alarm>): (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: (1114235070, '{"domain":"notify","service":"telegram_marina","service_data":{"message":"🛡 Saturn alarm armed Night Set","data":{"group":"alarm"}}}')] (Background on this error at: https://sqlalche.me/e/14/e3q8)
Unhandled database error while processing task EventTask(event=<Event call_service[L]: domain=telegram_bot, service=send_message, service_data=target=-1001497697743, message=🛡 Saturn alarm armed Night Set>): (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: (3456934724, '{"domain":"telegram_bot","service":"send_message","service_data":{"target":-1001497697743,"message":"🛡 Saturn alarm armed Night Set"}}')] (Background on this error at: https://sqlalche.me/e/14/e3q8)
Unhandled database error while processing task EventTask(event=<Event call_service[L]: domain=telegram_bot, service=send_message, service_data=target=139670929, message=🛡 Saturn alarm armed Night Set>): (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: (735904095, '{"domain":"telegram_bot","service":"send_message","service_data":{"target":139670929,"message":"🛡 Saturn alarm armed Night Set"}}')] (Background on this error at: https://sqlalche.me/e/14/e3q8)
Unhandled database error while processing task EventTask(event=<Event call_service[L]: domain=telegram_bot, service=send_message, service_data=target=526011943, message=🛡 Saturn alarm armed Night Set>): (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: (4195239706, '{"domain":"telegram_bot","service":"send_message","service_data":{"target":526011943,"message":"🛡 Saturn alarm armed Night Set"}}')] (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 above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 610, in _process_one_task_or_recover
    return task.run(self)
  File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 211, in run
    instance._process_one_event(self.event)
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 708, in _process_one_event
    self._process_non_state_changed_event_into_session(event)
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 773, in _process_non_state_changed_event_into_session
    if data_id := self._find_shared_data_in_db(data_hash, shared_data):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 743, in _find_shared_data_in_db
    if data_id := self.event_session.execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/lambdas.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  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)
sqlalchemy.exc.OperationalError: (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: (426407653, '{"domain":"notify","service":"everyone","service_data":{"message":"🛡 Saturn alarm armed Night Set","data":{"group":"alarm"}}}')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Looks like in version 2022.7.0 the mysql driver stopped working with caching_sha2_password.

not sure why this is closed 2022.7.7 shows the problem. based on https://github.com/home-assistant/docker/releases was released 10 days ago. and 2022.7.7 where 2 days ago. which sounds like 2022.7.7 has the fix but its not working.

Edit:

looks like the variables here have not been updated: https://github.com/home-assistant/core/blob/2022.7.7/build.yaml#L3-L8

The caching_sha2_password is recommended as per https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

You can change the password of the user to one with a newer encryption scheme that the library supports

Example here

https://stackoverflow.com/questions/49194719/authentication-plugin-caching-sha2-password-cannot-be-loaded

@alphasixtyfive, @TheFes: Please open a separate issue for the collation error

It is, they finally updated the base container.

Still same problem in 2022.7.5. I guess it is waiting for a new release of https://github.com/home-assistant/docker/releases