MySQL issue with Core2024.3.0 #112663

MasterCATZ · 2024-03-08T00:18:59Z

The problem

I just did an HA update today and now MySQL refuses to allow connection
no passwords or host names etc have been changed mysql settings have not been played with


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

[Warning] Access denied for user ‘homeassistant’@‘homeassistant.fritz.box’ (using password: YES)

its a Galera Cluster  with offsite replication 

Core2024.3.0
Supervisor2024.02.1
Operating System12.0
Frontend20240306.0

and after about 100 restarts using quick and full trying to change config combinations and even full power off / on , gave up and rolled back to a part backup from 10 hours ago

Core2024.2.5
Supervisor2024.02.1
Operating System12.0
Frontend20240207.1

and it connected again somethings up with Core2024.3.0

assuming

Fix sql integration no recorder (@gjohansson-ST - #111263)

creating a proxmox snapshot of current machine

What version of Home Assistant Core has the issue?

Core2024.3.0

What was the last working version of Home Assistant Core?

Core2024.2.5

What type of installation are you running?

Home Assistant OS

Integration causing the issue

No response

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

I have seen others having issues with ssl no longer working ie) can not use webmin anymore can no longer remote in

The text was updated successfully, but these errors were encountered:

ulf-melin-sveasolar · 2024-03-08T06:49:40Z

I have basically the same experience, mysql connection did not work after installing core 2024.3.0, but started working again as soon as I rolled back to 2024.2.5

I am using Raspberry Pi 3b installation and a mysql in Azure, with ?ssl=true

dkrebs · 2024-03-08T07:05:29Z

~~Not sure it it's the same problem, but after upgrade to Core2024.3.0 recorder also stopped working completely for me.~~

