Thanks for this guide. It seems to work also for migrating long-term statistics (such as energy management), however it seems that after the migration these values are no longer updated. I guess something similar with AUTO_INCREMENT should be done for the statistics, statistics_meta and statistics_run tables (more information here)? Any idea on how to alter these tables to restore the functionality?
1 Like
At the time I did the migration in the original post, the long term statistics you mention were not available in HACS yet. as you mentioned, you would probably need to do the same steps for the referenced tables and I would imagine would be similar to the steps for turning off auto index, making note of the current index values, then copy the data, update the auto increment value, and turn auto increment back on.
phpmyadmin will be your friend when figuring out the auto increment structure for the new tables.
I‘m looking for a bullet-proof, up to date (meaning HA 2021.12 release) guide on how to migrate all data from SQLite to MariaDB/MySQL database.
This one here is really the best (because: the only one!) I could find after searching the whole forum. Unfortunately seems to be outdated as stated in the previous comments. Maybe someone can guide on an up to date, proven guide - or at least add the missing modification steps for the „newer“ tables.
1 Like
I wonder if InfluxDB would be a good option so you could set the number of measurements per entity to keep and avoid having to trim/purge/manage the database over time?
There is much more of us that would like to migrate to MySQL but don’t know actually how to.
I’m running MySQL DB for my webpage and I have basic mysql knowledge but not nearly enough to do this migration myself and these guides don’t really help with newest HA updates.
1 Like
I think I have just managed to migrate my SQLite DB to a Mariadb on another server (with over 1million rows in the states table and 2.7million in the events table!)… it appears to still have the entire available history in home assistant and is updating without errors in the logs. I had to do some additional steps on the database side to the original poster’s instructions, mainly to include the statistics tables and their foreign key constraints…
Details below:
Prepare the sqlite3 data (as per OP instructions, pasted below for ref):
# The first few steps here are typical install and configuration of mariadb.
sudo apt install mariadb-server mariadb-client
sudo apt install libmariadb-dev sqlite3
sudo mysql_secure_installation
I did the following 2 steps using phpmyadmin
Created a user hass with all permissions
Created a table hass
Stop the hass server process on your server.
sqlite3 home-assistant_v2.db .dump > hadump.sql
git clone https://github.com/athlite/sqlite3-to-mysql
copied sqlite3-to-mysql to same directory as hadump.sql
bash sqlite3-to-mysql hadump.sql > haimport.sql
mysql -u hass -p -h localhost hass < haimport.sql
## the above mysql statement imported about 55K records in both states and events, 360 into recorder_runs, as well as 6 records into schema_changes for my home-assistant with a 2 day history. Your mileage will vary.
“Fix” the data and update tables:
mysql hass
update events set event_data = REPLACE(event_data, '`', '"');
update states set attributes = REPLACE(attributes, '`', '"');
# Remove Foreign Key Constraints
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_1`;
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_2`;
ALTER TABLE `statistics_short_term` DROP FOREIGN KEY `statistics_short_term_ibfk_1`;
ALTER TABLE `statistics` DROP FOREIGN KEY `statistics_ibfk_1`;
# alter the primary key in the tables to use auto_increment, starting from the last run id +1 (replace N+1 with the maxid+1
select max(run_id) from recorder_runs;
alter table recorder_runs modify column run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
select max(event_id) from events;
alter table events modify column event_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
select max(state_id) from states;
alter table states modify column state_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(change_id) FROM schema_changes;
ALTER TABLE schema_changes MODIFY COLUMN change_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(id) FROM statistics;
ALTER TABLE statistics MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(id) FROM statistics_meta;
ALTER TABLE statistics_meta MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(run_id) FROM statistics_runs;
ALTER TABLE statistics_runs MODIFY COLUMN run_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(id) FROM statistics_short_term;
ALTER TABLE statistics_short_term MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
# Add back the foreign key constraints
ALTER TABLE`states` ADD CONSTRAINT `states_ibfk_2` FOREIGN KEY (`event_id`)REFERENCES `events`(`event_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE`states` ADD CONSTRAINT `states_ibfk_3` FOREIGN KEY (`old_state_id`)REFERENCES `states`(`state_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE`statistics` ADD CONSTRAINT `statistics_ibfk_1` FOREIGN KEY (`metadata_id`)REFERENCES `statistics_meta`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `statistics_short_term` ADD CONSTRAINT `statistics_short_term_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
4 Likes
Great! Those are the additional steps, right? Together with the above (outdated) guide it worked with HA 2021.XX.Y… ?
Sorry - yes I am currently running 2021.12.3 (and updated instructions above with O/Ps migration steps for a full reference)
1 Like
Slightly related, I also installed Mariadb on a Mac mini (running as a server) using this guide:
or you can use their docker container (Docker Hub)
nb make sure you have created a user from the mysql command line as well, e.g.:
create database hass;
grant all privileges on hass.* to 'user'@'%' identified by 'password';
flush privileges;
and an easy way to get phpmyadmin running to be able to connect to your Mariadb (if not running the add-on on a Raspberry Pi) is using the phpmyadmin docker image:
docker run --name myadmin -d -e PMA_ARBITRARY=1 -p 9086:80 phpmyadmin
then you can connect to http://server-name:9086 to log in and view the db from a UI.
I also set up InfluxDB with the intention of having a shorter purge_keep_days in order to keep the db at a reasonable size.
Again this is on a server external to the home assistant instance running on a raspberry pi in order to reduce the strain on its SD card. The add-ons for influx, Mariadb and phpmyadmin are awesome though if you have an SSD on your pi.
ex config:
influxdb:
api_version: 2
ssl: false
host: !secret influxdb_host
port: 8086
token: !secret influxdb_token
organization: !secret influxdb_org
bucket: homeassistant
tags:
source: HA
tags_attributes:
- friendly_name
default_measurement: units
I went with Mac home-brew but docker is probably a good alternative too.
Thanks, I’ll definitely give this a go. I’ll be standalone as well (as a vm) vs Docker as that’s how my setup is structured currently.
Is your database still working as expected?
Yes it seems to be. The previous history is available and any new events are saved as I would expect.
1 Like
Unfortunately the energy dashboard is totally messed up. It gives a strange graph and the costs are also incorrect. I installed it yesterday and it thought I had used over 200 bucks for gas and electricity. Well, I used the old guide from 2019, but this morning I did a re-migration to MariaDb with this instruction and the same.
I reverted back to sqlite3 (unfortunately).
For the rest it seems to work nicely.
Maybe there are some additional steps needed for the energy dashboard data (which is based on the statistics and statistics_meta tables)?
Can you confirm this @ChrisJohnston ?
I wouldn’t know any different as the main reason for moving to MariaDb is I’ve just started collecting energy statistics and was noticing that the sqlite3db was growing much faster than before. I have about 2 weeks of consumption data collected from some zwave switches, which seem to be fine.
I’m using a custom component for octopus energy, which hasn’t seemed to work properly since I installed it as the energy dashboard seems to want it to be continuously accumulative rather than a daily kWh value (it’s loaded a day late anyway due to the way the octopus API reports it).
If it helps, my statistics, statistics_meta and statistics_short_term tables are structured as follows:
statistics:
statistics_meta:
statistics_short_term:
Note I just corrected a typo in my SQL above for the foreign key constraint on the statistics_short_term table (missing space) - so maybe make sure that this constraint is present.
I found the typo, and there’s another one. Instead of run_id it says id for adding the constraint on table statistics_run.
My energy-consumption is measured with the P1-meter and the HomeWizard Energy HACS-addon.
The sum is from the date I started HASS (about a month ago) and looks correct.
But this one:
is quite odd. The total stays the same (well, there’s hardly any sun to deliver back to the net) and here the sum increases. This was after my migration.
And this one even is decreasing, so that in the graph the returning to the net seems to be drawn from the net. Wondering where and how these statistics are calculated.
Fixed this.
The energy integration is all calculated values - when you first add a source, it does give a warning that it may take a few hours for the data to be correct. Perhaps it’s still crunching your numbers.
Well I thought it would but I had it running yesterday the whole day and hoped that today things would get back to normal but unfortunately it didn’t so back to sqlite. Maybe I’ll give Postgresql a try.
But thanks for the tutorial. It was working like a charm. And I liked to be able to view the tables a bit more convenient.
I doubt the (your) energy dashboard data issue is related to the underlying database.
Maybe the migration (perhaps the two typos?) created this issue, but switching to Postgres won‘t change anything I guess. By the way MySQL and SQLite are the most used database storage engines used for HA. Me personally wouldn’t go with a quite rarely used one - because of the lack of community support.
Anyway, this finding made me feel a bit uncomfortable (before that: „Wow great! Let’s migrate now, let’s do it!“) and I would like to see some others (who performed the migration using the guide Migrating home assistant database from sqlite to mariadb - #43 by ChrisJohnston) confirming everything is alright with their energy dashboard data.