Panowie, dzisiaj bez żadnej przyczyny szlag trafił całą historię w HA, najbardziej szkoda mi dwuletniej historii z panela energii, gdzie mam podpiętą fotowoltaikę. Nic w tym czasie nie robiłem, nic nie aktualizowałem, dopiero teraz zauważyłem jej brak.
Czy wgranie kopii zapasowej przywróci historię ? kopia była robiona dzisiaj w nocy, a historia zniknęła o 13.00.
A co mówią logi ? jakieś błędy z tym związane ?
tak, jeśli masz poprawny backup z nocy
ale oczywiście warto zajrzeć w logi,
bo zniknięcie historii bez przyczyny jest skrajnie mało prawdopodobne
(aktualizacja i baza nadal się przelicza?)
bo sprawdzeniu logów i zachowaniu wszystkiego co podejrzane do rozważenia jest restart HA oraz ewentualnie całego hosta
W logach nic podejrzanego nie zauważyłem. Restart samego HA a później całego hosta nic nie pomógł.
Wgrałem nocny backup i póki co jest ok.
Dzięki za zainteresowanie.
Edit:
Dzisiaj około godz. 7:33 ponownie cała historia poszła do kosza z nieznanych mi przyczyn .
Mniej więcej w tym czasie parowałem czujnik temp. i wilg. Zigbee (ZHA). Nie wiem czy to mogło być powodem ?
W logach znalazłem takie coś, możecie zerknąć ?
Logger: homeassistant.components.recorder.core Source: components/recorder/core.py:925 Integration: Recorder (documentation, issues) First occurred: 07:33:33 (1 occurrences) Last logged: 07:33:33
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, None, None, None, None, None, None, 1711607613.137662, 1086693363, 11975701, None, None, None, 0, <memory at 0x7fee43fbbac0>, None, None, 2836)] (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, None, None, None, None, None, None, 1711607613.137662, 1086693363, 11975701, None, None, None, 0, <memory at 0x7fee43fbbac0>, None, None, 2836)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Logger: homeassistant.components.recorder.statistics Source: components/recorder/statistics.py:742 Integration: Recorder (documentation, issues) First occurred: 07:33:33 (4 occurrences) Last logged: 07:33:33
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.temp_lodowka_bateria', 'sensor.lumi_lumi_weat
Baza zbiera dwa lata - sprawdź czy masz miejsce na dysku aby to pomieścić (albo dysk kończy żywot)
Odtwórz bazę z backup z sprawdź jej rozmiar
Statystyki długoterminowe wprowadzono w sierpniu 2021 i jak na razie nie zauważyłem takiego ograniczenia
Baza sqlite3, która zawiera te dane ma koło 400MB (to nie jest jakaś rozbudowana instalacja HA, na sporej instalacji mam bazę sqlite3 o rozmiarze koło 1.5GB, co jest aktualnym rozmiarem, bo ze 2 lata temu przed optymalizacjami w HA miała już koło 4GB, a ta z której jest obrazek bodajże miała wtedy coś koło 2GB).
Wydaje mi się, że związek z utratą historii ma czujnik temp/wilg. Zigbbe.
Co jakiś czas tracę połączenie z tym czujnikiem i jedynym sposobem na odzyskanie jest ponowne parowanie.
Przed chwilą (godz. 11:16). byłem zmuszony do ponownego sparowania czujnika z ZHA i w tym samym czasie utraciłem historię danych.
Czy jest to możliwe ?
Poniżej logi:
wpisz lub Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:139
Integration: Recorder (documentation, issues)
First occurred: 09:37:20 (8 occurrences)
Last logged: 11:16:23
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.temp_lodowka_bateria', 'sensor.lumi_lumi_weather_bateria', '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.temp_lodowka_cisnienie', 'sensor.lumi_lumi_weather_cisnienie', '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.temp_lodowka_temperatura', 'sensor.lumi_lumi_weather_temperatura', '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.temp_lodowka_wilgotnosc', 'sensor.lumi_lumi_weather_wilgotnosc', '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.temp_lodowka_bateria', 'sensor.lumi_lumi_weather_bateria', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Logger: homeassistant.components.recorder.statistics
Source: components/recorder/statistics.py:742
Integration: Recorder (documentation, issues)
First occurred: 09:37:20 (8 occurrences)
Last logged: 11:16:23
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.temp_lodowka_bateria', 'sensor.lumi_lumi_weather_bateria', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Logger: homeassistant.components.recorder.core Source: components/recorder/core.py:925 Integration: Recorder (documentation, issues) First occurred: 11:16:27 (1 occurrences) Last logged: 11:16:27
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, None, None, None, None, None, None, 1711620983.36214, 1086469858, 11975701, None, None, None, 0, <memory at 0x7f819ca38400>, None, None, 2836)] (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, None, None, None, None, None, None, 1711620983.362
wklej tutaj kod
Trudno wyczuć, czy to przyczyna, zazwyczaj taki cyrk z bazą może być związany z zanikiem zasilania.
Na obrazku pokazujesz, że masz uszkodzoną bazę.
Jeśli uważasz, że przyczyna leży po stronie tego czujnika, to przede wszystkim usuń jego statystyki w momencie gdy nie jest sparowany (jak rozumiem dopiero jego ponowne dodawanie rozwala bazę).
Ale najpierw przygotuj wszelkie możliwe logi i zgłoś issue do recordera.
Jeśli usunięcie statystyk nie umożliwi dodania go poprawnie bez uszkodzenia bazy, to ja bym rozważał zaprzestanie używania tego czujnika.
I jeszcze jedno czy przypadkiem backup bazy nie pochodzi ze starszej wersji HA?, jeśli tak to cofnij wersję HA przed przywróceniem backupu, a następnie zaktualizuj HA core, utwórz sobie tez świeży pełny backup z instalacji w stanie poprawnego działania (więc zapewne bez tego czujnika).
Tak z tego wynika, gdy ponownie go dodaję to rozwala bazę.
Dzisiaj dwukrotnie miała miejsce taka sytuacja, więc raczej nie jest to zbieg okoliczności.
A podpowiesz jak usunąć statystyki tego czujnika ?
Narzędzia deweloperskie → Statystyki
Przy okazji usuń tez inne statystyki, które noszą znamiona uszkodzonych (lub je napraw jeśli jest taka możliwość).
Może przegapiłeś jakąś breaking change?
Tak swoją drogą HA kasuje=zmienia nazwę uszkodzonej bazy danych przy rozruchu, więc od razu ciśnie mi się na usta pytanie z jakiej paki ten rozruch?