Deleting redundant states rows from MariaDB?

Configuration
1

I have a tasmota based power meter on our hot water cylinder. I like to see instantaneous power usage so I have tasmota reporting telemetry every 10 seconds. This in turns creates a LOT of state records in the database and for things like “energy today” there is a lot of repetition where the current state is the same as the old state.

I’d like to see if it’s possible for me to delete all these “duplicate value” records so that my database doesn’t grow so large.

As a test I wrote this DELETE query which will delete all states where the current state is the same as the previous state.

DELETE S
FROM states S
LEFT OUTER JOIN states S2 ON S2.state_id = S.old_state_id
WHERE S.entity_id = 'sensor.powr320d01_energy_today'
AND S.state = S2.State

There are some database constraints that are stopping me from doing this…

SQL 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`))

Can anyone guide me on the best way to remove these records without breaking things completely? Possibly I could turn off the constraints just while I do this delete?

My plan is to manually run these kind of scripts every now and then but maybe there is a more accepted way of reducing or removing what I feel is redundant data?

1 Like
2

Each new record of an entity have a link on the previous record of the same entity by the fiels ‘old_state_id’. 2 solutions.

  • Nullify this field prior to trying to delete
  • Delete starting by the newest one :
DELETE FROM `states` WHERE `entity_id` LIKE ....... ORDER BY `last_updated` DESC