Recorder will not connect if mariadb URL includes ssl=true

[tgrecojr]

The problem

I have a MariaDB 10.6.17 instance that is set to require secure transport by setting the require_secure_transport directive to 1 (true).

Following the directions in the Recorder Documentation, I set the SSL=true according to:

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

When this is set, I get Access Denied errors. When I remove require_secure_transport to 0, this works.

This worked when I was on version 2024.2.x, but started failing when I upgraded to 2024.3.1.

What version of Home Assistant Core has the issue?

core-2024.3.1

What was the last working version of Home Assistant Core?

core-2024.2.x

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

Logger: homeassistant.setup
Source: setup.py:266
First occurred: 6:39:29 PM (5 occurrences)
Last logged: 6:39:38 PM

Setup failed for 'recorder': Integration failed to initialize.
Setup failed for 'history': (DependencyError(...), 'Could not setup dependencies: recorder')
Setup failed for 'energy': (DependencyError(...), 'Could not setup dependencies: history, recorder')
Setup failed for 'logbook': (DependencyError(...), 'Could not setup dependencies: recorder')
Setup failed for 'default_config': (DependencyError(...), 'Could not setup dependencies: energy, history, logbook')

Logger: homeassistant.setup
Source: setup.py:240
First occurred: 6:39:33 PM (4 occurrences)
Last logged: 6:39:38 PM

Unable to set up dependencies of 'history'. Setup failed for dependencies: ['recorder']
Unable to set up dependencies of 'energy'. Setup failed for dependencies: ['history', 'recorder']
Unable to set up dependencies of 'logbook'. Setup failed for dependencies: ['recorder']
Unable to set up dependencies of 'default_config'. Setup failed for dependencies: ['energy', 'history', 'logbook']

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:949
integration: Recorder (documentation, issues)
First occurred: 6:38:58 PM (10 occurrences)
Last logged: 6:39:26 PM

Error during connection setup: (MySQLdb.OperationalError) (1045, "Access denied for user 'hassio'@'' (using password: YES)") (Background on this error at: https://sqlalche.me/e/20/e3q8) (retrying in 3 seconds)
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 145, in init
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3282, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 674, in init
self.__connect()
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
with util.safe_reraise():
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 643, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 616, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/MySQLdb/init.py", line 121, in Connect
return Connection(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 195, in init
super().init(*args, **kwargs2)
MySQLdb.OperationalError: (1045, "Access denied for user 'hassio'@'' (using password: YES)")

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 949, in _setup_recorder
self._setup_connection()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1428, in _setup_connection
Base.metadata.create_all(self.engine)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 5825, in create_all
bind._run_ddl_visitor(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3232, in _run_ddl_visitor
with self.begin() as conn:
File "/usr/local/lib/python3.12/contextlib.py", line 137, in enter
return next(self.gen)
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3222, in begin
with self.connect() as conn:
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3258, in connect
return self._connection_cls(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 147, in init
Connection._handle_dbapi_exception_noconnection(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2422, in _handle_dbapi_exception_noconnection
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 145, in init
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3282, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 674, in init
self.__connect()
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
with util.safe_reraise():
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 643, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 616, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/MySQLdb/init.py", line 121, in Connect
return Connection(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 195, in init
super().init(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1045, "Access denied for user 'hassio'@'' (using password: YES)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

[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!

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)}}

[tgrecojr]

I found this in the SQLAlchemy issues. sqlalchemy/sqlalchemy#5692

When I updated the DB URL to include ?ssl_mode=REQUIRED and set require_secure_transport=1 on the database, it works as expected.

I think the documentation for Recorder may need to be updated based on this, however, in reading the issue, it seems to be driver dependent.