Database migration fails (2024.8.2) `no unique constraint matching given keys for referenced table "event_data"`

home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.

https://www.home-assistant.io

Apache License 2.0

71.14k stars 29.81k forks source link

Database migration fails (2024.8.2) `no unique constraint matching given keys for referenced table "event_data"` #124135

Open rnorth opened 3 weeks ago

rnorth commented 3 weeks ago

The problem

Database migration is failing upon upgrade to 2024.8.2; I think this is a separate issue from #124127 and #124118.

I am using the TimescaleDB (Postgres) addon as my DB.

I've rolled back successfully using ha core update --version 2024.8.1 - after that I did attempt the upgrade a second time but was forced to roll back a second time too.

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

No response

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

Relevant logs from HA:

2024-08-17 19:54:37.079 WARNING (Recorder) [homeassistant.components.recorder.migration] The database is about to upgrade from schema version 44 to 45
2024-08-17 19:54:37.079 INFO (Recorder) [homeassistant.components.recorder.migration] Upgrading recorder db schema to version 45
2024-08-17 19:54:37.079 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding foreign key constraint to events.data_id. Note: this can take several minutes on large databases and slow machines. Please be patient!

...

2024-08-17 19:54:37.137 ERROR (Recorder) [homeassistant.helpers.recorder] Error executing query
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "event_data"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 101, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 727, in _add_constraint
    connection.execute(add_constraint)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "event_data"