2024-03-08` 08:02:13.808 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2024-03-08 08:03:01.866 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2024-03-08 08:04:01.969 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
...

~~When enabling debug mode there is no more information about the issue.~~

ok, this was probably unrelated - my MariaDb version seemed to be too old

ivercellino · 2024-03-08T12:51:34Z

I think something has definety changed in 2024.3.0 regarding the database conenctions.
I have more than 60 sql sensors configured with the standard sql integrations connected to a MariaDB running on the same Raspberry4.
After the upgrade most of them have become unvailable right from the startup in a random way.
Sometimes reloading helps, sometimes not.
Reverted back to 2024.2.5 and everything is fine again.

So obviuosly something is not running smoth with db connections.

flechaig · 2024-03-08T18:02:49Z

Same issue here:
ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (MySQLdb.OperationalError) (3159, 'Connections using insecure transport are prohibited while --require_secure_transport=ON.')
Works fine in 2024.2.5.
I think it's an issue with SSL/TLS but can't tell more details for now.

home-assistant · 2024-03-09T21:13:43Z

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

isewise · 2024-03-10T16:56:09Z

A recorder issue has popped up for me as well.
Error dump below:

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:949
integration: recorder (documentation, issues)
First occurred: 9:40:40 AM (10 occurrences)
Last logged: 9:41:07 AM

Error during connection setup: (MySQLdb.OperationalError) (2002, "Can't connect to server on '192.168.1.25' (115)") (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: (2002, "Can't connect to server on '192.168.1.25' (115)")

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) (2002, "Can't connect to server on '192.168.1.25' (115)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)

MasterCATZ · 2024-03-15T10:34:53Z

Core
2024.2.5
Supervisor
2024.03.0
Operating System
12.1
Frontend
20240207.1

updated the OS and it still works just about to try core again

Core
2024.3.1
Supervisor
2024.03.0
Operating System
12.1
Frontend
20240307.0

mysql broke again rolled back to working

Core
2024.2.5
Supervisor
2024.03.0
Operating System
12.1
Frontend
20240207.1

isewise · 2024-03-17T16:42:49Z

A recorder issue has popped up for me as well.
Error dump below:

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:949
integration: recorder (documentation, issues)
First occurred: 9:40:40 AM (10 occurrences)
Last logged: 9:41:07 AM

Error during connection setup: (MySQLdb.OperationalError) (2002, "Can't connect to server on '192.168.1.25' (115)") (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: (2002, "Can't connect to server on '192.168.1.25' (115)")

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) (2002, "Can't connect to server on '192.168.1.25' (115)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)

To give further details on my issue: I upgraded to 2024.3.0 and everything was okay for a few days. Then I noticed I couldn't pull any log or recorder history for entities.

I checked my NAS, where I store my HA recorder history, and the entire database was gone. Nothing changed on my NAS side of things either.

I have no idea why the entire database would be deleted and I've never had this issue before

MasterCATZ · 2024-03-28T10:45:41Z

broke again trying Core 2024.3.3 and rolled back to 2024.2.5 , and still no ones looked into it

Core
2024.3.3
Supervisor
2024.03.1
Operating System
12.1
Frontend
20240307.0

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 'homeassistant'@'bb4b53317d6d4bbba30b7805c9020979.fritz.box' (using password: YES)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-28 20:34:57.749 INFO (MainThread) [homeassistant.components.websocket_api.http.connection] [139723670159424] Received unknown command: energy/info
2024-03-28 20:34:57.860 INFO (MainThread) [homeassistant.components.websocket_api.http.connection] [139723670159424] Received unknown command: energy/info
2024-03-28 20:34:57.864 INFO (MainThread) [homeassistant.components.websocket_api.http.connection] [139723670159424] Received unknown command: energy/info
2024-03-28 20:34:57.868 INFO (MainThread) [homeassistant.components.websocket_api.http.connection] [139723670159424] Received unknown command: energy/info
2024-03-28 20:35:00.446 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (MySQLdb.OperationalError) (1045, "Access denied for user 'homeassistant'@'bb4b53317d6d4bbba30b7805c9020979.fritz.box' (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 'homeassistant'@'bb4b53317d6d4bbba30b7805c9020979.fritz.box' (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/poo

isewise · 2024-03-28T18:08:53Z

I decided to try again and updated 2 days ago.

So far, its going well, but I think this is the same experience I had before it deleted the whole database.

MasterCATZ · 2024-03-29T06:18:29Z

guess its a good thing my db denies it then , I might have to start doing archive backups then
currently I just have a offsite Galera Cluster , tho they are on a ZFS FS so I guess I could still roll back

flechaig · 2024-04-04T07:44:40Z

Hi,
I solved my own issue (may not work for everybody).
Due to changes in the mysqlclient Python library (starting 2.2.2), the parameters of the connection string must be adjusted. I added ssl_mode=REQUIRED and the connection is back again in HA 2024.4.0.
See PyMySQL/mysqlclient#700 for details.

Your MySQL connection string should look like something like this.
db_url: 'mysql://<user>:<password>@<server>/<database>?charset=utf8&ssl=True&ssl_mode=REQUIRED'

The help page should be updated to reflect this new requirement.

MasterCATZ · 2024-04-12T10:50:23Z

so add ssl_mode=REQUIRED

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

to

mysql://user:password@SERVER_IP/DB_NAME?charset=utf8mb4&ssl=true&ssl_mode=REQUIRED

Confirmed working

Core
2024.4.2
Supervisor
2024.04.0
Operating System
12.2
Frontend
20240404.1

mib1185 added integration: recorder database: mariadb database: mysql labels Mar 9, 2024

MasterCATZ closed this as completed Apr 12, 2024

github-actions bot locked and limited conversation to collaborators May 12, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MySQL issue with Core2024.3.0 #112663

MySQL issue with Core2024.3.0 #112663

MasterCATZ commented Mar 8, 2024 •

edited

ulf-melin-sveasolar commented Mar 8, 2024

dkrebs commented Mar 8, 2024 •

edited

ivercellino commented Mar 8, 2024

flechaig commented Mar 8, 2024

home-assistant bot commented Mar 9, 2024

isewise commented Mar 10, 2024

MasterCATZ commented Mar 15, 2024 •

edited

isewise commented Mar 17, 2024

MasterCATZ commented Mar 28, 2024 •

edited

isewise commented Mar 28, 2024

MasterCATZ commented Mar 29, 2024 •

edited

flechaig commented Apr 4, 2024

MasterCATZ commented Apr 12, 2024 •

edited

MySQL issue with Core2024.3.0 #112663

MySQL issue with Core2024.3.0 #112663

Comments

MasterCATZ commented Mar 8, 2024 • edited

The problem

What version of Home Assistant Core has the issue?

What was the last working version of Home Assistant Core?

What type of installation are you running?

Integration causing the issue

Link to integration documentation on our website

Diagnostics information

Example YAML snippet

Anything in the logs that might be useful for us?

Additional information

ulf-melin-sveasolar commented Mar 8, 2024

dkrebs commented Mar 8, 2024 • edited

ivercellino commented Mar 8, 2024

flechaig commented Mar 8, 2024

home-assistant bot commented Mar 9, 2024

isewise commented Mar 10, 2024

MasterCATZ commented Mar 15, 2024 • edited

isewise commented Mar 17, 2024

MasterCATZ commented Mar 28, 2024 • edited

isewise commented Mar 28, 2024

MasterCATZ commented Mar 29, 2024 • edited

flechaig commented Apr 4, 2024

MasterCATZ commented Apr 12, 2024 • edited

MasterCATZ commented Mar 8, 2024 •

edited

dkrebs commented Mar 8, 2024 •

edited

MasterCATZ commented Mar 15, 2024 •

edited

MasterCATZ commented Mar 28, 2024 •

edited

MasterCATZ commented Mar 29, 2024 •

edited

MasterCATZ commented Apr 12, 2024 •

edited