Help: Need to clean energy dashboard database

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:

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?

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 (

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

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

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

It’s mariadb and the client is heidisql.

1 Like

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

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?

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

I think this is the way: