Mariadb recorder purge foreign key errors

drthanwho · November 21, 2020, 12:39am

4:12am is normal. That is the default HA purge being triggered. It happens every night.

elbarsal · November 25, 2020, 7:28pm

This is also happening with PostgreSQL. I dropped the foreign key that is failing and manually deleted the records, since there were a few months worth of records built up.

Dropping the foreign key won’t affect performance but could impact database integrity.

koenhaemels · November 27, 2020, 10:12pm

They have provided a possible solution for the problem in the issue earlier mentioned by @brucehvn HERE :

The database has purge!
I Have Hass.IO on Raspberry witn MariaDB add-on.

mysql -u <user_name> -p homeassistant
MariaDB [homeassistant]> SHOW CREATE TABLE states\G
*************************** 1. row ***************************
Table: states
Create Table: CREATE TABLE states (
state_id int(11) NOT NULL AUTO_INCREMENT,
domain varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
entity_id varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
state varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
attributes text COLLATE utf8mb4_unicode_ci 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_last_updated ( last_updated ),
KEY ix_states_entity_id_last_updated ( entity_id , last_updated ),
KEY ix_states_event_id ( event_id ),
CONSTRAINT states_ibfk_1 FOREIGN KEY ( event_id ) REFERENCES events ( event_id ),
CONSTRAINT states_ibfk_2' FOREIGN KEY ( old_state_id ) REFERENCES states ( state_id`)
) ENGINE=InnoDB AUTO_INCREMENT=553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

After the keyword “Constraint” the ngas have several keys. After that, we make the commands:

MariaDB [homeassistant]> ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;
MariaDB [homeassistant]> ALTER TABLE states
→ ADD CONSTRAINT states_ibfk_1
→ FOREIGN KEY (event_id)
→ REFERENCES events (event_id)
→ ON DELETE SET NULL;
MariaDB [homeassistant]> ALTER TABLE states DROP FOREIGN KEY states_ibfk_2;
MariaDB [homeassistant]> ALTER TABLE states
→ ADD CONSTRAINT states_ibfk_2
→ FOREIGN KEY (old_state_id)
→ REFERENCES states (state_id)
→ ON DELETE SET NULL;
MariaDB [homeassistant]> CREATE INDEX ix_states_old_state_id
→ ON states (old_state_id);

after that, I called the recorder.purge service in НА and the data base was compressed!

brucehvn · November 28, 2020, 12:04am

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.

dapuzz · November 28, 2020, 3:34pm

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

koenhaemels · November 29, 2020, 1:56pm

Did you tried my latest reply?

eddyg · November 29, 2020, 3:53pm

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

duceduc · November 29, 2020, 8:40pm

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

eddyg · November 29, 2020, 10:43pm

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?

juzel · November 30, 2020, 9:05am

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.

Shaad · December 2, 2020, 9:19am

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

slackr31337 · December 8, 2020, 2:47pm

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

GaryK · December 8, 2020, 6:39pm

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

not_a_coder · December 9, 2020, 11:40am

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 ?

pollinolas · December 9, 2020, 12:43pm

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

not_a_coder · December 9, 2020, 1:11pm

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.

pollinolas · December 9, 2020, 4:49pm

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

dapuzz · December 14, 2020, 8:30am

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

Purge is back again

MandM78 · December 14, 2020, 9:55pm

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

Yay! Purge is working again!

Shaad · December 15, 2020, 10:08am

+1 Seems working fine after the upgrade.