Taking the database apart from the same machine HA is running apparently introduces more dependencies and increases the possibility of things breaking. A half-working HA „just“ because the other machine hosting the database is gone for a moment (maybe due to network hiccups) is nothing I am looking forward, not speaking bout performance (network speed / latency etc.). Of course if you have perfect environment it might work really well, maybe even better than having everything in the same box.
Just my opinion, which is why I‘m personally not going to outsource my database but instead keep it running on the same host (HA OS).
But here it’s about migration (from SQLite to MySQL/MariaDB)
Yea, energy cost calculations got messed up for me after migration to MariaDB. It now just accumulates all the sums, instead of all the differences. It’s possible to recalculate the history in the database, but it still continues to calculate wrongly. I posted a more detailed reply here.
Now I’ve just let it be, since I’ve already spent a day or two trying to fix it.
Thank you all for sharing. That’s obviously a no-go for some users willing to migrate, but not accepting the loss of LTS / energy dashboard data. Hope you get it fixed somehow.
Maybe all of you affected can summarize what migration method you used so that @jr3us perhaps can add a disclaimer/warning sign in the original post. Nobody lucky to find these great migration how-tos will be happy finding out the energy dashboard in the end got messed up.
I took a simpler or lazier approach, and used a well supported library to quickly migrate home assistants sqlite database into MariaDB/MySQL with full history. There is no need to even transfer files - just a couple commands and done!
Prerequisites
install and activate MariaDB addon
Install and activate the Terminal & SSH addon
Maria DB Migration Steps
Stop the recorder service from developer tools
( ‘Developer Tools’ > ‘Services’ > Search for Recorder: Disable > Press the ‘Call service’ )
Start the Terminal & SSH addon (mariadb should be running as well)
Run the commands below to install python (already exists on host, super fast)
Run DB migration
Post-Migration (activation)
Configure HA to use new mariadb as recorder in configuration.yaml
will it work with HAOS?
Yes. The installed tools only need to exist as long as the commands take, and the Terminal & SSH add-on works great.
I got an error from mysql (invalid column, duplicate key, etc) !
Backup mysql, purge all tables, and try again. MariaDB tables should not exist when we start migratration, will be fiully created from sqlite. ONly the database itself should exist.
Can I use if database is on another host?
Yes, just change the ‘-h’ (hostname) argument to wherever it lives
Can I use with phpmysqladmin?
The python tool wants direct access to the MySQL database, just like phpmysql. You need access to the HA instance with ability to run python (hence Terminal addon)
What about Energy Dashboard (and statistics generally)?
Yep, all there. This migration should be everything in sqlite. (thanks @jusii, @malosaa)
My Energy costs are all messed up after migrating!
Giving Home Assistant a day to work through should correct this, (thanks @malosaa)
Are there size limits?
In theory any existing sqlite db can be migrated. only the resources of your machine limit that, but even little NUCs should get it done eventually.
And also…
Tremendous thanks to all the folks below who tested, questioned, and refined this method! We got a winner!
So on which setup did you / can one use this? Will it work on HAOS too (because installing stuff is - if at all - only temporary due to the dockerization which is reset on container recreation etc.)?