Deleting sensor from MySQL/MariaDB - 2021 foreign key constraint edition


I used to be able to delete the state history of one sensor from the database, but now that there are foreign key constraints from old_state_id to state_id and event_id to the event table, this is unfortunately above my pay grade and I couldn’t find any updated example using the forum search, only old ones, that don’t work anymore.

I am sure there are people here who know SQL syntax much better than I do and have already updated their scripts to work with those new foreign key constraints. Would anybody be able to share an update script to delete sensor history?

Thank you,

Haha, yeah, I just tried to remove some bogus records myself and ran into the same problem. :confused:

we probably got some added functionality with the old_state_id reference but now we cannot remove data – seemingly without wiping the whole entity_id's record. I tried to do all datapoints in one delete query but it still failed with the foreign key error.

delete from states where entity_id='sensor.living_room_eco2_value';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (hassio_db.states, CONSTRAINT states_ibfk_2 FOREIGN KEY (old_state_id) REFERENCES states (state_id) ON DELETE NO ACTION)

That is one part of the problem, because when you solve this one, there is another FK constraint to the event_id in the event table. From what I remember about SQL, we need to delete all the affected rows in the states table AND the events table at the same time by creating and committing a SQL transaction, but unfortunately the exact way of doing that - especially in a shell script - is beyond my pay grade :slight_smile: