Well, what i’m gonna do is setup another system just to debug and check where the error comes from.
If i know more i let u know here, and i try to find a solution for you.
regards
Actually there is a weird error going on and i don’t know how to fix this.
This is before the migrate
sensor.p1_electricity_consumed_off_peak_cumulative_cost
sensor Cost
36.5251999999997 state_class: total
last_reset: 2022-11-16T10:15:55.119094+00:00
unit_of_measurement: EUR
device_class: monetary
friendly_name: sensor Cost
sensor.p1_electricity_consumed_peak_cumulative_cost
sensor Cost
27.8880000000001 state_class: total
last_reset: 2022-11-16T10:15:55.119763+00:00
unit_of_measurement: EUR
device_class: monetary
friendly_name: sensor Cost
sensor.p1_gas_consumed_cumulative_cost
sensor Cost
29.1619400000004 state_class: total
last_reset: 2022-11-16T10:15:55.122524+00:00
unit_of_measurement: EUR
device_class: monetary
friendly_name: sensor Cost
this is after the migrate:
sensor.p1_electricity_consumed_off_peak_cumulative_cost
sensor Cost
0.0 state_class: total
unit_of_measurement: EUR
device_class: monetary
friendly_name: sensor Cost
last_reset: 2022-11-22T17:15:02.354903+00:00
sensor.p1_electricity_consumed_peak_cumulative_cost
sensor Cost
0.247999999999956 state_class: total
unit_of_measurement: EUR
device_class: monetary
friendly_name: sensor Cost
last_reset: 2022-11-22T17:15:02.355708+00:00
sensor.p1_gas_consumed_cumulative_cost
sensor Cost
0.569220000000378 state_class: total
unit_of_measurement: EUR
device_class: monetary
friendly_name: sensor Cost
last_reset: 2022-11-22T17:15:02.357128+00:00
there is no way to manual change the monetary device, not even in the statistics tab…
Maybe someone can tell us how to do it.
Last time i waited 1 day, and it did change indeed my costs back to normal, but not this time.
and this is my first test machine, im now testing on the next one, and wait till its next day.
and also only 1 single error in home assistant. but no error in the migration.
all sensor data correct, except the monetary devices.
sqlalchemy.exc.IntegrityError: (MySQLdb.IntegrityError) (1062, "Duplicate entry '1-2022-11-22 16:25:00' for key 'ix_statistics_short_term_statistic_id_start'")
[SQL: INSERT INTO statistics_short_term (created, start, mean, min, max, last_reset, state, sum, metadata_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: (datetime.datetime(2022, 11, 22, 17, 15, 52, 330863, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 11, 22, 16, 25, tzinfo=datetime.timezone.utc), 15.4, 15.4, 15.4, None, None, None, 1)]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
And would it be an option if I manually remove all my monetary sensor readings from the DB and then re-add the monetary values via the UI (thus losing historic monetary values, but at least have correct ones moving forward)?
I did not try that, yes maybe that should work, as it will calculate everything again.
you should try it, let me know
I did another test to just restore a backup i just created, and the monetary devices get messed up.
so i think it has nothing to do with the migration to mariaDB,
I investigate further now.
Just a question, did you try to “Use an entity with current price” what i did last time when it worked to fix the prices, i did use “Use an entity with current price” maybe that will resolve the problem for now.
I will try and look, as 1 of my monetary devices is an entity i created, and for now that is showing up correctly
I did use an entity (created as a helper). I already tried fully removing those helpers and recreating them (with different name), but that had the same results
I opened an issue on github.
Maybe if we provide more info they maybe can fix this.
as this issue happens also when i restore a backup, really weird.
1 Like
Edit: I was able to fix the error. I had connected the Mariadb to Home Assistant via the configuration.yaml. Data was written to the database when it was started. On the next try I didn’t connect the mariadb. Now all my data is there.
I have the following problem. I am using Home Assistant 2022.11.4. Migrated the data from SQLite to Maria DB as follows:
# Install pre-req
apk add python3
apk add py3-pip
pip3 install sqlite3-to-mysql
# run migration (CHANGE PASSWORD)
sqlite3mysql \
--sqlite-file /config/home-assistant_v2.db \
--mysql-database homeassistant \
--mysql-host core-mariadb \
--mysql-user homeassistant \
--mysql-password 'YOURPASSWORD' \
--ignore-duplicate-keys
No error is displayed when migrating. My energy dashboard now looks like this. The solar values are no longer available.
Before:
After:
Did some more deepdiving into the issue. Apparently I have a sensor called “sensor.p1_meter_3c39e72bf1a4_total_gas_cost” this is the only sensor I can find that carries a monetary value for gas consumption. If I look into how this sensor evolves it is fair to assume it is total costs (always increasing). If I estimate the costs for today (see below graph) I come to a cost of about 4,5 euro’s (which could be correct as I have consumed 1.52 cubic meters of gas at a cost of about 2,47 per cubic meter → 3,7544 euro’s which is in the ballpark). Now if I look at the energy dashboard the costs of gas consumption are € 2.028,60. So I can conclude that either sensor “sensor.p1_meter_3c39e72bf1a4_total_gas_cost” isn’t the sensor being displayed in Energy Dashboard OR something else is being added to it…
Hi, I am looking into moving my database across to MariaDB and your solution looks the easiest I have seen. Just a quick question before I begin, apologies if it’s a stupid question. From what I understand the migration will throw an error if MariaDB has tables existing already. But, as per the prerequisites, if I install and activate the MariaDB addon and configure HA to use MariaDB as the recorder, wouldn’t that in itself create tables before I’ve started the migration?
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…