Database upgrade to v45 constantly creating new constraints and never finishes (MariaDB)

[mackowskim]

The problem

New DB upgrade brought new issues on the database level.

Home Assistant upgrade process creates the same constraints repetively on the 'states' and 'events' tables, therefore the upgrade process never finishes.

My database is already 70GB of size and indexes itself takes over 40GB.

Steps to reproduce on a large database:

1. Start Home Assistant
2. DB upgrade starts
3. On MariaDB side new foreign keys are being created

(old_state_id and attributes_id takes over 10h to finish for each one)

4. During the time of creation it seems like the new process kicks in and creates the same FKs again:
5. Database ends up with several iterations of the same FK and the upgrade process never finishes.

Is there any check if the FK already exits? Is there any timeout set when the prosess tries to recreate the same FK again?

I'm fighting with this upgrade process for 2 days already and no luck.

What version of Home Assistant Core has the issue?

core-2024.8.2

What was the last working version of Home Assistant Core?

core-2024.8.1

What type of installation are you running?

Home Assistant OS

Integration causing the issue

Recorder

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder/

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

Rejestrator: homeassistant.components.recorder.migration
Źródło: components/recorder/migration.py:717
integracja: Recorder (dokumentacja, Problemy)
Pierwsze zdarzenie: 10:31:59 (3 zdarzenia)
Ostatnio zalogowany: 10:35:35

Adding foreign key constraint to events.data_id. Note: this can take several minutes on large databases and slow machines. Please be patient!
Adding foreign key constraint to events.event_type_id. Note: this can take several minutes on large databases and slow machines. Please be patient!
Adding foreign key constraint to states.old_state_id. Note: this can take several minutes on large databases and slow machines. Please be patient!

Additional information

No response

[home-assistant[bot]]

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of `recorder` can trigger bot actions by commenting: - `@home-assistant close` Closes the issue. - `@home-assistant rename Awesome new title` Renames the issue. - `@home-assistant reopen` Reopen the issue. - `@home-assistant unassign recorder` Removes the current integration label and assignees on the issue, add the integration domain after the command. - `@home-assistant add-label needs-more-information` Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue. - `@home-assistant remove-label needs-more-information` Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

recorder documentation recorder source _{^{(message by IssueLinks)}}

[mackowskim]

Today I've dropped all the constraints, restarted HA and it's creating them again. At least will not have 30 of them in the end of the day.

[MatthiasRei]

I'm also fighting with the same problem since days 😟

@mackowskim if dropping the constraints will work, can you please describe how you did it? Thx 😃

[mackowskim]

No it did not work. Last night I gave up and downgraded HA to 2024.8.1

It works fine after the downgrade and I need to purge the database to make it bearable.

[MatthiasRei]

No it did not work. Last night I gave up and downgraded HA to 2024.8.1

It works fine after the downgrade and I need to purge the database to make it bearable.

Did the same. Downgrade to 2024.8.1 and everything is working fine 👍

[mackowskim]

Correct me if I'm wrong but it seems 2024.9 beta is fixing this issue?

[MatthiasRei]

Correct me if I'm wrong but it seems 2024.9 beta is fixing this issue?

Do you mean #123645 ?

[MatthiasRei]

Short update: With 2024.9.3 the database migration script run through without any problems. It took around 24h for a 23GB database 👍