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?
Haha, yeah, I just tried to remove some bogus records myself and ran into the same problem.
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.
Input: delete from states where entity_id='sensor.living_room_eco2_value';
Output: 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
If you want to delete bogus rows from the table, set the parent row’s old_state_id to NULL before trying to delete it. This prevents the foreign key constraint from kicking in.
