SO whoever has an issue with the costs, just wait till the next day and it’s solved by itself.
I forgot to thank you, you are the master, it works perfect.
Finally a way to do it easy!
Regards and have a nice day!
Ill add that tip to my post, thanks for working through!
Not the V2 you need that 
This is the method I use, it drops all tables in the current database. Just open terminal on your ha, type mysql to open, use homeasssistant to select the DB and run. You can also run from phpmyadmin. MySQL DROP all tables, ignoring foreign keys - Stack Overflow
I did forget to reply him.
It gave me the same error as he has, but what i did is just deleting the mariaDB homeassistant database and leave it like that.
And NOT db_v2
And then running your script and it auto creates a new database called homeassistant and no errors anymore.
1 Like
Not sure if i have the same “problem” . Some of my historic data seemed mixed:
The whole migration whent through without any errors or similia. The humidity/temperature sensor history got values from the local gas station and vice versa.
That is quite odd. It seems like some reference IDs got mixed up. While I am tempted to suggest a manual data fix (update table where name = ‘one of those’ and entity_id = ‘the others rowid’ ) - BUT i don’t know nearly enough about HA’s data model to give exacts, AND I’m dumbfounded as to why that could happen.
But we could do some recon if you have phpmyadmin or query access through terminal
- statistics_meta table will have names and IDs, grab id for your station and temp. (2 switched values)
- Use those ids to run
SELECT * FROMstatisticswhere metadata_id = THAT_ID;
Are the values consistent through the history or flipped like the charts?
tbh i can’t find the matching ids. It looks like they are all messed up. I’m not sure if i had deleted some entities between the switch from sqlite to mysql, but its not a big deal.
The HA server is pretty young and there is nearly no data so its not a big deal.
1 Like
it does look like they switched between 2021-11-09 and 2022-11-17 (you can see in both they jump from single to double digitis or from double to single digits.
Did you migrate to clean DB (fully deleted or fresh?)
If you care enough to know and dont care enough to protect, you could find the exact timestamp of the switch you could do something like:
ENTITY_1_ID is new/current meta_id being wrriten to history, ditto for #2.
/* Correct first entity to use temp ID */
UPDATE statistics
SET metadata_id = 999999 /* final step is correct mark as #1 */
WHERE
metadata_id = 'ENTITY_2_ID' /* the 'wrong' id so to speak */
AND
created < 'THEOLDEST TIME USING NEW ID';
/* fix entity # 2 */
UPDATE statistics
SET metadata_id = 'ENTITY_2_ID'
WHERE
metadata_id = 'ENTITY_1_ID' /* this is why we needed the temp id, or else wed just set them all back! */
AND
created < 'THEOLDEST TIME USING NEW ID';
/* fix temp ids on #1 */
UPDATE statistics
SET metadata_id = 'ENTITY_ID_1'
WHERE
metadata_id = 999999;
Does that make sense? Basically since HA started using a new ID (confirm in statistics_meta by looking at name of that id) we can just update all the old historic data to use that ID. You’d run that query twice, swapping the IDs so both entity history is corrected. CAVEAT - you’d need a “temp” id, maybe 999 to use on the first set, otherwise it would just blend in with all the incorrect data.
I AM ONLY RECOMMEND THIS FOR SCIENCE AND BECAUSE YOU SAID YOU DONT CARE
My HA ist running on Synology DS Virtual Maschine so for “science” I just made a duplicate of the HM and use it for testing.
I’m pretty sure I migrated into a fresh db but I could give it another spin.
Even after a few days I still have issues with the energy costs. Tried removing the costs and re adding them, but it still gives wrong readings. Any tips?
Thats weird, for me it got solved…
WHat u can do is this:
Go to the energy dashboard and change the costs (NOT UPDATE), just put something else in there and press save.
Go back and change it again to the correct cost and save again and restart home assistant.
now next day it should be fine.
If this doesn’t work for you, ill have time this week to help you out.
i will test again with my test system and try to find a easy solution.
regards
Weird, i have tons of stats, and they are all fine.
There must be something wrong.
What i did is let it create a own database and not make one yourself.
try to delete the home assistant database from MARIADB and leave it like that
and run the script and it should migrate without any problems… beside some of us have a problem with the energy costs display to high.
Tried all the steps you indicated, but even after a day the costs are still wrong 
any help would be highly appreciated!
Should HA be stopped when running the migration or can I just have it open and running?
My thoughts:
- If you leave HA running, there is data (partially) merged due to continued activity.
- If you close HA Core, the web SSH becomes unavailable.
So just checking to ensure I’m copying it properly.
Do you have done this to your main system ?
Or you have a backup to revert back ?
All the data did copy correct, without any data loss…
I did do it while hass is running ofcourse, its up now.
I did do this with tons of data, the only thing i had was the energy costs where messed up.
Or you just download sqlite3-to-mysql and copy the home-assistant_v2.db to that folder and run this
sqlite3mysql \
--sqlite-file YOURFILELOCATION \
--mysql-database homeassistant \
--mysql-host MARIADBURL \
--mysql-user MARIADBUSERNAME \
--mysql-password MARIADBPASSWORD \
--ignore-duplicate-keys
My main system. Do have a backup, but that’s already over a week old
Ok, i gonna try to help you how we can fix this issue.
So all the other data is correct right ?
Everything else appears correct (historical energy data, but also the more recent data in the logbook etc)