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.
it wonāt be the typos as I already correctd them before running, well the one creating a foreign key on statistics_runs failed due to the typo, so had to correct that. According to my screenshots it must be something different. What could be is that the exportfile wasnāt completely correct. I might give it another try another time.
Was checking out by modifying the weird values in statistics to correct values and then restarted HASS with usage of mariadb, but then I saw the negative sums in table statistics_short_term showing up, so I guess it has to do something with that table.
I gave it another try and did the whole process another time to find out that the graph would be off again. I then dug deeper and found sum-values changing where the corresponding measured value stays unchanged.
Corrected the values but it keeps getting off.
In statistics_short_term that sums are decreasing/increasing whereas the corresponding measure value didnāt change:
So for metadata_id 5 0.004 is added every 5 minutes and for metadata_id 6 0.005 is subtracted and finally for metadata_3 0.002 is added every 5 minutes. Only metadata_id 4, currently changing seems to be correct.
But ofcourse I donāt want to keep editing values.
Interesting. While I canāt help you with the energy dashboard data (I currently have another job with that dataā¦ Energy Management in Home Assistant - #1127 by e-raser - because of my own fault back in Novemberā¦ those data can be quite tricky!) Iām interested:
- this behavior only occurred after the migration?
- when running on SQLite itās fine?
Depending on that:
- whatās special with your setup?
- why is it working (or seems to as not many responses on this yet) for others?
We really should clarify those questions to finally mark the migration guide as āsafeā to use, hopefully.
Iām not brave enough yet to test this on my productive HA environment.