Issues with MariaDB-Auth on docker

Configuration
1

Hi All

I’ve been spending quite some time on trying to use MariaDB as database.
I have not yet succeeded in making it work.
Could anyone help me in pointing to the errors?

I have homeassistant (not the hass io) running on my Synology in Docker.
I installed mysql CLI on my own pc to be able to connect/debug/…

I created a folder structure:

  • /docker/mariadb/conf
  • /docker/mariadb/conf/my.cnf (I mainly changed the binding to 0.0.0.0)
  • /docker/mariadb/data (empty)
  • /docker/mariadb/log (empty)
  • /docker/mariadb/docker-compose.yml

I started the container using: sudo docker-compose up -d

Docker compose creates a new network, but I’ve not yet investigated how networks work. I did discover you can’t put MariaDB in bridge network (all my other containers are in bridge or host).

In the docker application on my synology dsm page I went to the terminal and executed

mysql -u root -p
CREATE DATABASE homeassistant;
CREATE USER 'homeassistant' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON homeassistant.* TO 'homeassistant'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
select user,host from mysql.user;
show grants for homeassistant;
exit;

(the @‘%’ is necessary to allow connections from any IP; as my homeassistant is on another network as the mariadb).
the last two commands are just to confirm what has been created.

I can now go to my own pc and connect to the mariaDB instance (with the root user and the homeassistant user).
mysql -u homeassistant -p -h 192.168.1.1
I did not manage to import my old database (gives me an error that the operator < is reserved for future use).

In my configuration.yaml I added
db_url: mysql://homeassistant:'123456'@192.168.1.1/homeassistant?charset=utf8
I restarted homeassistant from the webgui
and I see

Error during connection setup: (MySQLdb._exceptions.OperationalError) (1045, “Access denied for user ‘homeassistant’@‘192.168.0.5’ (using password: YES)”) (Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation) (retrying in 3 seconds)
in my docker app in synology, in the log tab of the mariadb container, I also see a line saying the same thing.
I have the same issue using the root password.

I assume it’s not network related: otherwise I wouldn’t see the log message on the mariadb container.
I don’t assume it’s authentication related, otherwise I wouldn’t be able to connect from my pc
I DO assume it has something to do with the way homeassistant

What could be the issue here?

(i changed IP’s and passwords for privacy’s sake I might have mistyped them in the pastebins but they should be the same everywhere)

2

If HA and Maria are not using same network, you would need HA to connect using the host server IP and Maria DB exposed port. Else you cannot connect using docker networking

Generally I provide db a host name in docker and add ha to the created network and use his name as up address

3

I’m running Home Assistant container, but I have MariaDB running on the bare metal of the host Ubuntu OS. Maria was already running on the machine for something else and I didn’t want a duplicate of it running in docker. This is my config, I don’t have any quotes around my password, and I’m using the local host loopback address of 127.0.0.1:

mysql_login: mysql://homeassistant:[email protected]:3306/homeassistant?unix_socket=/var/run/mysqld/mysqld.sock&charset=utf8mb4

And for some reason, mine was on port 3306 . I tried a bunch of other ways to get it to work and this was the best for mine - yours might be different of course.

1 Like
4

Remove the quotes around your password.
It understands your password literately with quotes, which it doesn’t have.

5

that does seem to have been the issue. I broke my head on that for so long!

6

Thanks got it working for me after updating the mariadb 11.2.2

1 Like