core: MariaDB doesn't purge

@pbarvinko Thank you much for the link and reply. https://dev.to/mcgurkadam/how-to-change-a-foreign-key-constraint-in-mysql-1cma

I’ve used @Mikle61 queries above to patch fix the error. Now the wait time to see if the error comes back.

Note: Running the below query in phpmyadmin console will come back error. You can only run this in CLI to verify the foreign key constraint name. Mine just so happen to be the same as Mikle61 above.

SHOW CREATE TABLE states\G

After I found out the name(s), I went back to phpmyadmin console and paste in these blocks and hit go after each query. The connection via the CLI was flaky. That was the reason why I use phpmyadmin instead.

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);

The last step was to manually purge in HA. Developer Tool -> Service

recorder.purge

I have the same purge issue as described above and I started to try some root cause analysis.

The first thing I did was checking the table creation of the ‘states’ table with and older version (0.115.1) and a more recent version (0.117.0). Here is what I got:

0115.1:

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 `ix_states_event_id` (`event_id`),
  KEY `ix_states_last_updated` (`last_updated`),
  KEY `ix_states_entity_id_last_updated` (`entity_id`,`last_updated`),
  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

0.117.0 :

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_event_id` (`event_id`),
  KEY `ix_states_last_updated` (`last_updated`),
  KEY `ix_states_entity_id_last_updated` (`entity_id`,`last_updated`),
  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=245984 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

As one can see from the two above there was something changed between the 2 versions -> there was a relationship added to the ‘old_state_id’ within the table itself (second row starting with ‘CONSTRAINT’).

I looked into the code of models.py which I found here : https://github.com/home-assistant/core/tree/dev/homeassistant/components/recorder

in the diffs of the source code : https://github.com/home-assistant/core/commit/d9ba32dc3fe5be8f8e242123a4109a98ce751446#diff-ecf535aee3c11066659a4a0a5f493ace168a8ee82372be63098a1b06896a4142

I found that the change was related to #40467 where it’s explained why this change was made.

To me it looks like the negative impact on purge was overlooked. May be it would help to expand the table creation in models.py by ‘ON DELETE SET NULL’ or apropriate.

Just my 5ct

I started a thread in the forums here with a similar problem. With the Mariadb add-on purge causes foreign key errors (logs are in the forum thread and below). It appears to be trying to execute the delete from the EVENTS table before doing it from STATES. I can do the purge manually from the MySQL CLI client by doing the two deletes, STATES, then EVENTS. Several people in the thread are having the same issue.

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)

@duceduc Ah, sorry, my fault. I assumed that mysql syntax for constraints manipulation is the same in postgres and mysql. It is different. Here is a nice article that shows the required mysql commands: https://dev.to/mcgurkadam/how-to-change-a-foreign-key-constraint-in-mysql-1cma

And you still need step 4 to add index on old_state_id.

Thanks for the link!

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!

Just to be clear: the current design of the purge process in home assistant when using postgres/mysql/mariadb physically cannot work. It’s not a fault of your particular installation. It has a design flaw, which tries to delete rows that are protected by foreign key constraint. Currently the only way to resolve it is to modify the database structure as described in the post above.

For users with mysql/mariadb, the syntax of command to change/add constraints is a bit different and described here. You also still need to do also step 4.

@fubar-coder Good one. I altered the constraints for both event_id and old_state_id. It resulted in (finally!) working purge. For the record and for the others - what I have done:

1. Checked the confdeltype of the currently available constraints:

SELECT con.conname, con.confupdtype, confdeltype
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = 'public'
             AND rel.relname = 'states';

It was set to ‘a’ (meaning “no action”) for both of them. 2. Executed

ALTER TABLE states DROP CONSTRAINT states_old_state_id_fkey;

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

ALTER TABLE states DROP CONSTRAINT states_event_id_fkey;

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

to set the constraints to “on delete set null” 3. Verified with the pg_constraint query above the results. confdeltype is now set to ‘n’, meaning set to null.

1. Created index on old_state_id column, which has speeded up the purge procedure factor 100x

CREATE INDEX ix_states_old_state_id
ON states (old_state_id);

The steps above are largely specific to Postgres. For those, who use MySQL/MariaDB, please check the following post for the solution.

@duceduc Ah, sorry, my fault. I assumed that mysql syntax for constraints manipulation is the same in postgres and mysql. It is different. Here is a nice article that shows the required mysql commands: https://dev.to/mcgurkadam/how-to-change-a-foreign-key-constraint-in-mysql-1cma

And you still need step 4 to add index on old_state_id.

@tobbensoft For now, I suggest that you modify the database as described by @pbarvinko in this comment.

Hi, I have the same problem with MariaDB:

arch armv7l chassis dev false docker true docker_version 19.03.13 hassio true host_os Raspbian GNU/Linux 10 (buster) installation_type Home Assistant Supervised os_name Linux os_version 5.4.72-v7l+ python_version 3.8.6 supervisor 2020.11.0 timezone Europe/Moscow version 0.117.5 virtualenv false

Уровень: 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=<UTC>),)] (Background on this error at: http://sqlalche.me/e/13/gkpj)

MariaDB Configuration

databases:

• homeassistant logins:
• username: hass host: ‘%’ password: hass_password rights:
• username: hass host: ‘%’ database: homeassistant grant: ALL PRIVILEGES ON

i tried to apply solution from fubar-coder. Installed the program MySQL Workbench, connected to the database and tried to execute the query. However, it turned out that the user did not have enough privileges to execute the select command:

Error Code: 1142. SELECT command denied to user ‘hass’@‘192.168.1.47’ for table ‘pg_constraint’

rights are not enough even to view information about the database:

The current user does not have enough privileges to execute SELECT ‘<global>’ as Db,Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Create_tmp_table_priv,Lock_tables_priv,Create_view_priv,Create_routine_priv,Alter_routine_priv,Execute_priv,Event_priv,Trigger_priv FROM mysql.user WHERE Select_priv = ‘Y’ OR Insert_priv = ‘Y’ OR Update_priv = ‘Y’ OR Delete_priv = ‘Y’ OR Create_priv = ‘Y’ OR Drop_priv = ‘Y’ OR Grant_priv = ‘Y’ OR References_priv = ‘Y’ OR Index_priv = ‘Y’ OR Alter_priv = ‘Y’ OR Create_tmp_table_priv = ‘Y’ OR Lock_tables_priv = ‘Y’ OR Create_view_priv = ‘Y’ OR Create_routine_priv = ‘Y’ OR Alter_routine_priv = ‘Y’ OR Execute_priv = ‘Y’ OR Event_priv = ‘Y’ OR Trigger_priv = ‘Y’.

SELECT command denied to user ‘hass’@‘192.168.1.47’ for table ‘user’

Unfortunately I have no experience with databases. I ask you to suggest how to fix the lack of necessary privileges to fulfill the previously described requests?

@fubar-coder I have updated my post above to add index on old_state_id column. Now (thanks to your advise) purge works properly. In the essence two things have to be changed/added to the current purge process:

• Adding “ON DELETE SET NULL” to the states_old_state_id_fkey and states_event_id_fkey constraints
• Adding index on old_state_id column

Does anyone know who from the developers shall be tagged here?..