"Developer tools > Statistics > Delete selected statistics" not working

[jprates]

The problem

Selecting statistics for deletion appears to be working, i.e. no errors are shown on the UI, no errors logged in the core logs, but nothing happens, statistic ids stay in the list and refuse to go away.

And no, it's not a matter of waiting, I've waited over one day long and they are still there.

What version of Home Assistant Core has the issue?

2024-12-5

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

core

Link to integration documentation on our website

No response

Diagnostics information

I've noticed a growing list of statistics with issues on my setup, now at 180.

When I tried to select all with issues and pressed delete, nothing happened.
Assuming it would be too much to ask for, I then selected only one problematic statistic, and again, nothing happened.
Here's a sample of some of them showing up on the UI (please note I ran this several times during 2 days, always with the same result):

And here are the entries in the core log (debug mode) when that call for deletion is done:

2024-12-21 11:13:42.628 DEBUG (Recorder) [homeassistant.components.recorder.core]
 Processing task: ClearStatisticsTask(on_done=<function ws_clear_statistics.<locals>.clear_statistics_done at 0x7f2abedc5bc0>,
                                      statistic_ids=['sensor.ups_battery_voltage',
                                                     'sensor.telemovel_mrd_battery_level',
                                                     'sensor.telemovel_mrd_battery_power',
                                                     'sensor.telemovel_mrd_battery_temperature',
                                                     'sensor.telemovel_jprates_battery_level'])

Some short time afterwards, the log indicated there was nothing more pending for the database, or so it seems:

2024-12-21 11:14:37.037 DEBUG (MainThread) [homeassistant.components.recorder.core] Recorder queue size is: 0

Since the statistics were still showing up, I did in sequence, with no practical results:

• refresh web page with CTRL-F5
• restart home assistant
• restart host

I then proceeded to check the database side and to my surprise those statistics ids don't even exist:

SELECT * FROM `statistics_meta`
 where `statistic_id` in ('sensor.ups_battery_voltage',
                          'sensor.telemovel_mrd_battery_temperature',
                          'sensor.telemovel_mrd_battery_power',
                          'sensor.telemovel_mrd_battery_level',
                          'sensor.telemovel_jprates_battery_level');
                           
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0006 seconds.) 

I then asked myself, could it be that there is no foreign key constraint and there are child rows on the stats?
But apparently no:

SELECT * FROM statistics s
 WHERE not exists (select 1
                    from statistics_meta m
                    where m.id = s.metadata_id)

MySQL returned an empty result set (i.e. zero rows). (Query took 4.3579 seconds.)

SELECT * FROM statistics_short_term st
 WHERE not exists (select 1
                    from statistics_meta m
                    where m.id = st.metadata_id)
                    
MySQL returned an empty result set (i.e. zero rows). (Query took 2.9587 seconds.) 

Could there be some sort of database corruption?
Apparently no, since all these SQL commands returned OK:

check table statistics_meta extended;
check table statistics_short_term extended;
check table statistics extended;

optimize table statistics_meta;
optimize table statistics_short_term;
optimize table statistics;

analyze table statistics_meta;
analyze table statistics_short_term;
analyze table statistics;

I've run out of resources, I don't have a clue where those stats on dev tools are coming from, but apparently they're saved on some sort of cache on HASS (???), since they don't exist on database side, and they are pesky little fellows that refuse to just die off.

All seems to indicate we have a bug in this module, but please, if I'm the one missing something or doing something wrong, do let me know.

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

[home-assistant]

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of recorder can trigger bot actions by commenting:

• @home-assistant close Closes the issue.
• @home-assistant rename Awesome new title Renames the issue.
• @home-assistant reopen Reopen the issue.
• @home-assistant unassign recorder Removes the current integration label and assignees on the issue, add the integration domain after the command.
• @home-assistant add-label needs-more-information Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue.
• @home-assistant remove-label needs-more-information Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

recorder documentation
recorder source
_{^{(message by IssueLinks)}}

[home-assistant]

Hey there @ThomDietrich, @gjohansson-ST, mind taking a look at this issue as it has been labeled with an integration (statistics) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of statistics can trigger bot actions by commenting:

• @home-assistant close Closes the issue.
• @home-assistant rename Awesome new title Renames the issue.
• @home-assistant reopen Reopen the issue.
• @home-assistant unassign statistics Removes the current integration label and assignees on the issue, add the integration domain after the command.
• @home-assistant add-label needs-more-information Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue.
• @home-assistant remove-label needs-more-information Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

statistics documentation
statistics source
_{^{(message by IssueLinks)}}

[jprates]

Hi @gjohansson-ST , don't mean to be rude or anything alike, but are you sure the issue is not in the statistics part?
From what I could gather the database/recorder side did indeed clean the records from the tables, or so it seams.
Where are those rows in the developers statistics tab in the UI coming from?

Merry xmas!

[gjohansson-ST]

Hi. Non taken.
I removed the statistics integration label which is not the same as the long term statistics which you are referring to (recorder).

Merry Christmas 🎄

[jprates]

Thanks for clarifying that, I was not aware LTS were on the recorder side, apologies for the confusion.