Connection Issue to SQL database

[Teberon]

I am trying to figure out why Home Assistant is unable to connect to the MariaDB server.

When starting HA I have the following within the logs

2023-12-31 12:22:52.671 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (MySQLdb.OperationalError) (2002, "Can't connect to server on '192.168.10.30' (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.11/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3292, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1269, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 169, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 167, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
self.__connect()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
with util.safe_reraise():
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 637, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 616, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/MySQLdb/__init__.py", line 121, in Connect
return Connection(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/MySQLdb/connections.py", line 193, in __init__
super().__init__(*args, **kwargs2)
MySQLdb.OperationalError: (2002, "Can't connect to server on '192.168.10.30' (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 932, in _setup_recorder
self._setup_connection()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1406, in _setup_connection
Base.metadata.create_all(self.engine)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 5828, in create_all
bind._run_ddl_visitor(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3242, in _run_ddl_visitor
with self.begin() as conn:
File "/usr/local/lib/python3.11/contextlib.py", line 137, in __enter__
return next(self.gen)
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3232, in begin
with self.connect() as conn:
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3268, in connect
return self._connection_cls(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
Connection._handle_dbapi_exception_noconnection(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2430, in _handle_dbapi_exception_noconnection
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3292, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1269, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 169, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 167, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
self.__connect()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
with util.safe_reraise():
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 637, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 616, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/MySQLdb/__init__.py", line 121, in Connect
return Connection(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/MySQLdb/connections.py", line 193, in __init__
super().__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2002, "Can't connect to server on '192.168.10.30' (115)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)

This is the entry in the config.yaml rto connect to the database (removed username and password)

recorder:
  db_url: mysql://hassadmin:<password>@192.168.10.30:3306/homeassistant?charset=utf8mb4

The firewall is open on port 3306 as below

I have no issues connecting from my computer in the same VLAN to connect to MariaDB with the credentials I use in the config.yaml

The rights for the hassadmin on the db should also be correct

Unfortunately with the HA OS I can’t install further tools for troubleshooting…

Any assistance is highly appreciated.

[boheme61]

Maybe this ( Btw, you do know there is a search function in here ? )

No history available: MySQLdb.OperationalError) (2002, “Can’t connect to MySQL server on ‘core-mariadb’ (115)

This helped me: In the file /etc/mysql/mariadb.conf.d/50-server.cnf (Raspi-os 2021-03-04 with MariaDB installed), you should replace the line "bind-address = 127.0.0.1" (localhost) by "bind-address = 0.0.0.0" (all). After, you should restart your MySQL server : $ sudo service mariadb restart

ERROR 2002 (HY000): Can't connect to MySQL server on '192.168.1.15' (115)

Error Messages — SQLAlchemy 2.0 Documentation) ( url from your log )

[Teberon]

I have had a look at that. If that would be the solution, I wouldn’t be able to connect to the DB using my computer as this would also be blocked.

[Teberon]

The “bind-address = 127.0.0.1” was commented out in the 50-server using #
uncommented it and set to 0.0.0.0 and it worked. I am still unsure why my computer had no issues accessing the DB when bind-address was not set while HA wasn’t able.

[boheme61]

@Teberon

while HA wasn’t able

about 3 years since my first HA, still don’t know how it “communicates” internally nor externally