Mariadb Sensor Can't Connext

Configuration
1

I just switched over from the default sqllite to Maria db and everything seems to be working fine. I don’t have any errors and I can see history for each of the devices starting from when I made the switch.

I’m trying to create a sensor to monitor the database size and I can’t get it to connect to the database. This is the error I’m getting.

Log Details (ERROR)

Fri Aug 02 2019 17:13:04 GMT-0500 (Central Daylight Time)

Couldn’t connect using mysql://hass:[email protected]:3306/homeassistant?charset=utf8 DB_URL: (MySQLdb._exceptions.OperationalError) (1130, “Host ‘hassio.localdomain’ is not allowed to connect to this MariaDB server”) (Background on this error at: http://sqlalche.me/e/e3q8)

Here’s my sensor config

  • platform: sql
    db_url: mysql://hass:[email protected]:3306/homeassistant?charset=utf8
    queries:
    • name: HASS DB size
      query: ‘SELECT table_schema “database”, Round(Sum(data_length + index_length) / 1048576, 2) “value” FROM information_schema.tables WHERE table_schema=“homeassistant” GROUP BY table_schema;’
      column: ‘value’
      unit_of_measurement: MB

I’ve tried several different variations of the db_url based on my searches and the documentation but I still can’t get it to connect. This is the latest attempt. Any ideas what I might be doing wrong?

2

How did you setup the MariaDB?
If the db_url is the same as for the recorder config it should work.
Maybe google for ‘mysql allow host’ to grant acces from all hosts.

BTW: It’s better to use secrets.yaml for the db_url.

secrets.yaml

db_url: mysql://user:[email protected]/homeassistant?charset=utf8

and reference to it in the config.

recorder:
  db_url: !secret db_url
  ...

sensor:
  - platform: sql
    db_url: !secret db_url
    ...
3

I got it working. I did exactly that. I googled the error message and it had to do with permissions and using the ip address. Someone suggested creating a new user but I couldn’t figure out how to connect to the mariadb. I’m using the hass mariadb add-on. So I replaced the ip address in the db_url with localhost and got a different message. Something about cannot connect to the socket. Googled that message and someone suggested to use 127.0.0.1 instead of localhost because it connects using tcp instead of a socket. And finally, that worked. Aparrently each method connects differently. Also, the official SQL sensor documentation says to use user:pass@locahost but that didnt work for me.

In case someone else finds this thread, this is what worked for me. Your Maria DB config defines the username and database that the senor will use to connect. In mine, homeassistant is the database and hass is the user. This is also different from the SQL sensor page. The Maria DB add-on defaults to homeassistant as the database name. But the SQL sensor page tells you to use hass as the database. Another problem I had originally.

And yes, I should be using the secrets.yaml. I’ll probably move all my passwords there soon.

My Maria DB config:

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

My working sensor config:

- platform: sql
  db_url: mysql://hass:[email protected]/homeassistant?charset=utf8
  queries:
    - name: HASS DB size
      query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1048576, 2) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
      column: 'value'
      unit_of_measurement: MB
2 Likes
4

Not 100% sure, but i think "host": "%" should allow any host in the local network.

  "rights": [
    {
      "username": "hass",
      "host": "%",
      "database": "homeassistant",
      "grant": "ALL PRIVILEGES ON"
    }
  ]
1 Like
5

Did you get this working?

Also trying to get Maria db Size sensor working.

6

Yes I did. Look a couple of comments back. I have an explanation along with my config.