MariaDB and Access Denied

Installation Home Assistant OS
,
1

Home Assistant Core 0.112.3 on a RPi 4+

I installed the mariadb add-on and, as far as I can tell, it’s working fine with my HomeAssistant installation. If I navigate to LogBook in the HA web interface, I see the data that I’m collecting. So, that seems fine.However, I do keep getting errors in my log for mariadb.

My configuration is as follows:

databases:
  - homeassistant
logins:
  - username: ha_maria
    password: <redacted>
rights:
  - username: ha_maria
    database: homeassistant

I’m guessing the errors are from a different component which relies on the database. The IP address that the error is coming from is unknown to me. Perhaps that’s a different docker process?

Here’s the error:

2020-07-08 19:26:11 18 [Warning] Access denied for user 'homeassistant'@'172.30.32.1' (using password: YES)
2020-07-08 19:26:11 19 [Warning] Access denied for user 'homeassistant'@'172.30.32.1' (using password: YES)
2020-07-08 19:26:11 21 [Warning] Access denied for user 'homeassistant'@'172.30.32.1' (using password: YES)

Any help in resolving this would be appreciated!

1 Like
2

I’m having the same issue. Same IP address as well but user is ‘service’. Did you resolve this?

2020-07-08 19:26:11 21 [Warning] Access denied for user 'service'@'172.30.32.1' (using password: YES)
3

Hi, did you solve this?

4

Make sure you edited the connection example given to use in your config.yaml if you changed the default username and password

Default below

recorder:
  db_url: mysql://homeassistant:password@core-mariadb/homeassistant?charset=utf8mb4

homeassistant should be changed to ha_maria:YourPW or whatever you have set in the MariaDB configuration

Hope that helps!

5

I have been trying to resolve these problems off and on for a few weeks with no success.

Invalid config
The following integrations and platforms could not be set up:

recorder
logbook
history
energy
default_config

The logbook and history items appear to be a result of the recorder issue.

Logger: homeassistant.setup
Source: /usr/local/lib/python3.9/asyncio/events.py:80
First occurred: 8:08:18 AM (1 occurrences)
Last logged: 8:08:18 AM

Setup of recorder is taking over 10 seconds.

This is my mariadb config:

`databases:

  • homeassistant
    logins:
  • username: ha-maria
    password:
    rights:
  • username: homeassistant
    database: homeassistant`

This is the recorder setting from configuration.yaml

recorder: db_url: mysql://ha-maria:628500klr%40core-mariadb/homeassistant?charset=utf8mb4

The “%40” was substituted for the original “@” per a recent breaking change.

I have tried several different suggested changes from this forum with no improvement and would appreciate a push in the right direction.
Thanks for your input.

6

I’m not sure what breaking change you’re referring to but I still have @ in there and it works without issue.

I’m having a hard time reading your examples because you’re not formatting your yaml examples correctly (use triple backticks for multiline blocks of code) but if this is actually your config then it needs to be changed to this:

recorder:
    db_url: mysql://ha-maria:628500klr%40core-mariadb/homeassistant?charset=utf8mb4

With db_url on the next line and indented.

Also your mariadb config looks completely wrong but I have no idea since it’s being butchered by formatting. Can you please paste it in surrounded by triple backticks so we can see what it actually is?

7

My apologies about the code format. It does not look anything like what I pasted in, so here is another attempt.

This is my mariadb config:

databases:
  - homeassistant
logins:
  - username: ha_maria
    password: 628500klr
rights:
  - username: homeassistant
    database: homeassistant```

This is the recorder setting from configuration.yaml

recorder:
 `db_url: mysql://ha-maria:628500klr@core-mariadb/homeassistant?charset=utf8mb4`

I apparently got a wrong recommendation concerning @ vs %40
8

That is not what the breaking change required. It only requires the substitution if % is used in your username or password. You can leave the URL as is.

9

Did anyone get to the bottom of this?

I have a MariaDB container (mariadb) setup with the following environment variables:

MYSQL_PASSWORD=hadbpw
MYSQL_USER=homeassistant
MYSQL_DATABASE=homeassistant

I have this in my configuration.yaml:

db_url: mysql://homeassistant:hadbpw@mariadb/homeassistant?charset=utf8mb4

I get the same error despite dumbing down the password to a simple lowercase string to avoid any possible special characters issues.

The root password seems to work no issues:

mysql://root:MysqlR00t@mariadb/homeassistant?charset=utf8mb4

So is there some other configuration required for the local database user? In phpMyAdmin I can see the homeassistant user has all priveleges to the homeassistant database, except for ‘GRANT’.