Mariadb recorder purge foreign key errors

Configuration
1

I’m running the 0.117.3 hassio with the mariadb add-on. I was trying to execute a purge service call and it didn’t appear to work. I looked in the log and I see these errors:

2020-11-04 21:11:58 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (MySQLdb._exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`))')
[SQL: DELETE FROM events WHERE events.time_fired < %s]
[parameters: (datetime.datetime(2020, 7, 8, 7, 26, 53, tzinfo=<UTC>),)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
2020-11-04 21:11:59 WARNING (Recorder) [homeassistant.components.recorder.purge] Error purging history: (MySQLdb._exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`))')
[SQL: DELETE FROM events WHERE events.time_fired < %s]
[parameters: (datetime.datetime(2020, 7, 8, 7, 26, 53, tzinfo=<UTC>),)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

It looks like maybe it’s trying to delete from the events table, but it needs to delete from the states table first to avoid a foreign key error. I’ve successfully purged manually by deleting from the states table first.

3 Likes
2

I have the same problem did you manage to fix it ?

3

Nope, no responses yet. I might have to put in an issue.

4

Ditto - monitoring for a solution…

5

Have the same problem what leads to this problem

Any progress you know?

6

Yes, whenever a purge is executed this will happen. I delete the whole DB and recreate everytime this happens. Hoping for a solution soon.

7

I am also having the same problem…

8

Have any of you opened an issue yet?

It likely won’t get fixed unless you do.

9

Someone already opened an issue HERE. I added a comment with a link to this thread.

2 Likes
10

It’s weird that I’m not seeing this. Have you tried to manually call a purge from the developer tools services menu - with the repack option set to true?

I had to do this after the last lot of database optimisations to get the old data that was marked as deleted to actually delete and then my database reduced in size considerably.

Deleting the addon and re-adding it should have created the new table layout so I’m not confident this will help but it’s worth a try.

11

Unfortunately, deleting the setting and then reinstalling it does not help - the error remains.
If you call the service:
recorder.purge
keep_days: 2
repack: true

(in other matters, and without repacking the same thing) an error occurs:

Уровень: ERROR
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:43
Integration: Recorder (documentation, issues)
First occurred: 0:11:00 (4 occurrences)
Last logged: 11:54:04

Error executing query: (MySQLdb._exceptions.IntegrityError) (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` ))') [SQL: DELETE FROM states WHERE states.last_updated < %s] [parameters: (datetime.datetime(2020, 11, 5, 17, 21, 28, tzinfo=),)] (Background on this error at: http://sqlalche.me/e/13/gkpj)

And the base, meanwhile, is growing day by day …
Can anyone please tell for dummies step by step how to manually clear the base through Mysql?

12

Yeah I opened the issue two weeks ago but no solution so far. Although at discord Ludeeus also told me that this is an issue dating back to maybe 6 months.

Only option seems to be manually deleting so far, or if you don’t care about the data uninstalling the addon, restarting HA and reinstalling the mariadb

I hadn’t tried the repack option, although I see someone tried and didn’t change anything.

13

Very similar error here, though I’m not convinced mine is caused by the purge, it might as well be. I’ve also added my log and configs in the GitHub issue, hopefully a dev can spare the time to look at the issue.

2020-11-10 20:08:19 ERROR (Recorder) [homeassistant.components.recorder] Error saving events: (MySQLdb._exceptions.IntegrityError) (1452, ‘Cannot add or update a child row: a foreign key constraint fails (homeassistant.states, CONSTRAINT states_ibfk_2 FOREIGN KEY (old_state_id) REFERENCES states (state_id))’)

14

Hi,

good I’m not the only one. I’ve had the same few times already, can’t correlate with any specific things I’ve done just before it got broken. This time (2 days ago) it was after updating to 0.117 but I’m not sure it was after an update last 2-3 times it happened.
This time scenario was as follows:

4:12:00 AM - auto purge is triggered and it throws an error for the first time:

2020-11-10 04:12:00 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (MySQLdb._exceptions.IntegrityError) (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))’)
[SQL: DELETE FROM states WHERE states.last_updated < %s]
[parameters: (datetime.datetime(2020, 10, 27, 9, 12, 28, tzinfo=),)]
(Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)

The very next day:
04:12:00 AM - again the same error
04:12:10 AM - this error happens:

2020-11-10 04:12:10 ERROR (Recorder) [homeassistant.components.recorder] Error executing query: (MySQLdb._exceptions.IntegrityError) (1452, ‘Cannot add or update a child row: a foreign key constraint fails (homeassistant.states, CONSTRAINT states_ibfk_2 FOREIGN KEY (old_state_id) REFERENCES states (state_id))’)
[SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, old_state_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: (‘sensor’, ‘sensor.gosund_washing_machine_power’, ‘0’, ‘{“unit_of_measurement”: “W”, “friendly_name”: “Gosund - Washing Machine Power”, “icon”: “mdi:flash”}’, 1469522, datetime.datetime(2020, 11, 10, 3, 12, 9, 128404, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 10, 3, 12, 9, 128404, tzinfo=datetime.timezone.utc), datetime.datetime(2020, 11, 10, 3, 12, 9, 128422, tzinfo=datetime.timezone.utc), 618529)]
(Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)

From this moment on the same error starts to appear on regular basis (every 10 seconds since this particular sensor is refreshed every 10 seconds). It happens mainly for this specific sensor but also for few more as well. After a couple of hours logs are flooded with that error. Back in the days when I hadn’t have any notification I ran out of disk space because of the logs and then HA stopped working correctly :smiley:
After last time I’ve added a notification when log file grows over few MBs (got only errors and warnings there so they’re usually quite small) so I can prepare before it happens but still can’t prevent that from happening :frowning:

15

As a temporary workaround, I switched back to homeassistant’s default SQLITE database. (by removing the db_url from configuration.yaml)

@ipatalas For MariaDB, you can limit the log size by limiting the days in the custom.cnf file.

16

@kzaoaai It was HA log file growing indefinitely because of those errors, not MariaDB’s :slight_smile:

17

Same error here, also at 04:12 AM.

18

Adding myself to the list of foreign key error. Using external Mysql.

19

Same problem here.

20

Same here, also beginning at 4:12