My db is in use:
Threads: 4 Questions: 54828 Slow queries: 0 Opens: 54 Flush tables: 1 Open tables: 44 Queries per second avg: 5.040
But when I do: pi@homepi:~ $ cd /var/lib/mysql
I get this: -bash: cd: /var/lib/mysql: Permission denied
I did try to make it work with mariadb in my nas but it didn’t work. I’m out of ideas. Do you remember the steps that you followed?
I followed the Arch Linux wiki entry for MySQL here (the Arch wiki entry for MariaDB redirects to the MySQL entry). My HA instance runs on a Pi2 under the “Pi” user so I followed the Arch Wiki for database installation, created a “Pi” user for the database, created the database for HA, and then pointed HA to the new database and it seems to be working smoothly.
I finally decided to do all from scratch, reinstalled raspbian and HA, factory reset and reinstalled MariaDB on NAS and everything work fine at once following exactly the same steps as I described in a previous post based on the quides/recommendations from @sjorsjes @rotor.
In the AIO installer I did the following steps:
sudo apt-get install libmysqlclient-dev
su -s /bin/bash hass
source /srv/hass/hass_venv/bin/activate
pip3 install --upgrade mysqlclient
and then I just made the configuration of the recorder for the database instance on my NAS:
db_url: mysql://hassuser:YOURPASSWORDHERE@IP:Port/hass_db
1 Like
Hi all!
I have a problem with HA and MySQL.
Successfully connected HA and MySQL, but HA writes values in database only when starting and I have some errors about MySQLdb in log, last of them is:
UnicodeEncodeError: 'latin-1' codec can't encode characters in position 0-6: ordinal not in range(256)
And after that HA don’t write values at all, until next restart.
I think I’m getting this because I have four template sensors which states are having unicode symbols and in MySQL HA writes that states like “unknown”.
I tried to create database with UTF-8 collation but that didn’t help.
Is there a way to tell HA not to write this values in database at all? I have excluded these sensors from history and logbook, despite that HA writes just everything.
UPDATE:
Found the solution:
- database should be in utf8 collation;
- in
recordersection,db_urlparameter should look like
mysql://user:pass@hostname/db_name?charset=utf8
13 Likes
I had the exact same problem as many people; correct setup but No data flowing into the database. I Made the same change as @omeasire and now everything works.
Moving from sqlite to mysql makes a HUGE change in speed on my pi B for graphing and history!
Thanks Rotor! This also solved my issue!
@sjorsjes tanks for sharing this. I think it would be great if you could add it to the official docs too.
Thank you for the
mysql://user:pass@hostname/db_name?charset=utf8
Did th magic. Can you put this into HA documentation as other wise it woul be hard to find!
1 Like
I don’t have a Github account, and don’t know how to do this at all 
You don’t need Github; you can simply include it in your configuration.yaml
It was the answer to the previous post 
I already have a working mysql with hass
The only problem - still getting locked out. Too many
Error during connection setup: (_mysql_exceptions.OperationalError) (1129, “Host ‘xxxyyyzzz’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’”)
Can’t see any errors on the server side
Have purge set to 7 days and having MySQL database as a backend. Still very unhappy with the perfomance. Database gets 1,5M records in 7 days. History or logbook takes forever to render.
So one point is excluding as much as i can from history but at the same time - will switching to Postgre render any benefits?
I had no problems getting this to work with MariaDB running on a different host (Synology NAS). I did have to install the mysqlclient. Before I did that HASS would refuse to start with errors that looked like:
17-01-05 21:02:24 aiohttp.server: Error handling request
Traceback (most recent call last):
File "/home/homeassistant/.homeassistant/deps/aiohttp/web_server.py", line 61, in handle_request
resp = yield from self._handler(request)
File "/home/homeassistant/.homeassistant/deps/aiohttp/web.py", line 249, in _handle
resp = yield from handler(request)
File "/usr/lib/python3.4/asyncio/coroutines.py", line 143, in coro
res = yield from res
File "/usr/lib/python3.4/asyncio/coroutines.py", line 143, in coro
res = yield from res
File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/http/__init__.py", line 427, in handle
result = yield from result
File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/history.py", line 240, in get
self.filters)
File "/usr/lib/python3.4/asyncio/futures.py", line 388, in __iter__
yield self # This tells Task to wait for completion.
File "/usr/lib/python3.4/asyncio/tasks.py", line 286, in _wakeup
value = future.result()
File "/usr/lib/python3.4/asyncio/futures.py", line 269, in result
raise CancelledError
concurrent.futures._base.CancelledError`
etc. but after installing mysqlclient it works fine and the HASS interface is quite a bit faster and more responsive. Made a very noticeable difference!
One issue I did notice with configuring SQL on my NAS was that the default user permissions prevent remote host logins! I used phpmyadmin and got this as a warning in the interface. I had to drop two user permissions that allowed any login/any host/any user from localhost. HA would not connect to the MariaDB server until I did!
If you are trying to connect to SQL on your NAS, check the default logins in your database server!
3 Likes
Okay, I was attempting to do this tonight and I guess I mucked up something.
I got to the part:
CREATE USER 'hassuser'@'localhost' IDENTIFIED BY 'XXX';
I assume the password is the root password I set up when mysql installed? Then I forgot the stupid semi-colon and seemingly could not get out of there but somehow managed to do so. When I went back into mysql and tried to run the command again, I got an error:
ERROR 1396 (HY000): Operation CREATE USER failed for 'hassuser'@'localhost'
Though I could run:
SELECT User FROM mysql.user;
+------------------+
| User |
+------------------+
| root |
| root |
| root |
| debian-sys-maint |
| hassuser |
| root |
+------------------+
6 rows in set (0.00 sec)
and it appears the hassuser is there. So I moved on just in case. Then trying to grant the privileges:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'hassuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
And then when I tried to flush the privileges, even though it did not appear that any were granted in the previous command:
I forgot the blasted semi-colon again and had to escape out and went back in
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
I was not confident it went as it should but thought I will just finish out the venv part, then ran into this:
sudo su -s /bin/bash homeassistant
No passwd entry for user 'homeassistant'
So how bad did I muck things up? And whats with the errors?
This is actually achiles heel of home assistant. Depending on the type of installation you end up with either hass or homeassistant as the HA user.
In your case i think it’s hass. Also check the dir structure as that is also different.
And to ease up on DB management check out webmin.
And just for the report. Switching to Postgre SQL didn’t do the trick. Still have to wait up to several minutes for the history window to render. Overally feels the same as MySQL backend
Thanks for that tip @moskovskiy82, that was exactly it. My virtual environment was hass, not home assistant and the source directory was different. I probably should have followed the instructions by @dimmanramone as his have the correct paths.
So that basically just leaves me with the issues I had in setting up the MySQL section. If anyone has any input on that and anything I can check to make sure all is good there, I would appreciate it.
I guess I still have some issues left to sort out:
17-01-06 00:17:37 homeassistant.components.recorder: Error during connection setup: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'hassuser'@'localhost' (using password: YES)")
17-01-06 00:17:47 homeassistant.components.recorder: Error during connection setup: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'hassuser'@'localhost' (using password: YES)")
17-01-06 00:17:57 homeassistant.components.recorder: Error during connection setup: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'hassuser'@'localhost' (using password: YES)")
recorder:
purge_days: 30
db_url: mysql://hassuser:[email protected]/hass_db
This is what I get when trying to grant privileges:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'hassuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
I think I would think something is broken in HA if I tried something and it just worked without any issues.
EDIT:
I went back in and deleted the hassuser and verified it was no longer listed. Then I re-added hassuser as instructed above and granted all privileges, got the same response as above and still getting the same error in the log as listed above.
I also tried changing the configuration.yaml:
recorder:
purge_days: 30
db_url: mysql://hassuser:mysecretpassword@localhost/hass_db
Still getting the same thing.
I guess at this point I will have to wait and see if anyone has some input because I am not sure where things went wrong at now. You don’t have to restart MySQL after such actions, do you?