I’ve set up an input_number sensor to count up my electricity usage in kWh. It works fine but when i set it up some spurious data got logged that is way outside the proper range, so now my graph in Grafana is scaled wrongly.
So, I loaded phpMyAdmin, found the table that contained them, isolated the miscreant rows and deleted them from the table (there were 13 of them, around the zero point when the real reading should have been 16500 ish to match my electricity meter).
But the points are still there in the grafana graph!
I’ve rebooted HA as well as deleting the graph and recreating it, but they are still there. But, they are not in the database (InfluxDB).
Any ideas?
You deleted data via phpMyAdmin in SQL database, not in Influxdb. If you datasource in Grafana is Influxdb, maybe problem is here.
I must confess, I don’t know what I’m doing (still). Within phpMyAdmin there are what look like several databases. Grafana - which is empty, HomeAssistant - which is where I found the states table and did the deletions, Traccar, performance_scheme, MySQL, and phpMyAdmin.
I couldn’t find a table of states anywhere else.
I have MariaDB and InfluxDB loaded and looking at the recorder entry in the YAML file it points to MariaBD. I also have an entry for Influx but I cannot see where anything ‘points’ to it other than in the settings for Grafana where it is the default db (Maria is also listed there).
Anyway, is there some other way I can find and delete these datapoints I don’t want.
Which datasource you use for Grafana dashboard: InfluxDB or MariaDB?
phpMyAdmin is an interface to view/modify/manipulate MariaDB data, and “HomeAssistant” database in phpMyAdmin is the right place to do such things.
If you are using InfluxDB in parallel, states data also recorded to InfluxDB, and if your Grafana dashboard data source is InfluxDB, then you need to delete datapoints in InfluxDB.
OK, I get that, but how?
I have HA and all the add-ons running on a Raspberry Pi. I do have MySQL on my PC so originally I was trying that but just couldn’t get a connection; then i came across phpMyAdmin which looks cool but obviously doesn’t do what I wanted.
I was hoping the data would only be stored the once and that phpMyAdmin would help.
You need to use InfluxDB addon UI, where you can check and manipulate InfluxDB data: (InfluxDB and Home Assistant (Part 2) | dummylabs.com)
How to delete data: InfluxDB: Removing or deleting data
What, you’re teasing me. I have Influx DB but what’s this UI bit? can’t find any reference to it. And the link mentions nothing about deleting points/rows from the database.
I’ve googled it and i’m not the first with this problem. For now I’ve got round it by putting a ‘where value >= 1000’ into the SQL select statement.
Sorry 
, UI=user interface
I know that that. Anyway, I’ve just spotted it in the Influx DB Add-On info tab. Don’t really know how I’d missed it before! I’m in there now and will see what I can do.
Thanks for bearing with me.
1 Like