ASUSWRT: DB flooded by records with same state every 30 sec

[ildar170975]

The problem

According to Dev tools -> state, a device_tracker entity contains these attributes:

Let's open DB and filter a "states" table by a corr. "metadata_id":

The "states" table stores records every 30 sec:

So, there was no need to exclude last_time_reachable from DB - it does not help, DB is still flooded.

What version of Home Assistant Core has the issue?

2024.2.3

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant Container

Integration causing the issue

ASUSWRT

Link to integration documentation on our website

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

Diagnostics information

No response

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 @kennedyshead, @ollo69, mind taking a look at this issue as it has been labeled with an integration (asuswrt) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of asuswrt 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 asuswrt 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)}}

---

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

[ollo69]

I would expect that because this attribute is excluded from recorder defining the _unrecorded_attributes as explained here, the information in the recorder would not be updated.

I will make some internal test to understand if totally removing the attribute will stop generating data, in case I will create a new PR for this,

[ildar170975]

In a "normal" scenario when some attribute is stored in DB:

1. An attribute is changed.
2. Last-updated is changed.
3. A new record in "states" is created (also may be a new record is created in "states_attributes" as well - dependently on a current value of this attribute).

If this attribute is NOT stored in DB - I would expect to not have a new record in "states" due to a changed "last-updated".
But I am not an expert in DB.

[ollo69]

I agree with you, but seems that the behavior is different. Probably just because the attribute change also Last-update change and so new value is saved.
The only thing that I can do is try to remove the attribute and see if recorder stop saving data every 30 sec, in case remove the attribute.
The alternative that I can suggest is to open an issue not related to this integration but to the the recorder and link this issue as reference

[ildar170975]

Probably just because the attribute change also Last-update change and so new value is saved.

When an attribute changes (even a non-recorded) - the "last-updated" is changed. Axiom.
If last-update changes -> a new record is added.
I wonder if it is possible on the Recorder side to track these "last-update" changes and NOT write a record...
Seems that currently excluding a CHANGING attribute anyway make Recorder to write a new record on every change of this "non-recorded" attribute.
I will try to create an issue for Recorder.

in case remove the attribute.

This could be a breaking change for some users... People may want to have this attribute.

[ildar170975]

Created an issue for Recorder.
I wonder if it has a feedback...

[ollo69]

I think you should set "Integration causing the issue" field or the issue will stay generic and maybe no one will take it in charge.

[ildar170975]

You are right. Fixed that issue, associated it to Recorder explicitly.
Thanks ones again for support & efforts!

[ildar170975]

Hmmm, I added a link to a integration - but seems that is not processed.
Are there any magic commands to do it?

[ollo69]

I suggest to close and open a new one. Don't really know how does it works, but I suspect that it associate the integration only when you create the issue.

[ildar170975]

One kind developer helped me )))

[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.

[ildar170975]

2024.5.3
still there

DB is still flooded with same records.

[bdraco]

I closed the recorder issue because its not a recorder problem. The recorder will always write to the database when an entity is updated and the state or attributes changes. If last_updated changes you'll get a row in the database even if the attributes are excluded, but you won't get a new attributes row. It would be a significant breaking change to do otherwise.

If you don't want a database row, the solution is to avoid calling async_write_ha_state when nothing significant has changed

mqtt does something like that

core/homeassistant/components/mqtt/mixins.py

Line 1292 in a5dc4cb

if attributes is not None:

That technique could be extended to exclude insignificant changes. That's probably a breaking change for this integration though as well.

Implementing it the way the op is asking in either issue will likely result in a breaking change. Probably less pitchforks if its done here. Its perfectly reasonable to close this issue as you might make some other people quite unhappy that relied on last_updated changing frequently. It would also be ok to implement it if you think the fallout will be minimal and its a desirable change for the integration.

[ildar170975]

I am may be missing smth, please correct me then.

1. When an entity is changed (state or attrs) - the last_updated is changed. How a user can see that the integration is WORKING, not hanging? By looking at that last_updated: if it changes - means "smth going on", the entity is updated.
2. Based on "If last_updated changes you'll get a row in the database": if last_updated changes -> a new row will be added to the table; you cannot keep updating the last_updated property & NOT storing these rows in DB.

From one side - I need to have last_updated changed to see that the integration is working.
From another side - I do not want DB flooded by same rows.
But the 1st point is much more crucial.
Due to said above - surely I will accept the current fact "same rows with same state & attrs are created".
Closing.