Using a external MySQL server with Hass.io for Recorder

Home Assistant OS
#1

I have been trying to get this working on and off for a couple of months now. I can never get the recorder component to work on an MySQL DB hosted on another machine on my network.

Is this a limitation of Hass.io? Is there a work around?

For example, here is my db_url: mysql://hass:[email protected]/homeassistant?charset=utf8

1 Like
#2

I think this should work.
What is the problem? Any log outputs?

#3

Here is the error message I am getting. Even though I am specifying the IP it looks like it is defaulting to localhost?

Error during connection setup: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'hass'@'hassio.localdomain' (using password: YES)") (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
1 Like
#4

This means that the user ‘hass’@‘hassio.localdomain’ (which is the user you entered in the db_url) has no access to the mysql server.
You have to look on your mysql server to give the user access and create the database ‘homeassistant’

#5

Yep, thanks! you would think that having looked at this on and off for a while I would have noticed that I had a setting setup incorrectly on the DB side. But no. Thanks for the quick response!

#6

Here is a monster topic about using mysql database.

#7

Thanks! Yeah I have used that thread before to originally set up a external DB before i migrated to Hassio, that is what was so perplexing to me. I knew this worked for me before so I guess i jumped to the conclusion that is was a hassio issue


Thanks again!

#8

Make sure the user has enough previliges to write in the database and that you are sending the user and pass on the connection URL.

#9

VDRainer j.assuncao lightbulb0413
How did you resolve this problem?
I have error message:
(1005, 'Can\'t create table hass_db.events (errno: 13 "Permission denied")')
whch suggest that user don’t have permission to db but I can log in by HeidiSQL to this db with the same login and passowrd. How to add proper permission to this user?

edit: OK. I had problem with privileges on /var/lib/mysql/ha_db
Just chown mysql:mysql fixed it. Sorry for the problem.

#10

I’m using hassIO and have an MariaDB database on another machine for recorder, it works for me.

#11

How did you setup your user? Did you set it for a specific IP address or for %?

#12

Hi,

i’ve got an similar problem. I can’t connect to my sql database on a different pi.
I created a new database called ha and created also an user with permissions for the specific database.
mit config:

recorder:
  db_url: mysql://*****:****@192.168.178.91:3306/ha?charset=utf8

I get this log entry:

Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on '192.168.178.91' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)

The IP-Adress is correct and the port as well.
i tried to google the error, but found no results. Maybe someone can help?
Im running hass.io.

#13

Hi,

I have Hassio on raspberry Pi and I also wanted to use an external database to record hassio’s details.
I have QNAP NAS in my local LAN and I run MySQL server where I created database named “homeassistant” and created USER named “user” with all privileges to the “homeassistant” database.
Creating mySQL’s user you have to define ip_address of your hassio (ie. 192.168.1.37) and password.
Then configuration.yaml looks like following:

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

I do not use port number in my configuration.

#14

Hi,

I solved it.

I use Raspbian on my Pi with mySQL running.
The Problem was, that the mysql database only accepted data from localhost. I had to change the bind_adress in the config files. If you Google that, you will find the steps to configure mysql in that way, that it as well accept data from other hosts than localhost.

2 Likes
#15

I had an issue about Hass complaining about not being able to set up the connection with this character set (‘utf’). Turns out the documentation was flawed again as it should read ‘utf8’. Now it works.

Why do developers insist on releasing half-baked documentation and wasting millions of hours of user’s time?

2 Likes
#16

You can always change the documentation yourself if you think it is inadequate.

1 Like
#17

Can you please link to that documentation?
I don’t see any problems in the recorder docs.

#18

You can always change the documentation yourself if you think it is inadequate.

Problem with that philosophy is that it requires you know how it works in the first place.

1 Like
#19

This is the answer. /etc/mysql/my.cnf (assuming this is how you’re configured)

Add “bind-address=YOUR-SERVER-IP”
and comment out the skip-networking line.

#20

I’ve had this working ok for a while now, but now suddenly it doesn’t:

This is the log details on the error I get in HA:

> Error during connection setup to mysql://homeassistant:[email protected]/hass?charset=utf8: (MySQLdb._exceptions.OperationalError) (1115, "Unknown character set: 'utf8mb4'") [SQL: CREATE TABLE statistics_meta ( id INTEGER NOT NULL AUTO_INCREMENT, statistic_id VARCHAR(255), source VARCHAR(32), unit_of_measurement VARCHAR(255), has_mean BOOL, has_sum BOOL, PRIMARY KEY (id) )DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ] (Background on this error at: https://sqlalche.me/e/14/e3q8) (retrying in 3 seconds)

This is the config in HA:

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

The mySQL server, version 5.1.36, is located on an old QNAP NAS.

All the equipement was powered down reciently, for the first time in 2 years. Could be related to this, but I cannot figure out why.

Any thoughts where to look? Thanks.