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.
First 2 questions is ‘Yes’. And I checked also the tables after the migration, before starting up HASS.
Dunno what’s special. It’s a quite regular setup I guess. I run both Mariadb and HomeAssistant in separate docker-containers on a Raspberry Pi4.
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