Yeah its indeed strange, right now i did try a second time.
this time i did copy my homeassistant_v2.db to an other folder and did run the script again, i imported it and now also good value’s but i wait till home assistant update again and see whats happens.
I left the number format to auto this time
//UPDATE
the cost devices are messed up again… i’m now out of options.
I really did try every way to do this…
I do see a difference how home-assistant does save the data in mariaDB, the time is different.
I have a machine that is a clone of my main, but runs just from scratch mariaDB, i do see it saves the data differently.
look here, this is from the clone server that runs with a new db, just from scratch (MARIADB)
and this the migrated one
using this query (this is my energy device) it saves different.
SELECT * FROM statistics WHERE metadata_id =6 Order By created Desc
i did drop all monetary devices from my phpmyadmin [statistics_meta], see what happens if they get re-created…
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 
