[Bug]: It killed my Home Assistant with over 20.000.000 entries

[Alfamonk]

What happened?

My machine was starting to act really weird (and slow). Then I noticed my mariadb backup files were getting ridiculous size increase. Doing a quick search query in phpmyadmin, I found out that this metadata_id=4166 has created over 24.042.824 entries in the states table. Searching deeper, I found out this is some sensor.ble_mac_xxxxxxxxx.

The only thing I have installed for BLE is this addon. And I have been using it only for 2 weeks or less (haven't actually used it, I just installed 5 devices and said I will add them on the dashboard later).

How do I fix those? I can't seem to find the entity anywhere and the mariadb doesn't allow me to delete those states. It's crazy to have so many entries in just 2 weeks. I usually keep my history for full 3 years on HA.

Sensor type

No response

Relevant log output

No response

[Ernst79]

The MAC address of an iBeacon rotates, unlike most other BLE devices. I have never seen a iBeacon device that rotates the Mac so fast, 24 milion is a lot. As the MAC address is not really important to know for an iBeacon, I would advise to disable the ble_mac_xxxxxxxxxx sensor. This can be done from the UI.

Can you tell me what kind of device this is?

[Alfamonk]

The MAC address of an iBeacon rotates, unlike most other BLE devices. I have never seen a iBeacon device that rotates the Mac so fast, 24 milion is a lot. As the MAC address is not really important to know for an iBeacon, I would advise to disable the ble_mac_xxxxxxxxxx sensor. This can be done from the UI.

Unfortunately, the ble_monitor just killed my VM (I use VMWare Workstation Pro). If I install it again, in 5s or so, the whole VM crashes. In years of using HA, I never had crashes like this. So, without the ble_monitor installed, I can't seem to find that entity anywhere, but in my mariadb database. I really need to know how to delete those entries... but can't find a way.

Can you tell me what kind of device this is?

I have no ideea. It just popped in my Bluetooth devices. I haven't had the time to check my Bluetooth devices. And now I can't use Bluetooth at all if I install ble_monitor, as it crashes my VM. Strange, if I use the default Bluetooth from HA, it doesn't crashes, but I still can't see those devices.

[Ernst79]

I’m don’t know how to delete states from the Maria db, but that should be possible. Can’t you use the purge service?

https://www.home-assistant.io/integrations/recorder/#service-purge

if you can get ble_monitor running, you can delete the iBeacon device from the ble monitor options menu, by selecting devices, select the uuid of the sensor, apply, select delete device, apply 2x. Also disable the option to automatically add new devices, to prevent it from adding it again.

another thing you could try is to use yaml configuration, to enforce the option to disable adding new devices, but that won’t delete the entry from your database.

[Alfamonk]

I’m don’t know how to delete states from the Maria db, but that should be possible. Can’t you use the purge service?

https://www.home-assistant.io/integrations/recorder/#service-purge

Nope, as I can't seem to find that entity in HA. There is no "ble*" entity at all (after I disabled the Bluetooth card).

if you can get ble_monitor running, you can delete the iBeacon device from the ble monitor options menu, by selecting devices, select the uuid of the sensor, apply, select delete device, apply 2x. Also disable the option to automatically add new devices, to prevent it from adding it again.

Unfortunately I can't. Everytime I connect my Bluetooth card (it's onboard on a NUC), my HA just crashes in about 30s or so.

[Ernst79]

Can’t you install ble monitor without the Bluetooth adapter? This might give you the opportunity to delete the device from the ble monitor settings.

[Alfamonk]

I have managed to delete it using the Purge service, but only in YAML mode (as in UI mode it doesn't sees the entity). It looks like this:

[Ernst79]

Ok, great. So you have your HA running again.

If you want to use BLE monitor, I would suggest to apply the settings as explained before.

• Disable the option "Discover devices and sensors automatically"
• Delete the device with UUID 50765cb7d9ea4e2199a4fa879613a492 from the devices list in the BLE monitor options (select it, submit, in the next screen, select delete device, click submit 2x)

This should prevent the entity to be recreated. Note that if you obtain new BLE sensors, they won't be added automatically anymore. You will need to add the MAC manually.