How to delete an orphaned workday binary_sensor

I created a binary_sensor with the Workday integration, by adding this to my configuration.yaml:

# Workday
  - platform: workday
    country: IT
    workdays: [mon, tue, wed, thu, fri, sat]
    excludes: [sun, holiday]

Then I removed from the configuration, restarted HA, but the device is still listed and I cannot remove it becase the button Remove Entity does not appear.
In the Lovelace card if I click the cog icon it says the entity doesn’t have an unique ID, too.

How can I remove the orphaned Workday Sensor entity if it is not listed anymore in configuration.yaml?

Nobody knows where the sensors are stored? I checked inside the home-assistant_v2.db but it is not listed… from where does Lovelace load the binary_sensor state?

You must look inside the .storage folder → core.config.entries, but be careful to what you do… i advice you to create a backup of this file before editing it.

You must look inside the .storage folder → core.config.entries

Unfortunately, in my case it wasn’t there. But I was wrong too, it was inside the database, probably I didn’t search it thoroughly the first time :neutral_face:.

In Lovelace, I clicked on the orphaned binary sensor, then on the cog icon and the message This entity (“binary_sensor.festivi”) doesn’t have an unique ID… addressed me to look for binary_sensor.festivi in the database. These were the results:

[/share/home-assistant/.storage] # grep -l "binary_sensor.festivi" `find . -type f`
[/share/home-assistant/.storage] # cd ..
[/share/home-assistant] # grep -l "binary_sensor.festivi" `find . -type f`

So the Workday add-in actually saved in the database, not in core.config.entries. To search for the “offending” tables I used:

[/share/home-assistant] # for X in $(sqlite3 home-assistant_v2.db .tables) ; do sqlite3 home-assistant_v2.db "SELECT * FROM $X;" | grep -q "binary_sensor.festivi" && echo "Found in table: '$X'" ; done
Found in table: 'events'
Found in table: 'states'

Then I did a HA backup, stopped HA, copied the home-assistant_v2.db file in my PC and used DB Browser for SQLite to open it, as it’s easier to do then from the command-line (as I didn’t know the column names).

For each of the tables, it’s possible to remove the lines with the sensor by choosing the table from the dropdown (#1), typing the sensor name in the search box (#2), selecting each of the line and hitting the delete row button (#3).

Then I clicked on the Write changes button and to really delete the data I chose Tools > Compact database, selected main and OK.


Finally I copied the database file to the HA location again, overwriting the previous one, started HA and the orphaned sensor was gone! :partying_face:

Probably a function inside the HA developer toos would have been useful, if existed!

1 Like

So you’re saying the only thing left was in the recorder database, and you manually deleted the state history? Then you could also just have waited 10 days for the history purge to do the work for you.
For orphaned entities I believe you can delete long term statistics from the developer tools, statistics tab.

1 Like

That wouldn’t keep the sensor in the system. It gets removed when it’s removed from configuration.yaml as it’s not added to the entity registry.

I called both the Recorder : Purge and Recorder : Purge Entitites services manually but they didn’t remove the entity, which was still visible from the Developer Tools - States and, of course, in Lovelace. So probably the 10 days wouldn’t have affected it?

It was already removed from my configuration.yaml which didn’t contain any further Workday binary_sensors.

Purge deletes state older than the setting (default 10 days) so it won’t purge until then. That is why I said waiting should do the trick.

Probably… but I didn’t have enough patience to wait :wink: