I found that the sum values in the short_term statistics need to match the sum values in the statistics table for a given time. Last weekend I recalculated the sum values in the statistics table only, but the new values were still the old sum values. After I also recalculated the sum values in the short_term statistics I did not have to recalculate the values in the long term statistics anymore.
1 Like
This is a great writeup, thank you! The energy monitor is now running (compared to it not functioning at all in the ‘old’ instruction by OP). However, I’m having similar problems as @DickieDick is facing. All my sensors log fine, but my energy dashboard is calculating costs incorrectly (about 100x too much for electricity and 20x for gas). The electricity values also seem to be offset somehow? I have one monitor for off-peak hours and one for peak hours and they normally don’t overlap. The off-peak hours seem to have a constant value when peak hours is running and vice-versa.
I would like to update the instructions I published originally with the instructions that currently work for hassos 2021.12.X. If one of you can combine what worked when I migrated some years before with what is working now, I would update my original post with the current working steps. I see the beginnings of a complete post, but would appreciate a complete sequence similar to what I posted. I will then credit those that assisted with the updated instructions.
Regards!
We must be missing something with the way that the statistics work - I don’t really see any reason why a complete data migration between two different database engines should make any difference unless there’s still some data missing.
Some things to consider (I’m not claiming to know how this all works, but it would be interesting to see):
Was home assistant off while you exported the data/performed the migration? If not, there could be some statistics_runs that are missing in the MariaDb (statistics are run every hour according to the docs Long- and short-term statistics | Home Assistant Data Science Portal). This could be a reason for the stats data going awry - for example if the energy integration is keeping a note of the last run or something like that.
What is the state class for the entities in your energy monitor? Sensor Entity | Home Assistant Developer Docs - I’m having an issue with mine (even prior to migration to MariaDb) because they’re declared as total_increasing yet they’re actually a daily amount.
My post above is essentially the full steps (incorporating the backup actions from your original post)
1 Like
So I am guessing that this will be the ‘final answer’ for hassos 2021.12…
# 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 database 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;
Please comment if I need to add any additional information
NOTE: updated to correct creating DATABASE hass both here and on the original post.
4 Likes
that looks about right to me - the caveat is some people have reported that statistics (particularly energy) are acting weird post-migration…something we’re trying to get to the bottom of.
1 Like
I’ll hold of until we have final then.
2 Likes
So I tried it again, but still having the same problems (excessive prices and weird graphs). I ensured HA was off from start to finish. Only things that caught my attention were that it does not seem to remove any foreign keys (it returns that no alterations were made) and there were some typos in the instructions regarding the re-adding of foreign keys (just missing spaces) that I changed, but I guess that does not matter.
Not sure how to be of any more help here. Let me know if there’s something you’d like to see from my database.
1 Like
I am thinking about moving to MariaDB.
There are many tutorials how to convert the database.
But is there a way back?
Is it possible to convert the MariaDB-tables back to SQLite?
2 Likes
I think you mean : Created a database hass
Thank you, I have corrected the original post as well as the recent post to create database hass.
I’m facing some issues doing this…
Some of my sensors give an error with regards to the unit of measurement which seem to be broken during dump or import…
2022-01-27 17:52:12 WARNING (MainThread) [aiohue.util] Value {} of type <class 'dict'> is invalid for Device.identify, expected value of type typing.Optional[aiohue.v2.models.feature.IdentifyFeature]
2022-01-27 17:52:12 WARNING (MainThread) [aiohue.util] Value {} of type <class 'dict'> is invalid for Device.identify, expected value of type typing.Optional[aiohue.v2.models.feature.IdentifyFeature]
2022-01-27 17:52:12 WARNING (MainThread) [aiohue.util] Value {} of type <class 'dict'> is invalid for Device.identify, expected value of type typing.Optional[aiohue.v2.models.feature.IdentifyFeature]
2022-01-27 17:52:12 WARNING (MainThread) [aiohue.util] Value {} of type <class 'dict'> is invalid for Device.identify, expected value of type typing.Optional[aiohue.v2.models.feature.IdentifyFeature]
2022-01-27 17:52:12 WARNING (MainThread) [aiohue.util] Value {} of type <class 'dict'> is invalid for Device.identify, expected value of type typing.Optional[aiohue.v2.models.feature.IdentifyFeature]
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] sensor.bathroom_temperature has unit u00b0C which is unsupported for device_class temperature
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] sensor.bedroom_temperature has unit u00b0C which is unsupported for device_class temperature
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] sensor.oneplus_8_pro_battery_temperature has unit u00b0C which is unsupported for device_class temperature
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] The unit of sensor.humidifier_energy_factor (Cos u03c6) does not match the unit of already compiled statistics (Cos φ). Generation of long term statistics will be suppressed unless the unit changes back to Cos φ
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] The unit of sensor.bathroom_energy_factor (Cos u03c6) does not match the unit of already compiled statistics (Cos φ). Generation of long term statistics will be suppressed unless the unit changes back to Cos φ
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] The unit of sensor.dryer_energy_factor (Cos u03c6) does not match the unit of already compiled statistics (Cos φ). Generation of long term statistics will be suppressed unless the unit changes back to Cos φ
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] The unit of sensor.hallway_energy_factor (Cos u03c6) does not match the unit of already compiled statistics (Cos φ). Generation of long term statistics will be suppressed unless the unit changes back to Cos φ
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] The unit of sensor.servers_energy_factor (Cos u03c6) does not match the unit of already compiled statistics (Cos φ). Generation of long term statistics will be suppressed unless the unit changes back to Cos φ
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] The unit of sensor.christmas_tree_energy_factor (Cos u03c6) does not match the unit of already compiled statistics (Cos φ). Generation of long term statistics will be suppressed unless the unit changes back to Cos φ
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] sensor.diskstation_temperature_1 has unit u00b0C which is unsupported for device_class temperature
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] sensor.diskstation_temperature_2 has unit u00b0C which is unsupported for device_class temperature
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] sensor.diskstation_temperature_3 has unit u00b0C which is unsupported for device_class temperature
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] sensor.diskstation_temperature_4 has unit u00b0C which is unsupported for device_class temperature
2022-01-27 17:52:21 WARNING (Recorder) [homeassistant.components.sensor.recorder] sensor.diskstation_temperature has unit u00b0C which is unsupported for device_class temperature
Anyone managed to get onto something here?
I’m soon planning to migrate since I want more long term statistic and I have mySQL already running on server that is running HASS.
I have no idea who is able to sort out the remaining issues. I still don’t feel confident enough to migrate using this guide (still be best! but not error-free unfortunately) because reliability is a must. 
What about using DBeaver for migration - could it be useful?
Heard about it here (Energy Management in Home Assistant - #1156 by ChirpyTurnip) first and saw few others like here (Home Assistant Add-on: PostgreSQL + TimescaleDB - #22 by Ecard) using this software for migrating to PostgreSQL.
Maybe it’s just another tool to achieve the same like on the CLI as shown in former posts.
Seems like Download DataGrip: Cross-Platform IDE for Databases & SQL works just fine with converting sqlite to mysql.
I’ve set it up and am copying sqlite to mysql and HASS seems to work fine with it. Will test now once my second transfer is complete.
First I tested it and it was all seemingly working but now I’m converting the most up to date DB and will continue to use mysql for the following day and see how it goes.
1 Like
Does the trial version do the trick totally or does one need a license? In other words, any limits on fucntionality, database size, amount of records?
Great to hear! I did some further research and I think those information would guide us to a successful migration:
- Tool 1:
Download DataGrip: Cross-Platform IDE for Databases & SQL - Tool 2:
SQLite to MySQL Conversion and sync. | DBConvert - Knowledge 1:
sqlite - Quick easy way to migrate SQLite3 to MySQL? - Stack Overflow - Knowledge 2:
https://www.reddit.com/r/Database/comments/axyf6e/migrating_from_sqlite_to_mysqlmariadb/
And it also seems to be possible to migrate back/vice versa (MySQL to SQLite).
Keep us posted what your results are @zagi988. Especially if Energy Dashboard and other things (noted as malfunctioning in the former posts) are working fine.
1 Like
I’m trying to install mysql but I always get this error
sudo: apt: command not found