No connection to external MariaDB on NAS

Hi All,

I have HA OS running on a Raspberry 3b. To reduce the I/O on the SD card and thereby preventing to causing any damage to it (corrupt sd) I’m trying to setup an external database on my Synology NAS.

I already configer MariaDB on my NAS (no docker, i’m using the MariaDB package). From my Windows computer I can make a succesfull connection to MariaDB.
In my configuration.yaml have added the recorder intergration:

recorder:
  purge_keep_days: 5
  commit_interval: 1
  db_url: mysql://homeassistant:[email protected]:3307/homeassistant?charset=utf8

I have setup a database and user with the name ‘homeassistant’ and granded all permissions.

When I restart HA I get the following error in my logs:

* Error in database connectivity during commit: (sqlite3.OperationalError) no such table: events [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('timer_out_of_sync', '{"seconds": 1.016094272956252}', 'LOCAL', '2021-02-04 10:21:09.016257', '2021-02-04 10:21:09.016257', '299721b2978c77c3d1bfbd473db39dc8', None, None)] (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)
* Error in database connectivity during commit: (sqlite3.OperationalError) no such table: events [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{}', 'LOCAL', '2021-02-04 10:21:46.451759', '2021-02-04 10:21:46.451759', '92b50c659c61d0666f2d60c6d10990f7', None, None)] (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)
* Error in database connectivity during commit: (sqlite3.OperationalError) no such table: events [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{}', 'LOCAL', '2021-02-04 10:22:26.827260', '2021-02-04 10:22:26.827260', 'cfa64652b498db2d94e7be3445d857e3', None, None)] (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)
* Error in database connectivity during commit: (sqlite3.OperationalError) no such table: events [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{}', 'LOCAL', '2021-02-04 10:22:56.826650', '2021-02-04 10:22:56.826650', '42e8417804581bb68d070af402fd136c', None, None)] (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)
* Error in database connectivity during commit: (sqlite3.OperationalError) no such table: events [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{}', 'LOCAL', '2021-02-04 10:23:26.830919', '2021-02-04 10:23:26.830919', 'cc3cc436eb4b722fc971306dc30439ce', None, None)] (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)

When I check in phpMyAdmin, the database is empty.

I seems that HA is trying to connect to his down internal database despite I’ve configer the recorder intergration. I did not receive any errors related to permissions/connection to MariaDB. Can anybody help me how to set a connection to my external MariaDB?

Thank you!

MariaDBs default port is 3306 …

I already tried to switch to 3306 (Also TCP connection for MariaDB are enabled)

Its kinda wierd that i’m getting an sqlite3.OperationalError, because it should not use his own database anymore (?).

Check using sudo netstat -tulpen on which port and IP MariaDB is listening.

  • maybe TCP/IP socket is disabled completely
  • maybe TCP/IP socket is bound to 127.0.0.1
  • maybe port is something completely different.

Also: Did you restart HA after changing the record’s URL?

Yes, done that already: Netstat result:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      66         40167293   11906/mysqld

Found it!
Apparently a restart of HA is not enough. You’ll need to restart the host, so in my case SSH into HA and run the following command: ha host reboot