Help: Need to clean energy dashboard database

Configuration
1

Guys, I need some help.
I’ve currently set up a water meter by using a hall sensor attached to an ESP32 board, configured by esphome.
It works fine now but while testing I did a few initial mistakes which filled up my HA energy dashboard database with a lot (!) of wrong values, as you can see on the screenshot.
Now my plan was go get an SQL client and clean this stuff up but as soon as I try to delete those values, it gives me the errors which you can see on the bottom of the screenshot:

Unbenannt
Unbenannt1709×992 173 KB 

Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`)) */

Any SQL crack here who can help me out with this?

2

Not a crack and not a lot of time too but…
From what I am known to: the energy dashboard gets its data from statistics table and that gets it from statistics_short_term (with a periodic ‘reset’) and short_term I assume gets it from states (never looked into that)
So, if your states are repaired / proper …that is the basis as else it will continue…no need to touch/delete these if for energy dashbaord

Then you need to repair / update the statistics and align with statistics_short_term
My recommendation is to create new sensors for the dashboard, wait until they show in the statistics and copy/rework the existing data into that until you are happy with the board, then delete the old ones from statistics and those of the statistics_short_term.

Here a link to a recent LARGE post where I got my knowledge/exp. from
Help needed! Energy database costs calculation wrong, after investigating everything - Energy - Home Assistant Community (home-assistant.io)

3

Thanks for the hints. I didn’t know abou the statistics dependency, I will go that way!

4

What kind of database engine do you use? And what is the name of application is that?

5

Not sure what is used above but common user I see with phpMyadmin for Mariadb/mysql and dbbrowser for SQLite

6

It’s mariadb and the client is heidisql.

1 Like
7

How do you setup to edit the Mariadb? Heidi is a Windows app.

8

Please detail your question, when you setup a more advanced db-server there are always credentials (sqllite is different…‘lite’). The db-server has an ip/port so you can access this via the network as you configured

EDIT, do you have a mariadb setup for your instance?

9

Yes I do have mariadb. I used the add-on to install on my HA OS (Proxmox vm).

10

I think this is the way: