In the process of migrating from the old comfoconnect (integration via configuration.yaml) to the new HACS-based comfoconnect my database gets corrupted when I sort out the old and new entities (ie deleting old comfoconnect entities no longer in use and removing the '_2' suffix from the new comfoconnect entities. This has happened twice now (second time after restoring from a backup).
After a restart (when I have finished the cleaning up) I get the following fatal error(s) which may be due to my cleaning up or something else. What can I do? (I can provide more log entries at request)
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.comfoairq_inside_temperature', 'sensor.comfoairq_inside_temperature_2', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj) 2024-03-06 10:55:01.017 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_idsensor.comfoairq_inside_temperature_2tosensor.comfoairq_inside_temperaturebecause the new entity_id is already in use 2024-03-06 10:55:02.780 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, '2024-03-06T09:55:01+00:00', None, None, None, None, None, 1709718901.722167, 4807314, 579904, None, None, None, 0, <memory at 0x7f6d5c6d40>, None, None, 728)] (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
I have no clue what could be the cause of this. I don't do any manual database actions, so this seems like something that is caused by Home Assistant itself.
In the process of migrating from the old comfoconnect (integration via configuration.yaml) to the new HACS-based comfoconnect my database gets corrupted when I sort out the old and new entities (ie deleting old comfoconnect entities no longer in use and removing the '_2' suffix from the new comfoconnect entities. This has happened twice now (second time after restoring from a backup).
After a restart (when I have finished the cleaning up) I get the following fatal error(s) which may be due to my cleaning up or something else. What can I do? (I can provide more log entries at request)
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.comfoairq_inside_temperature', 'sensor.comfoairq_inside_temperature_2', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj) 2024-03-06 10:55:01.017 WARNING (Recorder) [homeassistant.components.recorder.entity_registry] Cannot migrate history for entity_id
sensor.comfoairq_inside_temperature_2to
sensor.comfoairq_inside_temperaturebecause the new entity_id is already in use 2024-03-06 10:55:02.780 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, '2024-03-06T09:55:01+00:00', None, None, None, None, None, 1709718901.722167, 4807314, 579904, None, None, None, 0, <memory at 0x7f6d5c6d40>, None, None, 728)] (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