Database corrupted while updating

  • 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.

Hi Carl,

I had the same issue like you. The update was failing when upgrading 2023.3.6 to 2023.5.3 (to db version 41).
Im running HA in a VM environment and had the luxury to be able to backup and restore easily.
What i did is that i have not used the GUI to update from 2023.3.6 to 2023.5.3 but to use the Terminal & SSL addon.
The command was the following:
Example: “ha core update --version 2023.4.6”
The solution for me was to update in steps. Maybe it’s not necessary to update in so many steps but i wanted to be carefull. I have done the following updates. 2023.3.6 → 2023.4.6 → 2023.5.1 → 2023.5.2 → 2023.5.3. I always checked if the DB gets corrupted and checked the core logs.

What i found intresting is that by updating from 2023.3.6 to 2023.4.6 the DB was updated from 35 to 41 without error. When i have done the update via GUI directlty to 2023.5.3 this db update step to 41 was failing.

Looks like I’m not the only one having this problem… So maybe it’s a bug in 2023.5? Great research, thanks!

I have the same problem:

2023-05-22 13:07:36.899 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 40 done
2023-05-22 13:07:36.901 INFO (Recorder) [homeassistant.components.recorder.migration] Upgrading recorder db schema to version 41
2023-05-22 13:07:36.902 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-22 13:07:36.908 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_event_types_event_type already exists on event_types, continuing
2023-05-22 13:07:38.586 WARNING (Recorder) [homeassistant.components.recorder.db_schema] State attributes for sensor.inbox exceed maximum size of 16384 bytes. This can cause database performance issues; Attributes will not be stored
2023-05-22 13:07:36.910 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-22 13:07:36.913 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_states_meta_entity_id already exists on states_meta, continuing
2023-05-22 13:07:36.920 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 41 done
2023-05-22 13:07:49.870 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: Error Messages — SQLAlchemy 2.0 Documentation)
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: Error Messages — SQLAlchemy 2.0 Documentation)
2023-05-22 13:07:49.939 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: Error Messages — SQLAlchemy 2.0 Documentation)
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: Error Messages — SQLAlchemy 2.0 Documentation)
2023-05-22 13:07:50.338 INFO (Recorder) [homeassistant.components.recorder.pool.RecorderPool] Pool recreating
2023-05-22 13:07:50.362 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-22T12:07:50.362357+00:00 in order to allow startup to proceed

Is there a defect raised for it? I’m happy to revert to my last working HA version - 2023.2.3 (docker on Synology) and wait until this is fixed; if it’s indeed a bug in 2023.5.

1 Like

The open issue: recorder database migration failed after core update from 2023.3.x to 2023.5.2 · Issue #92650 · home-assistant/core · GitHub

1 Like

Thanks for posting that link.
I’m also in the same boat (but using mariadb).I’ve added additional details to the posting on Github.

1 Like