- I am trying updating the HA on my rpi4, which is running on docker. From 2023.3 to 2023.5. I re-pull the image and recreate the container. After serveral minutes, I logged in with no issue. But soon i found all history were gone! I ran to check the log, it shows DB failed to migrate.
2023-05-18 09:24:38.133 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade from schema version: 35 to: 41
2023-05-18 09:24:38.149 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_id_bin, context_user_id_bin, context_parent_id_bin to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:38.196 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_id_bin, context_user_id_bin, context_parent_id_bin to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:38.213 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_context_id_bin` to table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:38.445 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_states_context_id_bin` to table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:41.689 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 36 done
2023-05-18 09:24:41.695 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns event_type_id to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:41.700 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type_time_fired_ts` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:41.820 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_event_type_id_time_fired_ts` to table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:42.379 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 37 done
2023-05-18 09:24:42.382 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns metadata_id to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:42.387 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_states_metadata_id_last_updated_ts` to table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.479 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 38 done
2023-05-18 09:24:45.480 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type_time_fired_ts` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.484 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.488 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type_time_fired` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.492 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_time_fired` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.496 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_context_user_id` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.500 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_context_parent_id` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.503 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id_last_updated` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.508 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_last_updated` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.513 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.521 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_context_user_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.526 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_context_parent_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.530 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_created_domain` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.535 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id_created` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.539 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `states__state_changes` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.543 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `states__significant_changes` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.548 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id_created` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.552 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_statistic_id_start` from table `statistics`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.556 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_short_term_statistic_id_start` from table `statistics_short_term`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.562 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 39 done
2023-05-18 09:24:45.563 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_event_type_id` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.567 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_metadata_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.572 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_metadata_id` from table `statistics`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:45.598 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_statistics_short_term_metadata_id` from table `statistics_short_term`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:46.252 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 40 done
2023-05-18 09:24:46.254 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_event_types_event_type` to table `event_types`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:46.258 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_event_types_event_type already exists on event_types, continuing
2023-05-18 09:24:46.260 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_states_meta_entity_id` to table `states_meta`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:46.264 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_states_meta_entity_id already exists on states_meta, continuing
2023-05-18 09:24:46.270 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 41 done
2023-05-18 09:24:46.915 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: event_types.event_type
[SQL: INSERT INTO event_types (event_type) VALUES (?) RETURNING event_type_id]
[parameters: ('service_registered',)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2108, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: event_types.event_type
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 130, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1474, in migrate_event_type_ids
session.flush() # Assign ids
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 4153, in flush
self._flush(objects)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 4289, in _flush
with util.safe_reraise():
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 4250, in _flush
flush_context.execute()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 467, in execute
rec.execute(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 644, in execute
util.preloaded.orm_persistence.save_obj(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
_emit_insert_statements(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 1133, in _emit_insert_statements
result = connection.execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1413, in execute
return meth(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1836, in _execute_context
return self._exec_insertmany_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2108, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: event_types.event_type
[SQL: INSERT INTO event_types (event_type) VALUES (?) RETURNING event_type_id]
[parameters: ('service_registered',)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2023-05-18 09:24:46.951 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detected: (sqlite3.IntegrityError) UNIQUE constraint failed: event_types.event_type
[SQL: INSERT INTO event_types (event_type) VALUES (?) RETURNING event_type_id]
[parameters: ('service_registered',)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2108, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: event_types.event_type
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 889, in _process_one_task_or_recover
return task.run(self)
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 416, in run
if not instance._migrate_event_type_ids(): # pylint: disable=[protected-access]
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1238, in _migrate_event_type_ids
return migration.migrate_event_type_ids(self)
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 608, in wrapper
return job(instance, *args, **kwargs)
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1474, in migrate_event_type_ids
session.flush() # Assign ids
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 4153, in flush
self._flush(objects)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 4289, in _flush
with util.safe_reraise():
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 4250, in _flush
flush_context.execute()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 467, in execute
rec.execute(self)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 644, in execute
util.preloaded.orm_persistence.save_obj(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
_emit_insert_statements(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 1133, in _emit_insert_statements
result = connection.execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1413, in execute
return meth(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1836, in _execute_context
return self._exec_insertmany_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2108, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: event_types.event_type
[SQL: INSERT INTO event_types (event_type) VALUES (?) RETURNING event_type_id]
[parameters: ('service_registered',)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2023-05-18 09:24:47.044 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.2023-05-18T01:24:47.044440+00:00 in order to allow startup to proceed
2023-05-18 09:24:48.569 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_context_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:48.578 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_context_id` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
2023-05-18 09:24:48.598 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id_last_updated_ts` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
- I suspect problem is the duplication in the event_types table, I edited the table and try again. But no help, even I drop the table, it consist give me the same error.