20760327 states entries created by one entity in just 2 weeks!

[Alfamonk (Daniel M)]

Hi people!

Sorry to bother you with my problem, but… it’s getting really bad in here. Long story short: since 2 weeks ago my HA machine was starting to act really weird (and slow). Then I noticed my mariadb files are getting a ridiculous size increase. Then my VM just crashed (never had that in years of HA use) and I wasn’t able to start it unless I disabled my Bluetooth device (it’s an Intel NUC, with Intel WiFi/Bluetooth Adapter).

And then, when I look in my phpmyadmin, there are entities like this “sensor.ble_mac_50765cb7d9ea4e2199a4fa879613a492” that have more than 20.000.000 entries. Again, in just 2 weeks! I have tried to run the purge service for those entities, but because now my Bluetooth is disabled, I can’t seem to find those entities anywhere, but mariadb/phpmyadmin.

Since I can’t find the entity anywhere, I have tried to delete from phpmyadmin. The command was like this:

delete from states where metadata_id=4166;

But then, after 300s, I get some error “database connection lost”. So I have tried using LIMIT, like this:

delete from states where metadata_id=4166 LIMIT 100;

In response, I get this error:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (homeassistant.states, CONSTRAINT states_ibfk_2 FOREIGN KEY (old_state_id) REFERENCES states (state_id))

Now, I’m out of ideeas. How can I cleanup my database, by deleting only those states with over 20.000.000 entries?

Thank you in advance!

[Edwin_D]

Ouch. Step one would be to exclude sensor.ble_* from the recorder as a workaround.

[boheme61]

Right and then enable the integration, and Purge entities ALL(keep 0 days) for either sensor.ble_mac_5067*****, or for all sensor.ble* … hit purge
then in service Purge, choose Repack … Purge

[Alfamonk (Daniel M)]

I already did that, but it doesn’t help too much, as I usually keep 3 years of history data.

[Alfamonk (Daniel M)]

If I enable the integration but don’t have the Bluetooth device connected (as I’ve said, it crashes my HA), I can’t find those entities anywhere in HA. So Purge will not work.

[boheme61]

@Alfamonk

, I can’t find those entities anywhere in HA

Have you looked in /Settings/Devices#entities , and checked "show hidden/Disabled ?
Anyhow, the “entries” are in your DB, first perform a Select, to find the largest entities, and there are also a few other Selects you can run to find individual entity_id’s
Depending upon which Table(and entries) you want to clean, you ofcause also need the metadata_id
There are several Topic’s in here, with several select/delete-queries examples, othervice it’s the Maria-DB docs or Google, who is your best chance.

As i don’t have Maria-DB, i can’t suggest how you approach , but it should be faily similar (select statements) as if you used the sqlite-web-integration

Cleaning up HA Database (with some old_state_id problems) - #3 by epankala.

PS: This one you should NOT run !!!
DELETE FROM states where state like ‘%0%’

However it might give you an idea(example upon how it works) You could replace 0 with i.e a “specific” value or a string
Here is a basic , starter

SELECT
  SUM(pgsize) bytes,
  name
FROM dbstat
GROUP BY name

a more appropriate way "Knowing the metadata_id " , there is a Select for that as-well.

SELECT * FROM states where metadata_id = 11,  but you might have huge amounts of row, so you need "another factor"
Single row
Delete FROM states where state_id = 17

And If your DB is up to it

SELECT * FROM states
INNER JOIN states_meta ON states.metadata_id=states_meta.metadata_id
GROUP BY states_meta.entity_id

And you will get an overview over varies “columns” from states/metadata

As you know(and dont seems to find a way to Purge)

Beside DELETE * , You can DELETE individual “states” ( It is after all a state-engine-db )
You can also “add” last_changed as option ( i.e * from table where last_change “larger/older than” ) ( Like “Keep Days in Purge” )

Big DELETEs - MariaDB Knowledge Base.

[Alfamonk (Daniel M)]

Thank you, I will do some more research over the delete for mariadb. It would’ve been great if the Prune service could find those devices. And yes, I have checked all unused and disabled entities. It’s very strange I can’t see any of them… I even filtered by ble_mac and there is nothing.

[boheme61]

@Alfamonk

It would’ve been great if the Prune service could find those devices

Have you tried, with integration Disabled ? ( I doubt that Purge cares about the integration, and the entities , but can be wrong)

Disabled Integration, then Service “Purge Entities”

Then Service “Purge”

Another “idea” is to remove the integration, clear all, and install it again( After you been able to Purge/DELETE ( Not sure why you need 3 years of “BLE” data )

[Alfamonk (Daniel M)]

I have tried, but it can’t find those entities. That’s what’s really strange. And those entities are in the database, that’s for sure! This is what I get:

Oh no, I don’t need to keep 3 years of “BLE” data. I was talking about my other sensors. The BLE I have already added in the “exclude” section of the configuration file.

[boheme61]

Try to Choose 1 “random” entity, to the right (Choose Entity) and try again ( with the glob still there )

[Edwin_D]

Or in yaml mode it should be possible to enter the actual entity ID, even if ha doesnt recognise it?

[boheme61]

Yes, apparently it wont “fire” And trow an Error, if one only tick “glob” , it needs an entity( as target ) also … ( Could be considered as a Bug )
A test showed that it does execute the purge on the Glob, if i have 1 random other sensor(not same “glob”) chosen as target. ( Would most likely also execute, if this entity also was “included” in the Glob )

[Alfamonk (Daniel M)]

I have selected an entity and used the globs to enter the “sensor.ble_*”. It purged the selected entity but didn’t do anything to the sensor.ble_ ones. It’s really annoying to not be able to remove those huge entries in the database.

[Alfamonk (Daniel M)]

THIS! This actually worked! You rock! Thanks a lot, man! You saved the day!

P.S. It doesn’t work for globs like “sensor.ble_mac_*” - but I am happy to at least be able to solve them one by one. Phew!

[Edwin_D]

It is likely entities allows a list, so you maybe able to do it once with all entities named explicitly. I think the glob does not work because it tries to expand to a list but does not know the entity is there.

[boheme61]

@Edwin_D

I think the glob does not work

Yes, maybe, it does seems like Glob does not work as a target, so it might do some “initial” processing( outside the DB , before executing the DELETE statement