In my case, the credentials I used in the URL in configuration.yaml was the user and pass for my db. I have one user with full rights to the db and that was what I used.
Is there someone that has a working mySQL setup?
Mentioned in this post above at the end:
I did give it a try again, with a new user and a new db with no luck. I did try with the root user too but it didn’t work.
I’m disappointed that no one else seems to have it working. If we could just get someone with a working setup to help, we’d know whether we missed something or whether this deserved an issue at github.
I’d hate to post an issue only to find out I missed something obvious.
EDIT: And now that my new SQLlite db is up to 250mb, things are starting to slow down again…
Hey Rob
Mind posting your configuration.yaml settings for MySQL? As mentioned above I have ,mine setup with Docker, so might be able to help.
1 Like
Mind? I appreciate the help!
I have it commented out at the moment but it looks like this:
recorder:
# Delete events and states older than 1 week
purge_days: 7
#db_url: 'mysql://hassuser:[email protected]/hass'
MySQL is running on the same Pi as HA. I created a user and password for the db (named ‘hass’) and gave it full rights to the db. I also tried using the actual IP instead of the loopback but there was no difference.
Excellent. This might get a bit long, so strap yourself in. I’ve never used a Pi, but these commands should be generic for any system.
Let’s first staff off checking that we can access MySQL. From a SSH to the Pi, let’s access MySQL using your username and password.
mysql -u hassuser -p
If everything is setup okay, you should be prompted for your password. If you get a denied message, then this is your problem.
MySQL uses a username, and also the hostname of the server as authentication. So, in the following example, all three are considered different users logging into MySQL, and need to have their own entry in the MySQL connection settings.
hassuser@localhost
[email protected]
[email protected]
Once logged in, we’ll need to switch over to our Home Assistant database. So, let’s do
use hass
Where hass is the name of your database. If you get an error here, then your user does not have permission to use that database. You’ll need to add a user, being sure to watch out for the host you’re using and assign them full permissions to the database.
Once you’re in the database, let’s do a test to ensure we can create a table. Let’s create a test table to see if we run into any issues
CREATE TABLE example(
id INT,
data VARCHAR(100)
);
If that works, then we have all the permissions we need.
Finally, I’m using docker, and that installs the following package for me. Might be a good time to re-run this just to ensure the dependency is installed
pip3 install mysqlclient
Let me know how you go. Hopefully something here trips you up which you can find where the problem is.
3 Likes
I had already done everything up to creating the table when I tested to see if the db had been written to, so I logged in and submitted the query. The query ran fine with no errors. Even though I was sure I installed the sqlclient, I went ahead and typed in the install command just to verify, and it showed that the requirements were already satisfied, verifying that I had indeed installed it.
So it appears that the user/password is correct, the db exists and the user has the correct rights to it and mysqlclient is correctly installed.
I found another thread on this and I have asked the poster for some advice here:
I’m hoping this might be the answer, but I need to know what user to install it as and whether it needs to be in the venv or just installed as my Pi user.
This part of your reply has me a bit confused though:
MySQL uses a username, and also the hostname of the server as authentication. So, in the following example, all three are considered different users logging into MySQL, and need to have their own entry in the MySQL connection settings.
hassuser@localhost
[email protected]
[email protected]
How do you know which one to use in the db_url setting in HA? I suppose I could try all three but I’d like to avoid a bunch of restarts if I could. 
It all depends on the URL you’re using to call MySQL.
In your config example, you’re using 127.0.0.1, so the user you want to make sure added is
[email protected]
If that doesn’t exist, then you’ll get permission errors, even if hassuser@localhost exists.
You can see the users who are setup on your MySQL, by running
SELECT User FROM mysql.user;
After you’ve logged into the MySQL server
So I logged in to MySQL as the hassuser and tried to execute the query
SELECT User FROM mysql.user;
and got an error that hassuser@localhost didn’t have the rights, so I figured that maybe hassuser@localhost was the user that I should be accessing as.
I went into my config and changed it and then restarted HA. Same thing as before; when I click on a sensor it just sits there.
Logging to mysql in as the root user I show the following:
mysql> SELECT User FROM mysql.user;
+------------------+
| User |
+------------------+
| root |
| root |
| debian-sys-maint |
| hassuser |
| root |
| root |
+------------------+
6 rows in set (0.02 sec)
For good measure I also tried changing it to the IP of the Pi. Again, same results. Clicking on a sensor shows no graph and a ‘processing’ animation until I close the card. So I’ve tried each of the URLs; localhost, 127.0.0.1 and 192.168.1.181 (the Pi’s address) with the same results.
Can you do a SELECT * FROM mysql.user;?
Just want to see what all the values in that table are for your users.
1 Like
+-----------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | *BD9789AED9E77554D72BA86E93A7DEC64ECC4BFD | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| raspi1 | root | *BD9789AED9E77554D72BA86E93A7DEC64ECC4BFD | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| 127.0.0.1 | root | *BD9789AED9E77554D72BA86E93A7DEC64ECC4BFD | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| ::1 | root | *BD9789AED9E77554D72BA86E93A7DEC64ECC4BFD | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| localhost | debian-sys-maint | *143CE1A220D99EB76B0A61C520ED15EFFDFDAB9A | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | NULL |
| localhost | hassuser | *7A29209F397FC2AB8081AA8F1B5B6054EDEDC174 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | NULL |
+-----------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
It looks like the hassuser doesn’t have any privileges despite my running the command:
GRANT ALL PRIVILEGES ON hass.* TO 'hassuser'@'localhost';
Unfortunately, I won’t be able to test anything in HA right now though as I’m having a startup issue.
No worries
Not sure about your other error. Hopefully someone can help.
Looks like that hass user doesn’t have any permissions, so once you get that sorted hopefully you’ll start seeing Home Assistant using your MySQL connection instead.
Got my other issue sorted out; it was a special character copied a pasted from a forum example that wasn’t excepted properly. I fixed it and also raised a bug issue.
Still not sure why the hassuser doesn’t have any perms though; I used the following command:
GRANT ALL PRIVILEGES ON hass.* TO 'hassuser'@'localhost';
but
SELECT * FROM mysql.user;
shows no change. I did it while logged in as the mysql root user.
Just to see if it’s the permissions user, can you change your configutation to use your root user?
recorder:
# Delete events and states older than 1 week
purge_days: 7
#db_url: 'mysql://root:[email protected]/hass'
Actually tried that, and have the correct permissions for the hass user now.
Neither one of them worked though. I can’t understand it…
Damn, I’m running out of solutions.
Last one I can pull out of the hat. Are you connecting to the correct MySQL Port? By default, I think MySQL uses port 3306, but if you’ve set up a different port number, you need to specify that in your connection settings.
You should be able to see the port MySQL is using my typing
mysql --help
Confirm that it is using the default of 3306. Here is the db users now:
+-----------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | *BD9789AED9E77554D72BA86E93A7DEC64ECC4BFD | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| raspi1 | root | *BD9789AED9E77554D72BA86E93A7DEC64ECC4BFD | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| 127.0.0.1 | root | *BD9789AED9E77554D72BA86E93A7DEC64ECC4BFD | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| ::1 | root | *BD9789AED9E77554D72BA86E93A7DEC64ECC4BFD | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| localhost | debian-sys-maint | *143CE1A220D99EB76B0A61C520ED15EFFDFDAB9A | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | NULL |
| localhost | hassuser | *7A29209F397FC2AB8081AA8F1B5B6054EDEDC174 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | NULL |
+-----------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+Strange!
Alright I’m out of ideas. Unless there’s a log entry in Home Assistant, I can’t see why your HASS can’t connect to your MySQL 