That sounds like a potential hot lead. Timestamps might be causing this… the question is, how this happens (maybe depending on environment?and how it can safely avoided during the migration process.
Looking forward to your follow-up experiences on this. Maybe others who already migrated and a) also face these issues as well as b) not having those issues could report their timestamp output, just to sort out if there’s a pattern and this really is a hot lead.
I went the nuclear option: removed all cost entities and afterwards removed all statistics data (through developer options → statistics → delete)
Looks good so far
I did drop the whole costs table so that all costs are 0, so now the next day homeassistant calculated again and again i have stupid high prices.
so this has nothing to do with the migration, but i think its a timestamp problem, because i dropped all the 3 devices in the mariaDB database. so homeassistant does calculate wrong
the dashoard settings are 0.40€ for peak/offpeak/produce peak/produce offpeak
and to test 1.85€ m3 gas.
this is what comes out,
The gas cost got re-created by home-assistant with a new meta_ID of course.
see what happened
You have an own created entity right ?
I can’t delete my one, only from the tables sadly.
But even deleting everything does calculate the value wrong…
So what actually did you do ?
Just go to developer tools → statistics —> and deleted the costs entity ?
Did u re-create that entity or used static cost instead ?
Just curious.
Found the problem, its the fixed price configuration.
Using a entity with the current cost solve the issue.
What i did was create a input helper (unit_of_measurement: EUR/m³) and it started using the correct value
because i have a fixed price and not an entity, so what i think would work is when the migration is complete, i need to re-create that entity and use that, as the old data stay in the tables anyway but can start new from scratch, so that should work.
ill update once i know more
using this template, it uses the data from the input number helper
template:
- sensor:
- name: Today GAS Total Cost
device_class: monetary
state_class: measurement
unit_of_measurement: EUR/m³
state: >
{{ (states('input_number.gasprice'))}}
- sensor:
- name: Today Offpeak Cost
device_class: monetary
state_class: measurement
unit_of_measurement: EUR/kWh
state: >
{{ (states('input_number.offpeak_price'))}}
- sensor:
- name: Today Peak Cost
device_class: monetary
state_class: measurement
unit_of_measurement: EUR/kWh
state: >
{{ (states('input_number.peak_price'))}}
- sensor:
- name: Today Produce Peak Cost
device_class: monetary
state_class: measurement
unit_of_measurement: EUR/kWh
state: >
{{ (states('input_number.produce_peak_price'))}}
- sensor:
- name: Today Produce OffPeak Cost
device_class: monetary
state_class: measurement
unit_of_measurement: EUR/kWh
state: >
{{ (states('input_number.produce_offpeak_price'))}}
// UPDATE
i did do migrate again and use after the migration entity devices, it still go mess up BUT if u delete this first line where it shows state 0 and the line before it, as you can see in the screenshot the data from the line before it is 340,91 and the one before that is 335,726 so its missing 1 digit so you need to delete that. to,and it will be corrected instantly.
So the steps what i did:
So a fixed price is much worse than using an entity.
SO i was using before the migration a fixed price, but after the migration i created a entity containing the price, you won’t lose any old data at all because the old data is still in the database, the new data needs to be corrected 1 time after an update so that day is showing correct, if you don’t care for 1 day just wait till next day and its fixed by itself.
First I deleted the entities (see below)
Then I deleted all data (see below)
But you are right: it didn’t solve anything. Will now perform steps again and then go for a fixed value (as you indicated in the below post)
Don’t use fixed price, its even worse.
Try to use my update post, its a bit much of info but there is no way to solve this problem than creating new cost entities and let the new data read from them.
this is how it looks after the first update after the migration, before it had 540€
in this screenshot i did not use gas, that was this morning, thats why it shows up 0
now awaiting the second update, fingers crossed.
//UPDATE
I give up, this is ridiculous, after the second update it get messed up.
Well i wait till its next day and see what happens, so this clarifies that there is no way to fix the current day automatically, bu you need to fix it manually…
//UPDATE
This morning i checked and it just create random data, so there is no way that i can get cost to work.
look this screenshot.
i did try everything and cant get it done, the only way to really fix this is by deleting all old cost data…
I don’t know how you set-up your cost tracking entity, but what you can try is after the migration using a new entity with a new name so it can store new data, and the old data is still there.
it maybe should work.
The fixed cost doesn’t work for me at all…
i’m trying a total cost entity but i need to investigate how to set-up a total cost tracker entity.
Hi prvakt, thanks for providing the SQL commands and instructions!
Could you please also share the triggers / stored procedures you’ve used? I’m not well versed in stored procedures and weary to copy and paste something from the forum you’ve linked to, especially since, apparently 2 versions are neede (for long and short term stats).
Hello and thanks for the tutorial!
I did all the steps and it looked fine at first, but then it just breaks after a few minutes with Killed.
What could be the reason for this?
Maybe out of memory? What’s the size of your database and how much RAM does your conversion machine have? I thought someone already mentioned this (here?)…
See my comment above database size, ram usage and getting killed.
The database file is 1.8 GB and I have 3 GB of RAM.
The system runs under Synology VMM in a VM.
There I can also expand the memory by a few GB, only then home assistant no longer starts. What do I have to do under the home assistant OS to expand the memory?
Thanks a lot
Maybe just increase swap would do it
There is a bug with the cost calculation, i did some extended tests and came to the conclusion that using the old data stored for gas or energy don’t work when u migrate, as the cost tracker, no matter if its a fixed price or an entity cause a very high output.
To solve that is creating a new gas or energy entity what gonna collect the data from now on and import to the energy dashboard, than it will OK, this has nothing to do with the cost tracker entity.
So if u want to see the old data, just select the old entity device what contains the old data and you can look back of course…
There is no way to fix it, nor in the database or manually trough the gui of HASS…
I do see some people having this issue, so thats why i’m reporting this.
The rest works all great, all entity data gets migrated correct, without any dataloss… so this migration works as expected!! except it MAY cas the cost problem, but it’s only the cost tracker entity…
Regards
Hmm, that seems sufficient, but agree with swap recommendation, You can also try adding the batch flag (see above) to break the work into chunks. Also worth checking your provider docs to see if they have a process killer for vms that exceed some memory/cpu burst limits. (I know I could never run composer on dreamhost for that reason)
Thanks for your work and reporting.
I unfortunately dont have any cost data, but hoping someone else who does and also some deeper knowledge of the schema can identify cause. While I have done dozens of database migrations in my career, and, encoding and timestamps can be problematic in transfer, but otherwise data is data.
The library should use the ecoding and collation required by HA, so shouldnt be an issue. Timestamps would make things inaccurate, shifted, but not exponentially wrong.
So something is not being transferred correctly (bug in sqlite3-to-mysql library) or HA has some bit of information either not store in DB(perhaps .storage/core.config_entries), or stored in DB but without proper relationship/constraint defined for migration to respect.
I’m hoping someone who understands schema and energy dashboard a bit more and has the data to test with can shine some light.
There is indeed a little difference while using the conversion.
The cost device is affected in the statistic_short_term
see my post
The rest is all fine, so whoever uses a cost entity should be aware that it gets messed up, so you need to start using a new entity instead to fix that problem.
Anyway, +++++ for @eddiewebb as besides this little issue it works flawless!, running my second system for a week now 
Can we highlight that little (but very important) information in the post here?
I was hoping to see a solution which makes it just work without the need of fixing stuff after the conversion.
Maybe someone with knowledge of the database scheme and energy dashboard has a „tweak“ for the conversion progress itself.
There is no need for fixing anything, besides some of us having a problem with the cost entity, thats it.
Its a home assistant issue and not a migration issue, as it’s really complicated how the last resets works in that particular entity.
Besides that i tested it for some days now and no problems at all, u can just convert safe.
Always make back-ups.
ALSO IMPORTANT, DURING THE CONVERSION DEPENDING HOW BIG THE homeassistant_v2.db is , THERE WILL BE NO DATA RECORDED IF YOU DUPLICATE THE DB AND KEEP THE OLD ONE RUNNING U CAN MANUAL INSERT THAT MISSING DATA AFTERWARDS ONLY IF YOU KNOW PHPMYADMIN!.
I tell you a way what went totally fine for me having tons of data in my database for almost 2 years.
So what i did:
do all this in terminal :
# Install pre-req
apk add python3
apk add py3-pip
pip3 install sqlite3-to-mysql
than do this, be sure u change password,username,and host,db file
sqlite3mysql \
--sqlite-file /config/home-assistant_v3.db \
--mysql-database homeassistant \
--mysql-host core-mariadb \
--mysql-user homeassistant \
--mysql-password 'YOURPASSWORD' \
--ignore-duplicate-keys
i changed this:
--sqlite-file /config/home-assistant_v2.db \
to this:
--sqlite-file /config/home-assistant_v3.db \
and did not use: (was my choice)
--ignore-duplicate-keys
Always make back-up!!!
Just let it migrate, if its ready, u just change the yaml and let it use the new MARIADB url as recorder and restart Home assistant and you ready. thats it 
As some of us who are using the cost entity, somehow there is a bug/or mechanism that triggers the last reset and mess up the cost data.
The work-around for the cost entity is this:
using this code:
the id’s are found here 
using phpmyadmin.
be sure to change MYNEWID and OLDID and the date 2022-12-01.
thanks to @vingerha
insert the code here, be sure u select the correct database and always make back-ups!
insert into statistics (created,start,state,sum,metadata_id) select created, start, 0, sum, MYNEWID from statistics where metadata_id = OLDID and start < ‘2022-12-01’
When the next energy update happens in the energy dashboard, it could show for this day a negative value, no worries as the next day it calculates correct, and u can manual just correct the value of that day… all the old data is also normal visibel in the energy dashboard.
But remember this is not a fix just a temporary work around, till we know how the last reset works for that particular entity…
Again all credits to @eddiewebb for making this conversion possible