Recorder component with MySql 8.4+

[rutkai]

The problem

Mysql version 8.4 was released last week and updating it broke the recorder component.

The change that caused the issue was deprecating the native password authentication plugin. Error:

2024-05-06 09:38:10.248 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (retrying in 3 seconds)
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
[...omitted lines...]
File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 195, in __init__
super().__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1524, "Plugin 'mysql_native_password' is not loaded")
(Background on this error at: https://sqlalche.me/e/20/e3q8)

But in 8.4 it is turned off by default and got deprecated: https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html#mysql-nutshell-additions https://dev.mysql.com/doc/refman/8.4/en/native-pluggable-authentication.html It can be re-enabled, but it is a temporary fix as it's going to be removed in the long run.

I checked the code and SQLAlchemy connection should be changed (probably) to switch to a different plugin: https://stackoverflow.com/a/51789675/1531853 Here: https://github.com/home-assistant/core/blob/dev/homeassistant/components/recorder/core.py#L1407

Connection string, I used: mysql://homeassistant:thisissecret@127.0.0.1:3306/homeassistant?charset=utf8

What version of Home Assistant Core has the issue?

I tested it with 2024.5.*, but I think all of them.

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant Container

Integration causing the issue

recorder

Link to integration documentation on our website

No response

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

[home-assistant[bot]]

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!

Code owner commands

Code owners of `recorder` can trigger bot actions by commenting: - `@home-assistant close` Closes the issue. - `@home-assistant rename Awesome new title` Renames the issue. - `@home-assistant reopen` Reopen the issue. - `@home-assistant unassign recorder` Removes the current integration label and assignees on the issue, add the integration domain after the command. - `@home-assistant add-label needs-more-information` Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue. - `@home-assistant remove-label needs-more-information` Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

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

[jurgenweber]

yeah, ran into this today; --mysql-native-password=ON solved the immediate problem but we do have a problem here.

[ahb-bs]

V9.0 removes this workaround. HA is now unable to use Recorder - I'm looking into why others aren't having his issue and what is causing the auth not to use new methods.

[jurgenweber]

I hear you, if this was a global problem we would have a lot more attention on this problem, so maybe it is something unique with our setups?

My config;

#https://home-assistant.io/components/recorder/
recorder:
  db_url: !secret recorder_db_url
  purge_keep_days: 30

recorder_db_url: "mysql://user:pass@hass-mysql.space-marine.org/hass?charset=utf8mb4"

I am using the bitnami mysql helm chart to host mysql inside of a self hosted k8s cluster using k3s. Here is the config;

# Primary database configuration
primary:
  configuration: |-
    [mysqld]
    authentication_policy='{{- .Values.auth.authenticationPolicy | default "* ,," }}'
    disable-log-bin # added by me
    skip-log-bin # added by me
    # The syntax 'skip_slave_start' is deprecated and will be removed in a future release. Please use skip_replica_start instead.
    skip_replica_start
    skip-name-resolve
    explicit_defaults_for_timestamp
    basedir=/opt/bitnami/mysql
    plugin_dir=/opt/bitnami/mysql/lib/plugin
    port={{ .Values.primary.containerPorts.mysql }}
    mysqlx={{ ternary 1 0 .Values.primary.enableMySQLX }}
    mysqlx_port={{ .Values.primary.containerPorts.mysqlx }}
    socket=/opt/bitnami/mysql/tmp/mysql.sock
    datadir=/bitnami/mysql/data
    tmpdir=/opt/bitnami/mysql/tmp
    max_allowed_packet=16M
    bind-address=*
    pid-file=/opt/bitnami/mysql/tmp/mysqld.pid
    log-error=/opt/bitnami/mysql/logs/mysqld.log
    character-set-server=utf8mb4 # added by me
    collation-server=utf8mb4_unicode_ci # added by me
    mysql-native-password=ON # added by me
    slow_query_log=0
    long_query_time=10.0

    [client]
    port={{ .Values.primary.containerPorts.mysql }}
    socket=/opt/bitnami/mysql/tmp/mysql.sock
    default-character-set=UTF8
    plugin_dir=/opt/bitnami/mysql/lib/plugin

    [manager]
    port={{ .Values.primary.containerPorts.mysql }}
    socket=/opt/bitnami/mysql/tmp/mysql.sock
    pid-file=/opt/bitnami/mysql/tmp/mysqld.pid

[gimlichael]

Same problem here; using: docker run -d --name=mysql --restart unless-stopped -p 3306:3306 -e MYSQL_ROOT_PASSWORD=xxx -e MYSQL_USER=yyy -e MYSQL_PASSWORD=zzz -e MYSQL_DATABASE=home-assistant_v2 -e TZ=Europe/Copenhagen -v $(pwd)/data/mysql:/var/lib/mysql mysql:8.4.1.

Switched back to 8.3.0.

[jurgenweber]

any thoughts on why to so few people are not having this problem or are we just the only ones using MySQL? :)

[ahb-bs]

I believe it's the way we set up MYSQL when we first installed it. The user would have been created using the old auth method and so now it's caught up with us.

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'root_password'; Something like the above is needed to convert the user account away from native password

More details: https://php.watch/articles/fix-php-mysql-84-mysql_native_password-not-loaded

[rutkai]

Thanks @ahb-bs, it solved the problem, it seems we all skipped the migration of passwords before.