Trying to use Maria DB as database

Configuration
1

I’m getting kind of frustrated, Home assistant is really not that user friendly.

I’m trying to connect the recorder to my MariaDB which is located on a Windows computer.
I keep getting. Host ‘HOMEASSISTANT’ is not allowed to connect to this MariaDB server.
I don’t know what to do.
I’ve made a user and granted privileges… my db_url looks like this:
MARIADB_URL: mysql://homeassistant:MYPASSWORD@MYSERVERIP/hassio?charset=utf8

What am I missing?

2

Hi,

could you tell us what you’ve got in the configuration of mariadb addon?
I’m not sure you should use “homeassistant” as username. try something else like “hass” or “ha_user”

3

Hi

This is where i’m getting confused by the documentation… as I read it the mariadb addon is only when you want to run Mariadb on your PI, where I want to run the Mariadb on an external device.
Or am I reading it wrong?

4

You can do both. The addon is a docker containerised version, you don’t want that.

Read the notes at the bottom of this page about URLs and alternative ports:

5

My bad. You said it, mariadb runs on a windows computer… :blush:

6

Which notes should I look for… I’ve tried with the ports and removing pymysql, but nothing…

7

Yes those notes. This URL;

mysql://user:password@SERVER_IP/DB_NAME?charset=utf8

Also:

For MariaDB you may have to install a few dependencies. If you’re using MariaDB version 10.2, libmariadbclient-dev was renamed to libmariadb-dev . If you’re using MariaDB 10.3, the package libmariadb-dev-compat must also be installed. For MariaDB v10.0.34 only libmariadb-dev-compat is needed. Please install the correct packages based on your MariaDB version.

1 Like
8

I assume this is not HA’s fault.
Are you able to connect to the mariadb from any other client, or only from localhost (the Windows computer where mariadb is installed)?

9

And if you try a mariadb on Windows, did you configure Windows firewall to allow access to the mariadb ports ?

10

Have you also removed or changed the loopback IP address binding of your MariaDb? Otherwise your MariaDB will never allow access from external hosts, no matter what username they use.

Ah, I overlooked @VDRainer post. Sry :wink:

11

By not being user friendly, I mean that the documentation is lacking.

I dropped the idea of using a MariaDB in my windows and am now looking into running home assistant and mariadb on Docker.
And I have gotten this to work with Home Assistant being able to talk to maria db.
But I am now facing other problems.

12

The documentation can be updated by anyone to help out everyone. If you found something lacking or not right, feel free to submit a PR to update the documentation. Click the link at the top on the page that needs updating that says “Edit this page on GitHub”. Complaining about the lack of documentation but saying you figured it out and not updating said documentation doesn’t help anyone.

1 Like
13

OK, I didn’t realize that.
I will remember to try and update documentation in the future, though I didn’t figure it out with the MariaDB… so this time I won’t update anything… but next time.

14

I have just made good progress.

This has been my journey - just to help those who come after me…

I first used the ‘add-on’ which worked and I noticed a speed increase (without realising it) for accessing the History.
I then used a Synology image.
I moved from the add on to the image as I couldn’t figure out how to access the DB from outside of my HASSIO install.

Here are some of the settings and important steps for me (I think)

As a minimum you need the DB, and a recorder reference to point to it (if it isn’t setup right you will get errors on the reboot in the notifications and you will not see the history tab.

Steps (that worked):
ON SYNOLOGY
Install Mariadb on Synology

  • enable the TCP option and record the socks address (required below starts /run/ )
  • user and password will allow you to access the DB structure NOT for HASS to use.
    An easy way to ‘administer the DB’ is to then install PHPmyadmin.
  • log in with root and the password you added
  • add a User (eg.HAUS) and all the privileges you think required and create a DB to the same name (sure it doesn’t need to be the same)

ON HASS
update Config .yaml
recorder:
db_url: mysql://HAUS:[email protected]:3307/HAUS?unix_socket=/run/mysqld/mysqld10.sock&charset=utf8mb4
the /run/ bit was the address I said see on the Synology maria DB UI.

restart and this config worked for me.

I was then able to see the tables in the SQL editor bit of phpmyadmin thing

Hope this garbled approach helps others!!!

1 Like
15

Hi,
Thank you for what you shared.

I’m trying to host the database outside of the raspberry pi and have an old synology ds115j which can install mariadb. I tried following your instructions but in the end I get HA complaining about the connection:

Error during connection setup to mysql://hadb:password@IPADDRESS:3307/hadb?unix_socket=/run/mysqld/mysqld10.sock&charset=utf8mb4: (MySQLdb._exceptions.OperationalError) (1045, “Access denied for user ‘hadb’@‘homeassistant’ (using password: YES)”)
(Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)

How should I try to fix this?
Is this in HA, Synology or MariaDB?

16

For me ( I am by no means an expert) this looks like its a problem with MariaDB user account

It seems like its at least talking and recognising the home assistant as a host.

Did you log in from Synology to phpmyadmin - is the user setup in there for hadb?
Does it have the privileges you need to give it?
Assume you have swapped the real password for the word… password
Does it allow for all hosts my one says %

Good luck.