I have made several topics over the past few weeks in connection with my Energy Dashboard, Recorder settings, Long-term statistics. The common point has always been that for some unexplainable reason the data stored was always missing elements or were corrupted for some reason. I always thought that I was making some mistakes in connection with the setup of the given instance/integration, but I am afraid that that the issue is much more simple albeit much more severe. I am running HA on a VM, so in all these cases the solution was always to do a rollback, but this is unfortunately not sustainable. Could you help me understand what is happening and what is causing it?
Symptoms:
- Energy Dashboard values reset overnight
- Sensor history goes missing all of a sudden
- HA DB size goes to absolute 0 - as if something would fully wipe the DB
- All Long-Term Statistics are gone (this is what confuses me the most)
Also my Recorder setup
recorder:
exclude:
domains:
- device_tracker
- media_player
- camera
- light
- automation
entity_globs:
- binary_sensor.doorbell_*
- binary_sensor.front_door_*
- binary_sensor.garage_*
entities:
- sensor.memory_free
- sensor.memory_use_percent
- sensor.processor_use
- sensor.ble_mac_50765cb7d9ea4e2199a4fa879613a492
- sensor.p1_meter_wi_fi_strength
Digging into the logs, I found 2 entries that seem to be related - could you help me understand how I could overcome this periodic reset?
Summary
Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:912
Integration: Recorder (documentation, issues)
First occurred: 03:06:39 (1 occurrences)
Last logged: 03:06:39
Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO state_attributes (hash, shared_attrs) VALUES (?, ?) RETURNING attributes_id] [parameters: (2997874097, '{"state_class":"total_increasing","source":"sensor.p1_meter_5c2faf042e4c_total_power_import","status":"collecting","last_period":"32.505","last_valid ... (103 characters truncated) ... 06465+00:00","unit_of_measurement":"kWh","device_class":"energy","icon":"mdi:transmission-tower-export","friendly_name":"Electricity import - Daily"}')] (Background on this error at: https://sqlalche.me/e/20/4xp6)
Traceback (most recent call last):
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2112, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
cursor.execute(statement, parameters)
sqlite3.DatabaseError: database disk image is malformed
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 912, in _process_one_task_or_recover
return task.run(self)
^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 305, in run
instance._commit_event_session_or_retry()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1163, in _commit_event_session_or_retry
self._commit_event_session()
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1183, in _commit_event_session
session.commit()
File "/usr/local/lib/python3.11/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.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/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.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1231, in _prepare_impl
self.session.flush()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4312, in flush
self._flush(objects)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4447, in _flush
with util.safe_reraise():
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4408, in _flush
flush_context.execute()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
rec.execute(self)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
util.preloaded.orm_persistence.save_obj(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
_emit_insert_statements(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 1136, in _emit_insert_statements
result = connection.execute(
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
return meth(
^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
return self._exec_insertmany_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2120, in _exec_insertmany_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2112, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
[SQL: INSERT INTO state_attributes (hash, shared_attrs) VALUES (?, ?) RETURNING attributes_id]
[parameters: (2997874097, '{"state_class":"total_increasing","source":"sensor.p1_meter_5c2faf042e4c_total_power_import","status":"collecting","last_period":"32.505","last_valid ... (103 characters truncated) ... 06465+00:00","unit_of_measurement":"kWh","device_class":"energy","icon":"mdi:transmission-tower-export","friendly_name":"Electricity import - Daily"}')]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
Summary
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:347
Integration: Recorder (documentation, issues)
First occurred: 03:06:39 (1 occurrences)
Last logged: 03:06:39
The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2023-12-19T02:06:39.299811+00:00 in order to allow startup to proceed