You can use DEveloper Tools > Services > Recorder: Disable to leave HA running but stop recording data to the DB for a minute while you migrate.
1 Like
Good catch in fact!
I did both ways, but found stopping recorder and not preconfiguring seems to go smoother for folks. Updated the guide above, thanks!
1 Like
@eddiewebb I migrated and had the recorder running. Took about 2hrs, but I didn’t seem to miss any data. So far, so good!
I still don’t get it, after days of searching.
Could it maybe have to do with a comma or point ?
Maybe the conversion goes different than our setup?
How do you have this set-up ?
I pay 0.80euro per m3, and it does calculate wrong…
My monetary device before the migrate shows up differently.
I think how that device works is like this:
- the device called sensor.p1_gas_consumed_cumulative_cost shows 0.02101€
- i did set up 0.80ct as usage per m3 in the energy dashboard
- that day i used 2m3 so it shows 1.60€ at the costs, thats correct
- when checking in statistics and clicking on sensor.p1_gas_consumed_cumulative_cost it shows a lot of different value’s, thats because it calculate the 0.80€ per single usage… so every second or 10 it calculates a value, and thats why we can’t manual fix it.
After the migration whe device sensor.p1_gas_consumed_cumulative_cost start showing up 3€ at the statistics page every single calculation, and i don’t know why this happens.
What could be is the comma or point issue, during the conversion.
Thats why
Do you have a problem with the monetary devices in the energy dashboard ?
Maybe if you go to developer tools – services — recorder disable
And than delete the database called homeassistant in MARIADB and re-run the script while recorder is disabled.
Maybe it will work. this is one thing i can’t test right now
regards
//UPDATE
I got a success test 
What i did was this, i did not stop recorder at all.
I did remove this line, --ignore-duplicate-keys line and no error and the costs are good now.
My test hass server was empty, only my evergy devices setup in my energy dashboard showing no data at all, but i manual did add the costs in the dashboard.
and added my power/gas devices (plugwise) and set them up.
After that when my database was migrated, i did change recorder config in my yaml and restarted, and it updated all my data in the energy dashboard with the correct costs.
Right now i wait 2 hours to let it update with new data and see it it gets messed up again.
fingers crossed.
//UPDATE2
No the energy gets mixed up after homeassistant updates.
With weird 13€ as a value all the time.
this is from my original one
this is from the migrated one
Am currently having the below. I have just changed it to the correct notation (also a Dutchie) to see if that changes stuff.
EDIT: weird… just before changing the number notation the monetary value for today was at 31.000 euro something. After changing number notation it is now 457,27 €…
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:
- Duplicate the homeassistant_v2.db in the same directory and give it a name like homeassistant_v3.db
- Run the above script without the duplicate parameter.
- create input devices where u can specify the current costs, if you have already, create new ones or duplicate them or rename them (DO NOT DELETE YOUR OLD ENTITIES, YOU WILL LOSE ALL OLD COSTS) and select the new ones in the homeassistant Energy dashboard.
but first use my template and specify your entities, i did post a quick sample - go to your yaml and make sure everything is correct, and change the recorder setting to the new migrated one and restart.
- if everything goes correct everything will be fine, and the energy dashboard will show everything correct.
- however when hass updates every hour it maybe mess up your cost device, wait 1 day and it will be fixed by itself, but your current day will be still messed up, they show up very high, to correct that u need to remove 2 lines in the database (from all the cost entities), as shown in the above screenshot.
there is 1 top line that has a state 0 and the one before it, u need to delete them and it will start calculating normally.
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?)…