[SQL: ALTER TABLE events ADD FOREIGN KEY(data_id) REFERENCES event_data (data_id)]
(Background on this error at: https://sqlalche.me/e/20/f405)

Postgres logs from around this time (no other errors shown):

2024-08-17 18:53:03.472 UTC [249] LOG:  checkpoint complete: wrote 1829 buffers (11.2%); 0 WAL file(s) added, 0 removed, 1 recycled; write=206.248 s, sync=7.226 s, total=216.041 s; sync files=69, longest=1.040 s, average=0.105 s; distance=20032 kB, estimate=21763 kB; lsn=209/21ACB700, redo lsn=209/20E33AE0
2024-08-17 18:54:27.555 UTC [249] LOG:  checkpoint starting: time
2024-08-17 18:54:37.129 UTC [36359] ERROR:  there is no unique constraint matching given keys for referenced table "event_data"
2024-08-17 18:54:37.129 UTC [36359] STATEMENT:  ALTER TABLE events ADD FOREIGN KEY(data_id) REFERENCES event_data (data_id)
2024-08-17 18:57:24.867 UTC [249] LOG:  checkpoint complete: wrote 1723 buffers (10.5%); 0 WAL file(s) added, 0 removed, 1 recycled; write=174.133 s, sync=0.515 s, total=177.312 s; sync files=60, longest=0.382 s, average=0.009 s; distance=13684 kB, estimate=20955 kB; lsn=209/2247FCB8, redo lsn=209/21B90D40
2024-08-17 18:59:27.966 UTC [249] LOG:  checkpoint starting: time



### Additional information

_No response_

home-assistant[bot] commented 3 weeks ago

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

skibbipl commented 3 weeks ago

I'm facing same issue. So I copied the database and started experimenting. I solved event_data problem by simply executing following sql:

alter table event_data add constraint data_id_unique unique (data_id);

After restaring HA it started data migration but crashed again on a different table:

2024-08-19 09:17:57.016 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding foreign key constraint to states.metadata_id. Note: this can take several minutes on large databases and slow machines. Please be patient!
2024-08-19 09:17:57.018 ERROR (Recorder) [homeassistant.helpers.recorder] Error executing query
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "states_meta"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 101, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 727, in _add_constraint
    connection.execute(add_constraint)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "states_meta"

[SQL: ALTER TABLE states ADD FOREIGN KEY(metadata_id) REFERENCES states_meta (metadata_id)]
(Background on this error at: https://sqlalche.me/e/20/f405)
2024-08-19 09:17:57.028 ERROR (Recorder) [homeassistant.components.recorder.core] Database error during schema migration
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "states_meta"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 988, in _migrate_schema
    new_schema_status = migrator(
                        ^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 335, in migrate_schema_live
    schema_status = _migrate_schema(
                    ^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 301, in _migrate_schema
    _apply_update(instance, hass, engine, session_maker, new_version, start_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 703, in wrapper
    job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 856, in _apply_update
    migrator_cls(instance, hass, engine, session_maker, old_version).apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 902, in apply_update
    self._apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1697, in _apply_update
    _restore_foreign_key_constraints(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 695, in _restore_foreign_key_constraints
    _add_constraint(session_maker, add_constraint, table, column)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 727, in _add_constraint
    connection.execute(add_constraint)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "states_meta"

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

Unfortunately I cannot add easily unique constraint on states_meta table as I have a lot of duplicated metadata_ids.

 metadata_id |                      entity_id
-------------+-----------------------------------------------------
         350 | sensor.feinstaubsensor_temperature
         350 | sensor.temperature_humidity_sensor_8c43_humidity
         352 | sensor.feinstaubsensor_pressure
         352 | sensor.temperature_humidity_sensor_8c43_voltage
         353 | sensor.feinstaubsensor_wifi_signal
         353 | sensor.temperature_humidity_sensor_8c43_temperature

I can see that table states references metadata_id but I have no clue which ID is for what entity:

 state_id | entity_id |  state  | metadata_id | attributes_id
----------+-----------+---------+-------------+---------------
 28346612 |           | unknown |         350 |      31167358
 27296383 |           | unknown |         350 |      31167358
 27296996 |           | unknown |         350 |      31167358
 28432872 |           | unknown |         350 |      31167358
 28435853 |           | unknown |         350 |      31167358
 27053915 |           | unknown |         350 |      31167358
 27053938 |           | unknown |         352 |      31167360
 28346615 |           | unknown |         352 |      31167360
 28432877 |           | unknown |         352 |      31167360
 28435856 |           | unknown |         352 |      31167360
 27296398 |           | unknown |         352 |      31167360
 27296998 |           | unknown |         352 |      31167360
 27296399 |           | unknown |         353 |      31167361
 27296999 |           | unknown |         353 |      31167361
 27053939 |           | unknown |         353 |      31167361
 28432878 |           | unknown |         353 |      31167361
 28346616 |           | unknown |         353 |      31167361
 28435857 |           | unknown |         353 |      31167361

Any hints how to proceed are welcome.

rnorth commented 3 weeks ago

Thanks @skibbipl I'm holding back on trying to manually resolve and am just sitting on 2024.8.1 for now - I hope that this can be fixed in HA!

skibbipl commented 3 weeks ago

So I dropped the states tables and restarted sequence numbers but it get's even worser:

2024-08-20 11:29:46.630 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding foreign key constraint to statistics.metadata_id. Note: this can take several minutes on large databases and slow machines. Please be patient!
2024-08-20 11:29:46.634 ERROR (Recorder) [homeassistant.helpers.recorder] Error executing query
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ForeignKeyViolation: insert or update on table "statistics" violates foreign key constraint "statistics_metadata_id_fkey"
DETAIL:  Key (metadata_id)=(8) is not present in table "statistics_meta".

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 101, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 727, in _add_constraint
    connection.execute(add_constraint)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "statistics" violates foreign key constraint "statistics_metadata_id_fkey"
DETAIL:  Key (metadata_id)=(8) is not present in table "statistics_meta".

[SQL: ALTER TABLE statistics ADD FOREIGN KEY(metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2024-08-20 11:29:46.644 ERROR (Recorder) [homeassistant.components.recorder.migration] Could not update foreign options in statistics table, will delete violations and try again
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ForeignKeyViolation: insert or update on table "statistics" violates foreign key constraint "statistics_metadata_id_fkey"
DETAIL:  Key (metadata_id)=(8) is not present in table "statistics_meta".

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 695, in _restore_foreign_key_constraints
    _add_constraint(session_maker, add_constraint, table, column)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 727, in _add_constraint
    connection.execute(add_constraint)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "statistics" violates foreign key constraint "statistics_metadata_id_fkey"
DETAIL:  Key (metadata_id)=(8) is not present in table "statistics_meta".

[SQL: ALTER TABLE statistics ADD FOREIGN KEY(metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

evantaur commented 3 weeks ago

Having the same issue with MariaDB

piotruscilko commented 2 weeks ago

I have similar issue in Postgresql DB (i think the same reason). In my case issue appeared also in 2024.8.1

Database error during schema migration
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.FeatureNotSupported: cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view "Błąd duplikacji w HASS" depends on column "metadata_id"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 988, in _migrate_schema
    new_schema_status = migrator(
                        ^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 335, in migrate_schema_live
    schema_status = _migrate_schema(
                    ^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 301, in _migrate_schema
    _apply_update(instance, hass, engine, session_maker, new_version, start_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 703, in wrapper
    job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 856, in _apply_update
    migrator_cls(instance, hass, engine, session_maker, old_version).apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 902, in apply_update
    self._apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1658, in _apply_update
    _modify_columns(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 562, in _modify_columns
    connection.execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.NotSupportedError: (psycopg2.errors.FeatureNotSupported) cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view "Błąd duplikacji w HASS" depends on column "metadata_id"

[SQL: ALTER TABLE statistics ALTER metadata_id TYPE BIGINT]
(Background on this error at: https://sqlalche.me/e/20/tw8g)

code-chicken commented 2 weeks ago

Even with 2024.8.3 updating the database fails with PostgreSQL. Here are the messages that I get from journalctl:

# journalctl -u postgresql --since "60 min ago" -f
Aug 26 06:06:15 hamilton systemd[1]: Starting PostgreSQL database server...
Aug 26 06:06:15 hamilton postgres[425]: 2024-08-26 06:06:15.537 CEST [425] LOG:  starting PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.1 20230801, 64-bit
Aug 26 06:06:15 hamilton postgres[425]: 2024-08-26 06:06:15.537 CEST [425] LOG:  listening on IPv4 address "0.0.0.0", port 5432
Aug 26 06:06:15 hamilton postgres[425]: 2024-08-26 06:06:15.537 CEST [425] LOG:  listening on IPv6 address "::", port 5432
Aug 26 06:06:15 hamilton postgres[425]: 2024-08-26 06:06:15.541 CEST [425] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
Aug 26 06:06:15 hamilton postgres[433]: 2024-08-26 06:06:15.559 CEST [433] LOG:  database system was shut down at 2024-08-26 06:04:36 CEST
Aug 26 06:06:15 hamilton postgres[425]: 2024-08-26 06:06:15.583 CEST [425] LOG:  database system is ready to accept connections
Aug 26 06:06:15 hamilton systemd[1]: Started PostgreSQL database server.
Aug 26 06:11:15 hamilton postgres[431]: 2024-08-26 06:11:15.657 CEST [431] LOG:  checkpoint starting: time
Aug 26 06:15:38 hamilton postgres[431]: 2024-08-26 06:15:38.112 CEST [431] LOG:  checkpoint complete: wrote 2603 buffers (15.9%); 0 WAL file(s) added, 1 removed, 0 recycled; write=262.431 s, sync=0.010 s, total=262.456 s; sync files=59, longest=0.003 s, average=0.001 s; distance=18204 kB, estimate=18204 kB; lsn=16A/960341C8, redo lsn=16A/95D00640
Aug 26 06:16:10 hamilton postgres[4476]: 2024-08-26 06:16:10.904 CEST [4476] ERROR:  insert or update on table "states" violates foreign key constraint "states_attributes_id_fkey"
Aug 26 06:16:10 hamilton postgres[4476]: 2024-08-26 06:16:10.904 CEST [4476] DETAIL:  Key (attributes_id)=(1147858) is not present in table "state_attributes".
Aug 26 06:16:10 hamilton postgres[4476]: 2024-08-26 06:16:10.904 CEST [4476] STATEMENT:  ALTER TABLE states ADD FOREIGN KEY(attributes_id) REFERENCES state_attributes (attributes_id)
Aug 26 06:16:15 hamilton postgres[431]: 2024-08-26 06:16:15.131 CEST [431] LOG:  checkpoint starting: time
Aug 26 06:17:19 hamilton postgres[431]: 2024-08-26 06:17:19.148 CEST [431] LOG:  checkpoint complete: wrote 639 buffers (3.9%); 0 WAL file(s) added, 0 removed, 1 recycled; write=63.990 s, sync=0.011 s, total=64.018 s; sync files=25, longest=0.008 s, average=0.001 s; distance=3279 kB, estimate=16711 kB; lsn=16A/96034310, redo lsn=16A/960342D8
Aug 26 06:18:38 hamilton postgres[4659]: 2024-08-26 06:18:38.231 CEST [4659] ERROR:  update or delete on table "states" violates foreign key constraint "states_old_state_id_fkey" on table "states"
Aug 26 06:18:38 hamilton postgres[4659]: 2024-08-26 06:18:38.231 CEST [4659] DETAIL:  Key (state_id)=(72379612) is still referenced from table "states".
Aug 26 06:18:38 hamilton postgres[4659]: 2024-08-26 06:18:38.231 CEST [4659] STATEMENT:  DELETE FROM states WHERE attributes_id in (SELECT attributes_id from states as t1 WHERE (t1.attributes_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM state_attributes AS t2 WHERE t2.attributes_id = t1.attributes_id)) LIMIT 100000);

So I am still stuck at 2024.8.1

Martin8211 commented 2 weeks ago

My Version of MariaDB was not up to date. After updating to 2.7.1 the datamigration was successful on first try.

rnorth commented 1 week ago

Just tried again with 2024.9.0 and getting similar errors:

Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "event_data"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 981, in _migrate_schema
    new_schema_status = migrator(
                        ^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 334, in migrate_schema_live
    schema_status = _migrate_schema(
                    ^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 300, in _migrate_schema
    _apply_update(instance, hass, engine, session_maker, new_version, start_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 703, in wrapper
    job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 883, in _apply_update
    migrator_cls(instance, hass, engine, session_maker, old_version).apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 929, in apply_update
    self._apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1761, in _apply_update
    _restore_foreign_key_constraints(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 721, in _restore_foreign_key_constraints
    _add_constraint(session_maker, add_constraint, table, column)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 753, in _add_constraint
    connection.execute(add_constraint)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "event_data"

[SQL: ALTER TABLE events ADD FOREIGN KEY(data_id) REFERENCES event_data (data_id)]
(Background on this error at: https://sqlalche.me/e/20/f405)

Error executing query
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "event_data"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 101, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 753, in _add_constraint
    connection.execute(add_constraint)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "event_data"

[SQL: ALTER TABLE events ADD FOREIGN KEY(data_id) REFERENCES event_data (data_id)]
(Background on this error at: https://sqlalche.me/e/20/f405)

Ultimately culminating in:

Will try and downgrade to 2024.8.1 again 😢

rnorth commented 1 week ago

A question to the maintainers: is there anything that'd help to diagnose this issue further? I'm guessing that at some point in the past our DB schemas have diverged from what the migrations expect (though I've not done any manual tinkering).

Would a dump of my current schema help?

TheDK commented 1 week ago

After going to .8 my admittedly too large DB (80 GB) not only failed the update without noticing (states table full), but also corrupted the DB because HA ran into memory issues and crashed after trying to migrate for 20 hrs.

Reverted back to .7, restored the DB from backup and am now trying to at least cut the DB by half by purging for only 6 months before trying .9 some time next weekend.

But I gotta say: This once again shows that without quite some knowledge and willingness to sink time into it HA is not for casual end users…

rnorth commented 1 week ago

@TheDK based on other issues (like #124356 and others) there are at least two distinct problems:

the migration process taking a very long time or using a lot of storage, potentially failing as a result (seems like the issue you have)
the migration failing immediately due to attempting operations on the data that aren't compatible with its current schema (my issue)

I hope that both can be fixed; hopefully the maintainers are aware that they're chasing two different problems here.

TheDK commented 1 week ago

@TheDK based on other issues (like #124356 and others) there are at least two distinct problems:

I hope that both can be fixed; hopefully the maintainers are aware that they're chasing two different problems here.

You´re right (and I initially wanted to post on the general release thread but ended up in the link you provided - my bad...) and I think the first part is hard to address (so many different environments, DB sizes, etc.), while the second one might be even harder as your problem could be a lot of things happening to the DB over a longer time span - so tracking individual problems...

Additionally, the final resort of starting with a new DB has become even more painful as long-term statistics would be gone as well.

srkoster commented 1 week ago

I faced the same issue and did the following to resolve it:

Create a new database and point the record to that one.
Restarted HA and let it create the database schema.
Gathered the database schema (DDL) using DBeaver.
Repointed HA to my original database and restarted it again.
I then compared my original database schema with the new one and found missing primary keys, which I created manually using the DDL of the new database. I also found missing indexes and created those.
I faced some issues with my statistics table having duplicate primary key values. These double entries were similar except for the metadata_id. In my case, one of those metadata_id entries didn't have an entry in statistics_meta and apparently it had to do with a renaming of an entity. I deleted all records in statistics pointed to that metadata_id to solve the creation of the primary key.
I then upgraded to 2024.8.2 and all foreign key constraints were added succesfully.

quentinmit commented 1 week ago

I compared the schema in the code (https://github.com/home-assistant/core/blob/dev/homeassistant/components/recorder/db_schema.py) against the actual schema in my database and added all the missing primary keys and indexes. In my case, that consisted of the following DDL:

alter table states add primary key (state_id);
create index ix_states_metadata_id_last_updated_ts ON states (metadata_id, last_updated_ts);
create index ix_states_context_id_bin on states (context_id_bin);
create index on states (last_updated_ts);
create index on states (old_state_id);
create index on states (attributes_id);
alter table states_meta add primary key (metadata_id);
alter table states_meta add unique (entity_id);
alter table statistics add primary key (id);
alter table statistics_meta add primary key (id);
alter table statistics_meta add unique (statistic_id);
alter table statistics_runs drop index statistics_run_pkey;
alter table statistics_runs add primary key (run_id);
alter table statistics_short_term add primary key (id);
create index on statistics_short_term (start_ts);
create unique index ix_statistics_short_term_statistic_id_start_ts on statistics_short_term (metadata_id, start_ts);

After doing that, the migration to add foreign keys was successful. I also saw a massive drop in postgres throughput, which is a nice side effect.

rnorth commented 1 week ago

Thank you @srkoster, @quentinmit I'm mighty tempted to do the same, and you've saved me some digging by sharing your approaches.

Still (for now) holding out hope that it'll be resolved by a change in HA - it never feels great to apply manual fixes to a discrepancy that in theory should be automatically resolved... I think as time goes on the temptation to do it will steadily increase, though!

superrob commented 1 week ago

Had the foreign key issue others are experiencing on MariaDB. After going through a couple handfuls of my remaining hair, I noticed that my database was a mix of InnoDB and MyISAM and saw that the recorder documentation specifically calls for InnoDB as the database engine.

No idea how my database ended up in this state. It might have happen at the migration from SQLlite to MariaDB which i did years ago.

So if anyone is experiencing the foreign key error 150 issue, please check that no tables are using the MyISAM engine.

code-chicken commented 1 week ago

I just tried an update from 8.1 to 9.0 and still get the following messages after about 6 minutes.

hamilton ~ # journalctl -u postgresql --since "10 minutes ago" -f
Sep 06 15:53:55 hamilton postgres[4768]: 2024-09-06 15:53:55.480 CEST [4768] ERROR:  insert or update on table "states" violates foreign key constraint "states_attributes_id_fkey"
Sep 06 15:53:55 hamilton postgres[4768]: 2024-09-06 15:53:55.480 CEST [4768] DETAIL:  Key (attributes_id)=(1147858) is not present in table "state_attributes".
Sep 06 15:53:55 hamilton postgres[4768]: 2024-09-06 15:53:55.480 CEST [4768] STATEMENT:  ALTER TABLE states ADD FOREIGN KEY(attributes_id) REFERENCES state_attributes (attributes_id)
Sep 06 15:56:37 hamilton postgres[4765]: 2024-09-06 15:56:37.084 CEST [4765] ERROR:  update or delete on table "states" violates foreign key constraint "states_old_state_id_fkey" on table "states"
Sep 06 15:56:37 hamilton postgres[4765]: 2024-09-06 15:56:37.084 CEST [4765] DETAIL:  Key (state_id)=(72379612) is still referenced from table "states".
Sep 06 15:56:37 hamilton postgres[4765]: 2024-09-06 15:56:37.084 CEST [4765] STATEMENT:  DELETE FROM states WHERE attributes_id in (SELECT attributes_id from states as t1 WHERE (t1.attributes_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM state_attributes AS t2 WHERE t2.attributes_id = t1.attributes_id)) LIMIT 100000);

Datebase is PostgreSQL Version is 16.3

sagargp commented 6 days ago

I compared the schema in the code (https://github.com/home-assistant/core/blob/dev/homeassistant/components/recorder/db_schema.py) against the actual schema in my database and added all the missing primary keys and indexes. In my case, that consisted of the following DDL:
alter table states add primary key (state_id);
create index ix_states_metadata_id_last_updated_ts ON states (metadata_id, last_updated_ts);
create index ix_states_context_id_bin on states (context_id_bin);
create index on states (last_updated_ts);
create index on states (old_state_id);
create index on states (attributes_id);
alter table states_meta add primary key (metadata_id);
alter table states_meta add unique (entity_id);
alter table statistics add primary key (id);
alter table statistics_meta add primary key (id);
alter table statistics_meta add unique (statistic_id);
alter table statistics_runs drop index statistics_run_pkey;
alter table statistics_runs add primary key (run_id);
alter table statistics_short_term add primary key (id);
create index on statistics_short_term (start_ts);
create unique index ix_statistics_short_term_statistic_id_start_ts on statistics_short_term (metadata_id, start_ts);
After doing that, the migration to add foreign keys was successful. I also saw a massive drop in postgres throughput, which is a nice side effect.

I'm having the same issue, and some of these manual migration steps are failing because of duplicate data. I'm at a loss how the schema didn't have unique constraints on these ID fields to begin with, let alone how they became duplicated. I'm getting errors like this:

ERROR: multiple primary keys for table "states_meta" are not allowed

ERROR: could not create unique index "statistics_pkey"
Detail: Key (id)=(420030) is duplicated.

etc. It seems that I have about 43 different state_attributes that duplicate the attributes_id field. I found this using this query:

SELECT attributes_id, jsonb_agg((shared_attrs)::jsonb ->> 'friendly_name') as friendly_names
  FROM state_attributes
 GROUP BY attributes_id
HAVING count(*) > 1;

This shows results like this:

attributes_id   friendly_names
799272          ["Downstairs Current Temperature", "Bedroom Air Filter energy"]
11935921        ["SQRM193812P Cooktop Status", "Bedroom Air Filter power"]
799274          ["OpenWeatherMap Temperature", "Bedroom Air Filter"]
...

The friendly names that share attributes_ids seem to be wildly unrelated. Note that the "Bedroom Air Filter" is a switch that I may have changed the name of a handful of times over the years, as I've used it for different purposes.

The only way I can think of to fix this is to manually go through each one of the 43 duplicates and determine which state_attribute is stale and manually delete its record from the state_attributes table. And then repeat this process in the other tables that are giving me duplicate errors.