Large homeassistant database files

Hmm have you tried since renaming that old SQLLite file?

I’m wondering if MySQL was working, but when you clicked that object, HASS saw there was an SQL lite DB file and tried to load the history from that, instead of MySQL.

I didn’t try that but will rename the new one and give it a shot and let you know.

Same results. When I use SQLlite, even if HA has only just started, clicking on an object brings up the detail card and a state history graph. With MySQL, nothing. Just sits there with the processing animation.

If I had phpSQL installed, I’d take a look and see if the db was even being written to but I don’t know enough SQL to check it from the command line.

OK so I looked up some SQL commands and although the db is there in SHOW DATABASES; when I issued the following commands (the db name is “hass”):

USE hass;

I get the result “Empty set (0.00 sec)” so it doesn’t appear that HA is writing to the db at all and hasn’t even created the tables and schema. If this is the case though, it should have raised SOME kind of an error but I see nothing in the logs.

@rpitera I also tried to connect my recorder component to a mariadb instance with no luck.
I created a db and a user for ha, made the configuration in recorder but it doesn’t seem to work.
Database is totally empty and with no tables. I don’t get any error on home assistant log.

Did you manage to make it work?

I had the same experience as you @rpitera

I noticed a light kept turning on every time I restarted HASS and realised the database wasn’t quite working, it was pulling in an old state. Unfortunately I don’t know what I’m doing and was waiting for you to come back with a write up so I could see where I went wrong. I had thought perhaps I might need to force the database location somewhere more accessible but it was a stab in the dark.

@dimmanramone No and I saw your post; was going to comment on my situation in it but didn’t want to hijack your post. :slight_smile:

@BarryHampants I was going to prepare a nice write up but it seemed pretty pointless if it wasn’t actually working so I am in a holding pattern. I have MySQL and the dependencies installed and have a working SQL server and db, but unless I can get HA to write to it, I don’t see the point in doing a write up yet.

Maybe @jbardi or @fanaticDavid will check in as they both seem to have working systems.

I do have a working setup with a MariaDB instance but it’s been a while since I set it up. I think it was mid July or something, shortly after the feature was released. At this point, I don’t quite remember doing anything specific to get it to work, other than what’s in the docs. The hardest bit for me was getting used to phpMyAdmin again to set up the user for HASS, as that user needs access rights to the HASS database in the MariaDB instance.

@fanaticDavid Is your MariaDB instance in the same host as HA? Can you check the hass user in phpMyAdmin the access right for the user and if you have defined host for the user?

my database size is 0 mb … i disabled logbook and history and recorder then no databases are generated at all … but you also don’t see any graphs for sensors. I use emoncms for the data & graphs that i need. This is handy if you only want to use home assitant to control devices and see their current state but have no need for any kind of historical data

I set up my MySQL instance with a separate db for HASS and a HASS user with full rights to that db. But got nothing.

My MariaDB instance is running on my Synology NAS. In phpMyAdmin, the HASS user has the following access rights to the HASS db:

Is it the same user that you have in your HA installation? or just a db user?

I’m not sure I understand your question but my MariaDB has 2 user accounts: the default user account which has access to everything (and has nothing to do with HASS), and then the user account I created for HASS which only has access to the HASS db. The credentials of that account is what I put in my db_url in my HASS configuration.

1 Like

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:

  # 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.