statistics_short_term not deleted on recorder.purge_entities

[clau-bucur]

The problem

I have my recorder keep data for 400 days, created filters to only keep the entities of interest.
But ouf of those entities, for some I only need more recent data, like the past week, or past month.

In order to accomplish this, I've created a script that is ran weekly and it purges recorder data as per my needs.
This way I have a 8 months of data in a 1.6GB states table.

What I see recently is that the statistics_short_term is getting bigger than expected, it's reached now 1.2GB.
Checking some data there I see I have old data (half a year, for example, or more) for entities which I'm constantly purging. The states table only has 7 days of data for the same entity.

What version of Home Assistant Core has the issue?

core-2023.7.1

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

recorder

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder/

Diagnostics information

I've picked a test entity to exemplify: sensor.average_illustairs_lux
Ran the below service and I've deleted everything except the past 1 day.

Then checked the states table:

SELECT COUNT(s.state_id), FROM_UNIXTIME(MIN(s.last_updated_ts)) FROM states s JOIN states_meta sm ON sm.metadata_id = s.metadata_id WHERE sm.entity_id = 'sensor.average_illustairs_lux'

The result is as expected:

Then checked statistics_short_term:

SELECT COUNT(sst.id), FROM_UNIXTIME(MIN(sst.created_ts)) FROM statistics_short_term sst JOIN statistics_meta sm ON sm.id = sst.metadata_id WHERE sm.statistic_id = 'sensor.average_illustairs_lux'

Here I have much older data:

Example YAML snippet

service: recorder.purge_entities
data:
  keep_days: 1
target:
  entity_id:
    - sensor.average_illustairs_lux

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.

_{^{(message by CodeOwnersMention)}}

---

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

[clau-bucur]

Nobody able to take a look at this?

[clau-bucur]

@balloob and @emontnemery as per your comments on #64383 the statistics_short_term table should be purged together with purging the entities.

However this is not the case in my above scenario.
Could it be that purging is excluding statistics_short_term in some special cases or what is happening here?

[GoNzCiD]

I have the same issue, after call the purge_entities service, old entries from states has been removed but not from statistics_short_term. I haven't checked the events....

[clau-bucur]

I have long gave up on being fixed in the recorder component and I have written a clean-up script which uses the https://github.com/qrioniclabs/homeassistant-mysql_command component with SQL scripts to remove short-term stats and non-existing statistics-meta.

Here's an example:

script:
  system_recorder_cleanup:
    sequence:

      # Remove entities states older than 7 days
      - service: recorder.purge_entities
        data:
          entity_globs:
            - sensor.zigbee_climate_*_position
            - sensor.zigbee_*_battery
            - sensor.average_battery_*
            - binary_sensor.illustairs_s*
            - sensor.zigbee_motion_*_illuminance_lux
            - sensor.average_paradox_system_power_*
            - sensor.austral_battery_level*
            - sensor.austral_*_battery_level
          keep_days: 7
        target:
          entity_id:
            - sensor.average_illustairs_lux
            - sensor.ws_battery
            - sensor.ws_rssi

      # Remove short-term statistics older than 7 days
      - service: notify.mysql_command
        data:
          message: >
            DELETE sst FROM statistics_short_term sst INNER JOIN statistics_meta sm ON sm.id = sst.metadata_id WHERE DATEDIFF(CURDATE(), FROM_UNIXTIME(sst.created_ts)) > %s
            AND
            (
              sm.statistic_id LIKE %s
              OR sm.statistic_id LIKE %s
              OR sm.statistic_id LIKE %s
              OR sm.statistic_id LIKE %s
              OR sm.statistic_id LIKE %s
              OR sm.statistic_id LIKE %s
              OR sm.statistic_id LIKE %s
              OR sm.statistic_id LIKE %s
              OR sm.statistic_id IN (%s, %s, %s)
            )
          target:
            [
              7,
              'sensor.zigbee_climate_%_position',
              'sensor.zigbee_%_battery',
              'sensor.average_battery_%',
              'sensor.zigbee_motion_%_illuminance_lux',
              'binary_sensor.illustairs_s%',
              'sensor.average_paradox_system_power_%',
              'sensor.austral_battery_level%',
              'sensor.austral_%_battery_level',
              'sensor.average_illustairs_lux',
              'sensor.ws_battery',
              'sensor.ws_rssi',
            ]

      # Remove short-term statistics for entities that no longer exist
      - service: notify.mysql_command
        data:
          message: >
            DELETE FROM statistics_short_term WHERE metadata_id IN
            (
              SELECT
                statistics.id
              FROM
                statistics_meta statistics
                LEFT JOIN states_meta states ON states.entity_id = statistics.statistic_id
              WHERE
                states.entity_id IS NULL
            )
      
      # Remove long-term statistics for entities that no longer exist
      - service: notify.mysql_command
        data:
          message: >
            DELETE FROM statistics WHERE metadata_id IN
            (
              SELECT
                statistics.id
              FROM
                statistics_meta statistics
                LEFT JOIN states_meta states ON states.entity_id = statistics.statistic_id
              WHERE
                states.entity_id IS NULL
            )

      # Remove statistics-meta for entities that no longer exist
      - service: notify.mysql_command
        data:
          message: >
            DELETE FROM statistics_meta WHERE id IN
            (
              SELECT
                statistics.id
              FROM
                statistics_meta statistics
                LEFT JOIN states_meta states ON states.entity_id = statistics.statistic_id
              WHERE
                states.entity_id IS NULL
            )

      # Remove statistics-meta for ids which do not have short-term or long-term statistics
      - service: notify.mysql_command
        data:
          message: >
            DELETE sm
              FROM statistics_meta sm
              LEFT JOIN statistics_short_term sst ON sm.id = sst.metadata_id
              LEFT JOIN statistics s ON sm.id = s.metadata_id
            WHERE
              sst.id IS NULL AND s.id IS NULL

      # Compact the database
      - service: recorder.purge
        data:
          repack: true
          apply_filter: true

BTW: you need qrioniclabs/homeassistant-mysql_command#9 for the custom component in order to use the target parameter.

[GoNzCiD]

tes_meta states ON states.entity_id = st

Thanks @clau-bucur, I've started using the purge_entities service, and I've pending to see how to delete sort term stats... That's my idea, equivalent to yours but, is it correct to use the condition states.entity_id is null? In my case is always null, it looks like an old scheme, and now it's pushed into the attributes table.

[clau-bucur]

Correct, the column entity_id from table states is null for me too.
But, in my statement, the states is an alias for states_meta table, so it's not actually refering to the states table, sorry for the confusion.

The same script would look like:

DELETE FROM statistics_short_term WHERE metadata_id IN
(
  SELECT
    statistics_meta.id
  FROM
    statistics_meta
    LEFT JOIN states_meta ON states_meta.entity_id = statistics_meta.statistic_id
  WHERE
    states_meta.entity_id IS NULL
)

Also the rest of the scripts follow the same logic.

[issue-triage-workflows]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.
Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍
This issue has now been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.