Mariadb recorder purge foreign key errors

Configuration
24

Well, I had no problem doing manual purges without any database changes by issuing the two following commands in order (I keep 90 days worth of data in my database):

delete from states where created < DATE_ADD("2020-11-27 08:00", INTERVAL -90 DAY);

delete from events where created < DATE_ADD("2020-11-27 08:00", INTERVAL -92 DAY);

I hadn’t done that manual purge since Nov 4, which I think was when I posted this discussion. I went to do the purge today and the data from +90 days ago was already gone, so it appears the automatic purging is working now. I’m running HA 0.118.3 currently.

1 Like
25 
Error saving events: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`hass_db`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: INSERT INTO states ....

Home assistant core 0.118.3
Any solution here?


Here the pull request. Hope they can merge asap
26

Did you tried my latest reply?

27

I’m running 0.117.6 and noticed this morning I had no history data any more. I checked the logs, and it’s full of messages like:

MySQLdb._exceptions.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`hass`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))')

I tried executing the commands, and the first set worked, but not the second:

ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;
ALTER TABLE states ADD CONSTRAINT states_ibfk_1 FOREIGN KEY (event_id) REFERENCES events (event_id) ON DELETE SET NULL;
ALTER TABLE states DROP FOREIGN KEY states_ibfk_2;
ALTER TABLE states ADD CONSTRAINT states_ibfk_2 FOREIGN KEY (old_state_id) REFERENCES states (state_id) ON DELETE SET NULL;

I get:

Cannot add or update a child row: a foreign key constraint fails (`hass`.`#sql-241_2dd78`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`) ON DELETE SET NULL)

For ref, when I do a SHOW CREATE TABLE states, I get:

CREATE TABLE `states` (
 `state_id` int(11) NOT NULL AUTO_INCREMENT,
 `domain` varchar(64) DEFAULT NULL,
 `entity_id` varchar(255) DEFAULT NULL,
 `state` varchar(255) DEFAULT NULL,
 `attributes` text DEFAULT NULL,
 `event_id` int(11) DEFAULT NULL,
 `last_changed` datetime DEFAULT NULL,
 `last_updated` datetime DEFAULT NULL,
 `created` datetime DEFAULT NULL,
 `old_state_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`state_id`),
 KEY `old_state_id` (`old_state_id`),
 KEY `ix_states_entity_id_last_updated` (`entity_id`,`last_updated`),
 KEY `ix_states_event_id` (`event_id`),
 KEY `ix_states_last_updated` (`last_updated`),
 CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=324643 DEFAULT CHARSET=utf8
28

I tried executing the commands, and the first set worked, but not the second:

From your Show Table, it only states states_ibfk_1 there is no states_ibfk_2

29

Isn’t that because I already executed ALTER TABLE states DROP FOREIGN KEY states_ibfk_2; and the command that would add back states_ibfk_2 is failing?

30

The problem is the foreign key states_ibfk_2. This ist set for the column old_state_id, wich refereces to state_id. So you can’t delete a row, with state_id stored in old_state_id. I don’t know, why they do that. But if you delete the foreign key states_ibfk_2, then it will run well.

31

Still this issue, and more : history stops functionning almost every night. I restart Home Assistant and recording functionnality is back.

1 Like
32

I have the same problem. MySQL recorder stops working every night. Testing this patch now.

33

I’m running MariaDB. Will this self-correct at the next release?

34

I have the same error. Running MariaDB on local SSD on RPi4. Where do I go to be able to use CLI in order to send the following command?

SHOW CREATE TABLE states\G

Is that actually what I am supposed to do? Is the correct procedure for my setup the one at https://github.com/home-assistant/core/issues/42402#issuecomment-729327643 ?

35

If you are using home assistant you can install the phpMyAdmin addon, once inside select your homeassistant database, in the sql tab copy the following:

ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;

ALTER TABLE states
ADD CONSTRAINT states_ibfk_1
FOREIGN KEY (event_id)
REFERENCES events (event_id)
ON DELETE SET NULL;

ALTER TABLE states DROP FOREIGN KEY states_ibfk_2;

ALTER TABLE states
ADD CONSTRAINT states_ibfk_2
FOREIGN KEY (old_state_id)
REFERENCES states (state_id)
ON DELETE SET NULL;

CREATE INDEX ix_states_old_state_id
ON states (old_state_id);

and press continue
Once the processes are finished, go to your home assistant and run a manual purge from the services tab

36

I might have made a mistake after my post and prior to reading your post. I already had the phpMyAdmin addon and I sent two individual commands thinking maybe the others were supposed to be responses.

I pasted

ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;

then clicked Go

then I pasted

ALTER TABLE states DROP FOREIGN KEY states_ibfk_2;

Then I clicked Go
Then I went to Developer Tool -> Service and used recorder.purge

Now when I go back to phpMyAdmin I get error code: 524 when I send

ALTER TABLE states
ADD CONSTRAINT states_ibfk_1
FOREIGN KEY (event_id)
REFERENCES events (event_id)
ON DELETE SET NULL;

Is there something I need to do to correct what I did wrong?

edit: I gest there error code: 524 was because it wasn’t getting a response at all. I kept trying and now I get

 #1005 - Can't create table `homeassistant`.`states` (errno: 121 "Duplicate key on write or update") (Details…)

then I click on Details and it says it already exists. So now I wonder if the command went through once before but I never saw the response.

37

I’m sorry, I wouldn’t know what to tell you. In fact, I thought I had fixed mine, but I just realized that it’s failing too.
I guess there will be waiting for an update of the addon to come out
EDIT:
It seems to be working, it only gives an error when doing a manual purge, with the automatic purge everything seems to work correctly

38

Upgraded to 2020.12.0
WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 9

Purge is back again :slight_smile:

3 Likes
39

I updated to 2020.12 this morning and watched my DB go from 1.3Gb to 300mb!

Yay! Purge is working again!

1 Like
40

+1 Seems working fine after the upgrade.

41

Agreed sounds like the problem is gone.

Though I did run into a few issues during the upgrade since I was previously running version 0.117 on a venv

Had an issue with: hass-nabucasa, see: Upgrade to 0.118 error

Fixed it by reverting to my backup and by first running:

pip install --upgrade hass-nabucasa

Second issue was the following:

Failed config
  homeassistant.packages.media_player.roku:
    - Package media_player setup failed. Component roku cannot be merged. Expected a dict.
    - automation: [source /home/homeassistant/.homeassistant/packages/media_player.yaml:134]
        - action: [source /home/homeassistant/.homeassistant/packages/media_player.yaml:140]

Changed the configuration:

roku:
  - host: 192.168.4.135
for
roku:
  host: 192.168.4.135

Got the following message:

WARNING:homeassistant.components.roku:The 'roku' option is deprecated, please remove it from your configuration

Ok, I get it … I guess it is time to remove that configuration. So overall all is good.

42

How this this purge thing works? I just updated from 0.117 to 2020.12.1 and to OS5.8. When does HA checks purge_keep_days:?

recorder:
  db_url: !secret MySQL_login
  purge_keep_days: 25
  exclude:
    domains:
     - group
     - automation
     - camera
     - media_player
     - scene
     - script
    entities:
     - sun.sun
     - sensor.date_time
43

Everyday at 04:12AM.