search

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.89k stars 30.12k forks source link

Historic data migration fails and leads to wipe of the database #111073

Closed CFenner closed 4 months ago

CFenner commented 7 months ago

The problem

Originally I tried to replace a Shelly measuring device for my solar panels and wanted to keep the historic data. I followed the steps listed in the energy dashboard faq.

Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_energy', 'sensor.shelly_pv_sandkiste_switch_0_energy', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_power', 'sensor.shelly_pv_sandkiste_switch_0_power', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_current', 'sensor.shelly_pv_sandkiste_switch_0_current', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_voltage', 'sensor.shelly_pv_sandkiste_switch_0_voltage', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_device_temperature', 'sensor.shelly_pv_sandkiste_switch_0_device_temperature', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.shelly_pv_sandkiste_energy', 'sensor.shelly_pv_sandkiste_switch_0_energy', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

After a while energy dashboard is empty and full backup size decrease dramatically. 

The system will rename the corrupt database file //config/.data/home-assistant_v2.db to //config/.data/home-assistant_v2.db.corrupt.2024-02-21T10:40:39.660703+00:00 in order to allow startup to proceed
Unrecoverable sqlite3 database corruption detected: (sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id] [parameters: (None, '419507912', None, None, None, None, None, 1708512039.086579, 253121509, 9317138, None, None, None, 0, <memory at 0x7f76256d40>, None, None, 63)] (Background on this error at: https://sqlalche.me/e/20/gkpj)

What concerns me is that this did not came to my attention earlier as there was no warning about the renaming. I think this should be raised to the user immediately to avoid data loss. I noticed only after 4 days.

What version of Home Assistant Core has the issue?

core-24-02

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

No response

Link to integration documentation on our website

No response

Diagnostics information

home-assistant_2024-02-21T11-16-04.631Z.log

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

mib1185 commented 7 months ago

i'm sorry, but the mentioned documentation is not written nor support by the HA project

CFenner commented 7 months ago

Ok, didn't see that. Nevertheless due to the renaming of an entity the database is set into some invalid state.

mib1185 commented 7 months ago

renaming of an entity the database is set into some invalid state

indeed, this should not happen!

home-assistant[bot] commented 7 months 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)

ASchwarz1984 commented 7 months ago

Experienced the same issue, after re-adopting a disconnected Hue motion detector in ZHA and renaming it to the previously used name I've received the same error regarding a corrupted sqlite3 DB and all of my historic data is gone.

CFenner commented 7 months ago

