Need help with MariaDB addon

I am trying to set up MariaDB on my RPi hass.io system. I have installed the MariaDB addon from the repo and entered the following in the config:

{
  "databases": [
    "homeassistant"
  ],
  "logins": [
    {
      "username": "hass",
      "host": "core-mariadb",
      "password": "myownpassword"
    }
  ],
  "rights": [
    {
      "username": "hass",
      "host": "core-mariadb",
      "database": "homeassistant",
      "grant": "ALL PRIVILEGES ON"
    }
  ]
}

The addon is started and the end of the log file reads:

[INFO] Start MariaDB
171115 19:06:17 mysqld_safe Logging to '/data/databases/core-mariadb.err'.
171115 19:06:17 mysqld_safe Starting mysqld daemon with databases from /data/databases
[INFO] Init custom database
[INFO] Create database homeassistant
[INFO] Init/Update users
[INFO] Create user hass
[INFO] Init/Update rights
[INFO] Alter rights for hass@core-mariadb - homeassistant

I have also added the following to my configuration.yaml:

recorder:
  db_url: mysql://hass:myownpassword@core-mariadb/homeassistant

This is the error I get in the home-assistant.log:

2017-11-15 19:14:24 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1130, "Host 'homeassistant' is not allowed to connect to this MariaDB server") (retrying in 3 seconds)
2017-11-15 19:14:27 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1130, "Host 'homeassistant' is not allowed to connect to this MariaDB server") (retrying in 3 seconds)

I have tried to follow the instructions here, but it looks as if hass.io and the MariaDB addon are supposed to do most of the job for me. Am I missing some steps in the process?

I struggled too. Here is what I ended up using that works.

{
“databases”: [
“homeassistant”
],
“logins”: [
{
“username”: “hass”,
“host”: “homeassistant”,
“password”: “my-password”
}
],
“rights”: [
{
“username”: “hass”,
“host”: “homeassistant”,
“database”: “homeassistant”,
“grant”: “ALL PRIVILEGES ON”
}
]
}

From my configuration.yaml file
db_url: mysql://hass:my-password@core-mariadb/homeassistant

Thanks, that seemed to work! No more errors. I am a bit curious though, where the database is stored. I guess the /config/home-assistant_v2.db i just the old one remaining?

Hi, I also have a bit of problem with MariaDB addon.
It does work, but I cannot open Logbook and History.
Anyone know what the problem might be?

This addon is working for me and can access via SQLAlchemy:

2 Likes

SOLVED:
I had the same problem and no matter what i tried I kept getting connection refused message in the logs.
To test what is wrong I attempted to connect from the outside haas from my laptop using MYSqlWorkbench app. I saw the same error as connection refused for “haas” user Then I changed the host name in both sections of the MariaDB host="%" , but still continued seeing the error.

Then I decided to create a NEW user “haas_admin” and all of a sudden I was able to connect from my laptop. I immediately went back to my configuration.yaml file and changed to the connection string to use this new uer. And everything started working like a charm.

I don’t know the reason for it, but maybe add-on is not properly making changes to a username that was previosly created with wrong configuration options. (though I am not sure about it)

Anyway, here is a working config (I added an additional user without removing a non-working haas user. ) I will try later to delete the original haas user from the config, but for now just posting what worked for me as is.

{
  "databases": [
    "homeassistant"
  ],
  "logins": [
    {
      "username": "hass",
      "host": "homeassistant",
      "password": "mypassword"
    },
    {
      "username": "haas_admin",
      "host": "%",
      "password": "my-password"
    }
  ],
  "rights": [
    {
      "username": "hass",
      "host": "homeassistant",
      "database": "homeassistant",
      "grant": "ALL PRIVILEGES ON"
    },
    {
      "username": "haas_admin",
      "host": "%",
      "database": "homeassistant",
      "grant": "ALL PRIVILEGES ON"
    }
  ]
}

And this is a connect string:

mdb_url: mysql://haas_admin:my-password@core-mariadb/homeassistant?charset=utf8

Hope creating a different user will resolve somebody’s problem.
Note: using “%” for host allow connections from inside home-assistant and from outside.

5 Likes

So, how do I find the database? How do i even verify that stuff is going into it? The addon says it is running and my old database is not growing.

I am looking for something that tells me it is work and the functional state.

In order to get it to work, I did have to add a second user and set the host to “%”

Hi, I have your same problem. You solved? Do you know how to find the homeassistant folder from a mysql manager?

For those who still struggle with the above instructions to get the MariaDB addon working in hass.io try using your hassio ip (or localhost) in the recorder db_url instead of the core-mariadb

ie.
db_url: mysql://haas_admin:[email protected]/homeassistant?charset=utf8

instead of:
db_url: mysql://haas_admin:my-password@core-mariadb/homeassistant?charset=utf8

2 Likes

Thank you very much. This helped solve my problem.

Ok,
But there is a way to migrate from sqlite3 to mariadb for managing the old history?

I suspect that the following,

‘mysql://homeassistant:password@’

Should be,

‘mysql://MYADMINACCOUNT:MYPASSWORD@’

Also, would restoring from a previous snapshot cause any issues? What was in the previous database be restored to the MariaDB? If not, is there a migration path?