MariaDB & HA on Docker via docker compose

Configuration
#1

Hi there,

I’m trying to get a MariaDB SQL database running next to docker where I can record my events into.

At the moment I’m using the following docker compose for the database:

Here is the docker compose:
The *** passwords here are for test purposes the same :wink:

  mariadb:
    container_name: mariadb
    image: yobasystems/alpine-mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: ***
      MYSQL_DATABASE: ha_database
      MYSQL_USER: homeassistant
      MYSQL_PASSWORD: ***
      PUID: 1000
      PGID: 1000
    volumes:
      - /opt/mariadb:/etc/mysql/conf.d
      - /opt/mariadb:/var/lib/mysql
    ports:
      - 3306:3306

The config is as follows:

recorder:
  purge_keep_days: 30
  auto_purge: true
  db_url: mysql://homeassistant:***@192.168.2.61:3306/ha_database?charset=utf8

However when restarting I get this error:

2022-05-23 23:52:41 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (1044, "Access denied for user 'homeassistant'@'%' to database 'ha_database'")

I’ve already tried a lot of different test and combinations without any success.
Can anyone point me in the right direction?
I guess this has something to do with the docker container with mariadb, which doesn’t provide the correct rights to make this connection work.

Thanks for the help!
Pieter

#2

Is mariadb running? you define a database but is that db created?

#3

Hi, yes I’m quite sure the db is created.

image
image1534×949 55.3 KB

With HeidiSQL I can connect to the database using the ‘homeassistant’ user.
Please let me know what or how I can test this further .

Thanks!

#4

Your screenshot is showing you using ha_db as database but your connection string is using ha_database.

#5

Yes, true… But I have been trying everything :stuck_out_tongue: including changing credentials etc.
At the moment I managed to get it to work using mysql://root:[email protected]

Still any advice on how to configure this sql docker container?
As this seems to give the issue.
Somehow the user in the docker compose doesn’t get the right privilliges right away.
This would be desirable to get this right from the start of the config,… right?

Just as an update, I changed my compose file now to:

    environment:
      MYSQL_ROOT_PASSWORD: ***
      MYSQL_DATABASE: ha_db
      MYSQL_USER: ha_user
      MYSQL_PASSWORD: ***
      PUID: 1000
      PGID: 1000

So my question: can I change this compose file still to be able to use the ha_user right away? Without going in and changing privilliges etc…

Thanks for your input!
Pieter

#6

One thing from my setup
My main HA is running container under ‘host’ and this then works fine with the 192… to mariadb
However, my subprod instance is running under bridge and in that case I actually need to supply the ip of the maria-db-docker container (with me this is alike 172.17… For some reason I could not get it to work otherwise.

#7

Changing the compose file will create a new container so I would expect that a new user (ha_user) will be created. Try from HeidiSQL to make sure it actually works.

#8

Indeed I’ve tried with the 172.0.0.12 local internal ip for the container, but without any luck

#9

You’re right that’s why I want it to work straight from the compose file without any other setup.
After relaunching the container this should keep working.

So everything works, in HeidiSQL I can see the changes in HA coming in.
Only using the MYSQL_USER, doesn’t work yet.

Thanks

#10

And another difference… my db_url does not (!) have the port…just the ip

#11

Yes, however this mysql:// url works, only thing here is that it works with root, and not with the secondary user created in the docker compose.

recorder:
  purge_keep_days: 30
  auto_purge: true
  db_url: mysql://root:***@192.168.2.61:3306/ha_database?charset=utf8

  db_url: mysql://root:***@192.168.2.61/ha_database?charset=utf8

Works as well, but doesn’t resolve the user issue.
Thanks

#12

What access to the db did you give the homeassistant user?
This is what I have via phpMyadmin

image
image713×143 15.5 KB

#13

I still don’t understand: you’re saying here the name of the database is ha_db, but here you’re using ha_database?

#14

Sorry for the confusion, while testing I started working with a shorter db name and user name. This was the last docker compose I used:

  mariadb:
    container_name: mariadb
    image: yobasystems/alpine-mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: ***
      MYSQL_DATABASE: ha_db
      MYSQL_USER: ha_user
      MYSQL_PASSWORD: ***
      PUID: 1000
      PGID: 1000
    volumes:
      - /opt/mariadb:/etc/mysql/conf.d
      - /opt/mariadb:/var/lib/mysql
    ports:
      - 3306:3306

About the phpAdmin I’m not completely sure. Is this installed along the docker compose? Or do you need to install this seperately? Is it essential?
However I can see this data coming in:

image
image1354×681 96.2 KB

#15

Is similar as HeidiSQL…I use it for managing mysql. What is important to find out is if the userid that you use has sufficient rights to write/update tables
The fact that you have data coming in, is interesting as this collides with the error…or are these by ‘root’?

#16

So it’s working :smiley:

#17

The data started coming in when I started using the url with root:

db_url: mysql://root:***@192.168.2.61/ha_database?charset=utf8

And indeed it’s working, was just curious about how to properly get this user fixed.
I’ll try to spin up a phpAdmin to have a webservice to monitor the db side.

Thanks for the help!

#18

Again though, you’re saying this is in the last docker compose file you used:

MYSQL_DATABASE: ha_db

So why are you using ha_database in your connection string? It should be ha_db like in the compose file, otherwise the user is granted permissions for a database that you don’t use from HA.