I restored some days ago and was able to replace some of my Shellys successfully. Did today the same with my Balkonkraftwerk but that again fails the DB directly 😭.

Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.shelly_pv_sandkiste_energy', 'sensor.shelly_pv_sandkiste_switch_0_energy', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3271, in update
    result: CursorResult[Any] = self.session.execute(
                                ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2308, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2190, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1617, in orm_execute_statement
    return super().orm_execute_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2344, 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 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.shelly_pv_sandkiste_energy', 'sensor.shelly_pv_sandkiste_switch_0_energy', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
denwald commented 7 months ago

I experienced the sqlalchemy.exc.IntegrityError today as well during shutdown of my HA 2024.2.4 docker compose setup for maintenance. During the shutdown a new database file was created. The old database file was fortunately not deleted, and only renamed to home-assistant_v2.db.corrupt*. Using this backup file, I was able to rescue years of statistics by switching the sqlite file back. I performed a PRAGMA integrity_check;on the old (corrupt) sqlite file, which returned no problems.

Because of my own stupidity, I lost the log file with all the entries that were put there before the database failure. So unfortunately, I can't help you with more details.

One thing that is striking though, and might be related to this problem, is the following: About an hour before the error occurred, I upgraded two of my Shelly Plus Plugs to the latest [1.2.2] 2024-02-23 firmware. This FW upgrade was special, because it changed the structure of the MQTT topics quite significantly, which I only noticed by chance. Maybe the changed MQTT message structure has thrown off the Shelly integration in Home Assistant? To be on the safe side, I removed the Shelly Integration for the time being, since I don't use the devices at the moment.

skarcha commented 7 months ago

Same problem using Docker versión 2024.2.5

Logger: homeassistant.components.recorder.statistics
Source: components/recorder/statistics.py:742
Integration: Recorder (documentation, issues)
First occurred: 09:59:53 (1 occurrences)
Last logged: 09:59:53

Blocked attempt to insert duplicated statistic rows, please report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+recorder%22
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3271, in update
    result: CursorResult[Any] = self.session.execute(
                                ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2308, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2190, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1617, in orm_execute_statement
    return super().orm_execute_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2344, 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 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.azotea_presion', 'sensor.azotea_presion_2', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Next error in log:

Logger: homeassistant.components.recorder.entity_registry
Source: components/recorder/entity_registry.py:66
Integration: Recorder (documentation, issues)
First occurred: 09:59:53 (1 occurrences)
Last logged: 09:59:53

Cannot migrate history for entity_id `sensor.azotea_presion_2` to `sensor.azotea_presion` because the new entity_id is already in use

And, a corruption detected:

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:925
Integration: Recorder (documentation, issues)
First occurred: 10:00:10 (1 occurrences)
Last logged: 10:00:10

Unrecoverable sqlite3 database corruption detected: (sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id] [parameters: (None, '17', None, None, None, None, None, 1709197198.628413, 28682115, 5, None, None, None, 0, <memory at 0x7ff458101e40>, None, None, 5)] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2113, in _exec_insertmany_context
    dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

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 925, in _process_one_task_or_event_or_recover
    self._commit_event_session_or_retry()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1182, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1202, in _commit_event_session
    session.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1969, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1256, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1231, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4312, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4447, in _flush
    with util.safe_reraise():
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4408, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 463, in execute
    n.execute_aggregate(self, set_)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 759, in execute_aggregate
    persistence.save_obj(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1137, in _emit_insert_statements
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
    return self._exec_insertmany_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2121, in _exec_insertmany_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2344, 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 2113, in _exec_insertmany_context
    dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, '17', None, None, None, None, None, 1709197198.628413, 28682115, 5, None, None, None, 0, <memory at 0x7ff458101e40>, None, None, 5)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
jthun commented 7 months ago

Experienced the same issue, after re-adopting a disconnected Hue motion detector in ZHA and renaming it to the previously used name I've received the same error regarding a corrupted sqlite3 DB and all of my historic data is gone.

Happened to me today as well.

I excluded a ZWave FGMS001 (Fibaro Motion Sensor) and included it again, renaming to the same name on the device and sensors as the one I excluded. Got the db corrupted but it really wasn't (all PRAGMA checks ran just fine), so I could do a ha core stop, rename the corrupted db back to home-assistant_v2.db and do ha core start.

I'm using ZWave JS UI with the standard ZWave integration.

denwald commented 7 months ago

I think this error should produce some notification in the UI to get the attention of the instance admin. It seems to be easy to recover from it, if you notice it quickly enough.

Just wanted to emphasize again, that my 'corrupt' database passed all sqlite3 consistency checks without issues.

skarcha commented 7 months ago

Argh! Too late to me. I lost all the historic data. :-/

CFenner commented 7 months ago

@denwald do you mean that I just rename the corrupt database and restart and I can continue with the historic data?

denwald commented 7 months ago

do you mean that I just rename the corrupt database and restart and I can continue with the historic data?

It worked for me. I was lucky, because I noticed the problem after only three hours.

To be extra cautious, I performed a thorough integrity check on the database file that was marked as 'corrupt' using the pragma integrity_check;command. It ran on my db for maybe 5 minutes and returned without any issues reported. I then shut down the home assistant container and replaced the database files manually.

I think your problem might be a little bit different from mine. I did not rename any entities manually before the error happened.

oronaldosantos commented 6 months ago

Same here. I've noticed this happen every time I update entities sensors:

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:139
Integration: Recorder ([documentation](https://www.home-assistant.io/integrations/recorder), [issues](https://github.com/home-assistant/core/issues?q=is%3Aissue+is%3Aopen+label%3A%22integration%3A+recorder%22))
First occurred: 11:10:23 AM (1 occurrences)
Last logged: 11:10:23 AM

Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.iluminancia_escada_escritorio', 'sensor.movimento_escada_escritorio_illuminance_lux', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

Do anyone now why and how to prevent this to happen?

dsteinkopf commented 6 months ago

Same here using HA 2024.3.0 while or after renaming an entity (not sure but strong assumption).

From my log:

2024-03-10 08:00:05.586 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.shelly_medienzimmer_analog_temperature', 'sensor.shelly_medienzimmer_analog_temperature_2', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1960, 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)
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3250, in update

...

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.shelly_medienzimmer_analog_temperature', 'sensor.shelly_medienzimmer_analog_temperature_2', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2024-03-10 08:00:05.631 WARNING (Recorder) [homeassistant.components.recorder.statistics] Blocked attempt to insert duplicated statistic rows, please report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+recorder%22
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1960, 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)
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3250, in update

...

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.shelly_medienzimmer_analog_temperature', 'sensor.shelly_medienzimmer_analog_temperature_2', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2024-03-10 08:00:06.083 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detected: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, '-63', None, None, None, None, None, 1710054005.318044, 1475897292, 152422402, None, None, None, 0, <memory at 0x7f94397b7100>, None, None, 94)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2104, in _exec_insertmany_context
    dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

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 928, in _process_one_task_or_event_or_recover
    self._commit_event_session_or_retry()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1185, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1205, in _commit_event_session
    session.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1972, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1257, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^

...

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, '-63', None, None, None, None, None, 1710054005.318044, 1475897292, 152422402, None, None, None, 0, <memory at 0x7f94397b7100>, None, None, 94)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2024-03-10 08:00:06.195 ERROR (Recorder) [homeassistant.components.recorder.util] The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2024-03-10T07:00:06.195672+00:00 in order to allow startup to proceed
2024-03-10 08:00:20.886 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id `sensor.shelly_medienzimmer_energy_today_2` to `sensor.shelly_medienzimmer_energy_today` because the new entity_id is already in use
2024-03-10 08:00:28.087 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id `sensor.shelly_medienzimmer_energy_total_2` to `sensor.shelly_medienzimmer_energy_total` because the new entity_id is already in use
2024-03-10 08:00:47.258 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id `sensor.shelly_medienzimmer_energy_yesterday_2` to `sensor.shelly_medienzimmer_energy_yesterday` because the new entity_id is already in use

I also was able to check and rename the "corrupt" db and everything until then was back again and working.

CFenner commented 6 months ago

I've managed to re-add the shelly sensors yesterday. But today it struck me again when I renamed an aqara sensor. This is pretty annoying!

In the logs it's saying that there should have been a renaming taking place, but that didn't happen, but this may be another issue.

The system will rename the corrupt database file //config/.data/home-assistant_v2.db to //config/.data/home-assistant_v2.db.corrupt.2024-03-12T12:15:18.255844+00:00 in order to allow startup to proceed

2024-03-12 13:15:17.627 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[parameters: ('sensor.aqara_wohnungstur_og_batterie', 'sensor.lumi_lumi_sensor_magnet_aq2_batterie', 'recorder')]
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
[parameters: ('sensor.aqara_wohnungstur_og_batterie', 'sensor.lumi_lumi_sensor_magnet_aq2_batterie', 'recorder')]
2024-03-12 13:15:17.799 WARNING (Recorder) [homeassistant.components.recorder.statistics] Blocked attempt to insert duplicated statistic rows, please report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+recorder%22
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
[parameters: ('sensor.aqara_wohnungstur_og_batterie', 'sensor.lumi_lumi_sensor_magnet_aq2_batterie', 'recorder')]
2024-03-12 13:15:17.827 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id `sensor.lumi_lumi_sensor_magnet_aq2_batterie` to `sensor.aqara_wohnungstur_og_batterie` because the new entity_id is already in use
2024-03-12 13:15:17.853 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
2024-03-12 13:15:17.907 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detected: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 929, in _process_one_task_or_event_or_recover
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 78, in run
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
2024-03-12 13:15:18.254 INFO (Recorder) [homeassistant.components.recorder.pool.RecorderPool] Pool recreating
2024-03-12 13:15:18.255 ERROR (Recorder) [homeassistant.components.recorder.util] The system will rename the corrupt database file //config/.data/home-assistant_v2.db to //config/.data/home-assistant_v2.db.corrupt.2024-03-12T12:15:18.255844+00:00 in order to allow startup to proceed
2024-03-12 13:15:18.477 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id `button.lumi_lumi_sensor_magnet_aq2_identifizieren` to `button.aqara_wohnungstur_og_identifizieren` because the new entity_id is already in use
2024-03-12 13:15:18.513 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id `binary_sensor.lumi_lumi_sensor_magnet_aq2_offnet` to `binary_sensor.aqara_wohnungstur_og_offnet` because the new entity_id is already in use
dsteinkopf commented 6 months ago

Will this probably be solved by #112718, too?

koltlp commented 6 months ago

I had the same issue when removing a sensor then adding a new one. I renamed the new sensor entities to the old entity names then the database went corrupt and all historic data lost. The removed sensor might have sent data via Zigbee.

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.homero_1_3em_temperature', 'sensor.homero_1_3em_homerseklet', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj) 2024-03-07 16:41:08.803 WARNING (Recorder) [homeassistant.components.recorder.statistics] Blocked attempt to insert duplicated statistic rows, please report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+recorder%22 Traceback (most recent call last): File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

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

Traceback (most recent call last): File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope yield session File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata statistics_meta_manager.update_statistic_id( File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id ).update({StatisticsMeta.statistic_id: new_statistic_id}) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3271, in update result: CursorResult[Any] = self.session.execute( ^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2308, in execute return self._execute_internal( ^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2190, in _execute_internal result: Result[Any] = compile_state_cls.orm_execute_statement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1617, in orm_execute_statement return super().orm_execute_statement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement result = conn.execute( ^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute return meth( ^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection return connection._execute_clauseelement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement ret = self._execute_context( ^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context return self._exec_single_context( ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context self._handle_dbapi_exception( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2344, 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 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.homero_1_3em_temperature', 'sensor.homero_1_3em_homerseklet', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj) 2024-03-07 16:41:08.848 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id sensor.homero_1_3em_homerseklet to sensor.homero_1_3em_temperature because the new entity_id is already in use 2024-03-07 16:41:09.532 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detected: (sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: INSERT INTO events (event_type, event_data, origin, origin_idx, time_fired, time_fired_ts, context_id, context_user_id, context_parent_id, data_id, context_id_bin, context_user_id_bin, context_parent_id_bin, event_type_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING event_id] [parameters: (None, None, None, 0, None, 1709826068.60717, None, None, None, 6901, <memory at 0x7fce02c7d900>, None, None, 18)] (Background on this error at: https://sqlalche.me/e/20/gkpj) Traceback (most recent call last): File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) sqlite3.IntegrityError: FOREIGN KEY constraint failed

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 925, in _process_one_task_or_event_or_recover self._commit_event_session_or_retry() File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1182, in _commit_event_session_or_retry self._commit_event_session() File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1202, in _commit_event_session session.commit() File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1969, in commit trans.commit(_to_root=True) File "", line 2, in commit File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go ret_value = fn(self, *arg, *kw) ^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1256, in commit self._prepare_impl() File "", line 2, in _prepare_impl File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go ret_value = fn(self, arg, kw) ^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1231, in _prepare_impl self.session.flush() File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4312, in flush self._flush(objects) File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4447, in _flush with util.safe_reraise(): File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit raise exc_value.with_traceback(exc_tb) File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4408, in _flush flush_context.execute() File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute rec.execute(self) File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute util.preloaded.orm_persistence.save_obj( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj _emit_insert_statements( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1227, in _emit_insert_statements result = connection.execute( ^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute return meth( ^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection return connection._execute_clauseelement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement ret = self._execute_context( ^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context return self._exec_single_context( ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context self._handle_dbapi_exception( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2344, 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 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: INSERT INTO events (event_type, event_data, origin, origin_idx, time_fired, time_fired_ts, context_id, context_user_id, context_parent_id, data_id, context_id_bin, context_user_id_bin, context_parent_id_bin, event_type_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING event_id] [parameters: (None, None, None, 0, None, 1709826068.60717, None, None, None, 6901, <memory at 0x7fce02c7d900>, None, None, 18)] (Background on this error at: https://sqlalche.me/e/20/gkpj) 2024-03-07 16:41:09.687 ERROR (Recorder) [homeassistant.components.recorder.util] The system will rename the corrupt database file** //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2024-03-07T15:41:09.687503+00:00 in order to allow startup to proceed

Logger: homeassistant.components.recorder.util Source: components/recorder/util.py:139 Integration: Recorder (documentation, issues) First occurred: 2024. február 22. 17:43:44 (6 occurrences) Last logged: 16:41:08 nn Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.homero_1_3em_temperature', 'sensor.homero_1_3em_homerseklet', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj) Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.homero_1_3em_humidity', 'sensor.homero_1_3em_paratartalom', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj) Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.homero_1_3em_battery', 'sensor.homero_1_3em_elem_battery', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj) Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.homero_1_3em_homerseklet', 'sensor.ewelink_th01_homerseklet', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj) Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.homero_1_3em_paratartalom', 'sensor.ewelink_th01_paratartalom', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj) Traceback (most recent call last): File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

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

Traceback (most recent call last): File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope yield session File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata statistics_meta_manager.update_statistic_id( File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id ).update({StatisticsMeta.statistic_id: new_statistic_id}) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3271, in update result: CursorResult[Any] = self.session.execute( ^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2308, in execute return self._execute_internal( ^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2190, in _execute_internal result: Result[Any] = compile_state_cls.orm_execute_statement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1617, in orm_execute_statement return super().orm_execute_statement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement result = conn.execute( ^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute return meth( ^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection return connection._execute_clauseelement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement ret = self._execute_context( ^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context return self._exec_single_context( ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context self._handle_dbapi_exception( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2344, 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 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.homero_1_3em_temperature', 'sensor.homero_1_3em_homerseklet', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)

CFenner commented 6 months ago

@koltlp are you on the latest patch? There was a fix for another issue that may also help for this one.

arthurrump commented 6 months ago

I had what looks like a similar issue yesterday, after adding a new Zigbee device and renaming that device in the dialog that comes up after pairing. I think I was still on 2024.3.0 and only noticed the update afterwards.

Here's the log:

2024-03-15 13:25:49.080 DEBUG (MainThread) [homeassistant.components.zha.core.device] [0x1F34](TRADFRI motion sensor): power source: Battery or Unknown
2024-03-15 13:25:49.081 DEBUG (MainThread) [homeassistant.components.zha.core.device] [0x1F34](TRADFRI motion sensor): completed initialization
...
2024-03-15 13:26:20.078 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.new_device_name_battery', 'sensor.ikea_of_sweden_tradfri_motion_sensor_battery', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1960, 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)
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3250, in update
    result: CursorResult[Any] = self.session.execute(
                                ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1617, in orm_execute_statement
    return super().orm_execute_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1408, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 513, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1630, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1839, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1979, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2335, 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 1960, 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: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.new_device_name_battery', 'sensor.ikea_of_sweden_tradfri_motion_sensor_battery', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2024-03-15 13:26:20.128 WARNING (Recorder) [homeassistant.components.recorder.statistics] Blocked attempt to insert duplicated statistic rows, please report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+recorder%22
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1960, 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)
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3250, in update
    result: CursorResult[Any] = self.session.execute(
                                ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1617, in orm_execute_statement
    return super().orm_execute_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1408, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 513, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1630, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1839, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1979, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2335, 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 1960, 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: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.new_device_name_battery', 'sensor.ikea_of_sweden_tradfri_motion_sensor_battery', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2024-03-15 13:26:20.171 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, None, None, None, None, None, None, 1710505579.97539, 343944520, 907218, None, None, None, 0, <memory at 0x7f03620b41c0>, None, None, 2301)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1960, 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)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3250, in update
    result: CursorResult[Any] = self.session.execute(
                                ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2170, in _execute_internal
    ) = compile_state_cls.orm_pre_session_exec(
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/bulk_persistence.py", line 696, in orm_pre_session_exec
    session._autoflush()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3005, in _autoflush
    raise e.with_traceback(sys.exc_info()[2])
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2994, in _autoflush
    self.flush()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4296, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4431, in _flush
    with util.safe_reraise():
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4392, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 463, in execute
    n.execute_aggregate(self, set_)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 759, in execute_aggregate
    persistence.save_obj(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1233, in _emit_insert_statements
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1408, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 513, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1630, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1839, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1979, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2335, 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 1960, 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, None, None, None, None, None, None, 1710505579.97539, 343944520, 907218, None, None, None, 0, <memory at 0x7f03620b41c0>, None, None, 2301)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2024-03-15 13:26:20.208 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detected: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, None, None, None, None, None, None, 1710505579.97539, 343944520, 907218, None, None, None, 0, <memory at 0x7f03620b41c0>, None, None, 2301)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1960, 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)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

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 929, in _process_one_task_or_event_or_recover
    return task.run(self)
           ^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 78, in run
    statistics.update_statistics_metadata(
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 742, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 313, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3250, in update
    result: CursorResult[Any] = self.session.execute(
                                ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2170, in _execute_internal
    ) = compile_state_cls.orm_pre_session_exec(
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/bulk_persistence.py", line 696, in orm_pre_session_exec
    session._autoflush()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3005, in _autoflush
    raise e.with_traceback(sys.exc_info()[2])
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2994, in _autoflush
    self.flush()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4296, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4431, in _flush
    with util.safe_reraise():
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4392, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 463, in execute
    n.execute_aggregate(self, set_)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 759, in execute_aggregate
    persistence.save_obj(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1233, in _emit_insert_statements
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1408, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 513, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1630, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1839, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1979, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2335, 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 1960, 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, context_user_id_bin, context_parent_id_bin, metadata_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING state_id]
[parameters: (None, None, None, None, None, None, None, 1710505579.97539, 343944520, 907218, None, None, None, 0, <memory at 0x7f03620b41c0>, None, None, 2301)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2024-03-15 13:26:20.324 ERROR (Recorder) [homeassistant.components.recorder.util] The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2024-03-15T12:26:20.324250+00:00 in order to allow startup to proceed
2024-03-15 13:26:20.475 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id `button.ikea_of_sweden_tradfri_motion_sensor_identify` to `button.new_device_name_identify` because the new entity_id is already in use
2024-03-15 13:26:20.494 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id `binary_sensor.ikea_of_sweden_tradfri_motion_sensor_motion` to `binary_sensor.new_device_name_motion` because the new entity_id is already in use
koltlp commented 6 months ago

@CFenner core 2024.2.0, supervisor 2024.03 0

myxor commented 5 months ago

Happened to me as well. Any way to recover the historic entries from an old database backup?

tuomasva commented 5 months ago

This happens to me like everyday now.

Wondering what is the common denominator here for us? Should we cross-examine our plugins list or something?

I'm gonna migrate over to rasperry pi 5 anyway soon, not sure if that is gonna affect this in anyway but I was gonna setup the whole thing again from the ground up.

koltlp commented 5 months ago

This happens to me like everyday now.

Wondering what is the common denominator here for us? Should we cross-examine our plugins list or something?

I'm gonna migrate over to rasperry pi 5 anyway soon, not sure if that is gonna affect this in anyway but I was gonna setup the whole thing again from the ground up.

@tuomasva Do you mean it happens frequently even if you don't remove or add Zigbee devices? What HA version?

tuomasva commented 5 months ago

This happens to me like everyday now. Wondering what is the common denominator here for us? Should we cross-examine our plugins list or something? I'm gonna migrate over to rasperry pi 5 anyway soon, not sure if that is gonna affect this in anyway but I was gonna setup the whole thing again from the ground up.

@tuomasva Do you mean it happens frequently even if you don't remove or add Zigbee devices? What HA version?

Yes, i don't have any zigbee devices or hubs. 

Core 2024.4.2
Supervisor 2024.04.0
Operating System 12.1
Frontend 20240404.1
bdraco commented 4 months ago

Has this happened to anyone recently?

CFenner commented 4 months ago

I just renamed an entity again and had no problems this time 👍