'recorder/recorded_entities' is finding cruft from long-ago deleted entities about core

[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)}}

[karwosts]

I'll guess this isn't really something that needs to be addressed for the milestone, as I don't think there's anything actually wrong with the new API, it's just exposing what I feel might be an unrelated long standing issue in the recorder behavior.

[bdraco]

This isn't a regression.

Did you by chance upgrade, downgrade, upgrade without restoring an external at the point of downgrade?

[bdraco]

I'm guessing you are using SQLite so that's probably unlikely and this is a bug in the purge code.

Given how much coverage we have for this I'll need a copy of the database to be able to find it

[karwosts]

This is stock core/SQLite. It's a dev instance though so I abuse it pretty hard, changing branches and pulling new changes pretty frequently.

[bdraco]

On Jan 27/28 2023 HA was started up with an old version after the data was migrated to the new format so there are some old rows in the old format that are not being purged.

This type of issue will only happen for upgrade->downgrade->upgrade with the database not matching the version

It can be solved with
delete from states where last_updated is not null;, than wait for the next nightly purge to cleanup