Recorder + database schema migration fails

Installation
1

Did recently the upgrade to 2024.8.2 and now my Recorder + Database do fail.

Errors:

psycopg2.errors.ForeignKeyViolation: insert or update on table "states" violates foreign key constraint "states_attributes_id_fkey"
DETAIL:  Key (attributes_id)=(7996072) is not present in table "state_attributes".

and:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "states" violates foreign key constraint "states_attributes_id_fkey"
DETAIL:  Key (attributes_id)=(7996072) is not present in table "state_attributes".

[SQL: ALTER TABLE states ADD FOREIGN KEY(attributes_id) REFERENCES state_attributes (attributes_id)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Is there anyway to fix/upgrade the database to most recent level?

Regards, Arthur

2

hello.
I have the same problems. and open a issue.

are you database big??
the mine is more than 30gb and i think this is the problem

this is my issue:
Database migration errors in 2024.8.2 · Issue #124186 · home-assistant/core (github.com)

3

Same: Database migration fails (2024.8.2) `no unique constraint matching given keys for referenced table "event_data"` · Issue #124135 · home-assistant/core · GitHub

I don’t think it should be size related - seems to me that the migrations are expecting the schema to be in a particular state but it is not…

4

Mine is indeed 12Gb large, however the error comes very quickly.
It’s a key which is expected to be available and missing. Happy on one side that this situation is not unique to my setup.

Thanks for sharing the issue

5

in my case, some errors apears quickly. but after 7 hours apears (i think) the main error, a timeout from home assistant to mariadb :frowning: