Hi.
As in the title still keep getting SQlite3 corrupted once a week.
I had that July last year and i read it could be due to emmc card start dying.
So to prevent this happening again i order new Sandisk 128gb emmc and also connect Sandisk x110 128gb ssd and connect through adapter to USB. Move system to SSD using standard migration tool. It was OK for few months but last month system keep crashing and getting corrupted sqlite3 database.
I have latest update and its HA OS installed.
I unplug ssd and on PC did ssd SMART check. No problems found and health is still 83%.
I manage to download logs but nothing obvious when corrupt happens.
Blockquote
2024-01-14 04:12:01.954 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
(Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation)
Traceback (most recent call last):
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py”, line 1135, in fetchall
rows = dbapi_cursor.fetchall()
^^^^^^^^^^^^^^^^^^^^^^^
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/util.py”, line 139, in session_scope
yield session
File “/usr/src/homeassistant/homeassistant/components/recorder/purge.py”, line 88, in purge_old_data
has_more_to_purge |= _purge_states_and_attributes_ids(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/usr/src/homeassistant/homeassistant/components/recorder/purge.py”, line 195, in _purge_states_and_attributes_ids
state_ids, attributes_ids = _select_state_attributes_ids_to_purge(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/usr/src/homeassistant/homeassistant/components/recorder/purge.py”, line 255, in _select_state_attributes_ids_to_purge
).all():
^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/result.py”, line 1390, in all
return self._allrows()
^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/result.py”, line 554, in _allrows
rows = self._fetchall_impl()
^^^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/result.py”, line 2293, in _fetchall_impl
return list(self.iterator)
^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/loading.py”, line 215, in chunks
fetch = cursor._raw_all_rows()
^^^^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/result.py”, line 546, in _raw_all_rows
rows = self._fetchall_impl()
^^^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py”, line 2102, in _fetchall_impl
return self.cursor_strategy.fetchall(self, self.cursor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py”, line 1139, in fetchall
self.handle_exception(result, dbapi_cursor, e)
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py”, line 1080, in handle_exception
result.connection._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/cursor.py”, line 1135, in fetchall
rows = dbapi_cursor.fetchall()
^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
(Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation)
2024-01-14 04:12:04.998 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed
(Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation)
Traceback (most recent call last):
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py”, line 1135, in fetchall
rows = dbapi_cursor.fetchall()
^^^^^^^^^^^^^^^^^^^^^^^
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 113, in run
if purge.purge_old_data(
^^^^^^^^^^^^^^^^^^^^^
File “/usr/src/homeassistant/homeassistant/components/recorder/util.py”, line 625, in wrapper
return job(instance, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/usr/src/homeassistant/homeassistant/components/recorder/purge.py”, line 88, in purge_old_data
has_more_to_purge |= _purge_states_and_attributes_ids(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/usr/src/homeassistant/homeassistant/components/recorder/purge.py”, line 195, in _purge_states_and_attributes_ids
state_ids, attributes_ids = _select_state_attributes_ids_to_purge(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/usr/src/homeassistant/homeassistant/components/recorder/purge.py”, line 255, in _select_state_attributes_ids_to_purge
).all():
^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/result.py”, line 1390, in all
return self._allrows()
^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/result.py”, line 554, in _allrows
rows = self._fetchall_impl()
^^^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/result.py”, line 2293, in _fetchall_impl
return list(self.iterator)
^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/loading.py”, line 215, in chunks
fetch = cursor._raw_all_rows()
^^^^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/result.py”, line 546, in _raw_all_rows
rows = self._fetchall_impl()
^^^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py”, line 2102, in _fetchall_impl
return self.cursor_strategy.fetchall(self, self.cursor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py”, line 1139, in fetchall
self.handle_exception(result, dbapi_cursor, e)
File “/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py”, line 1080, in handle_exception
result.connection._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/cursor.py”, line 1135, in fetchall
rows = dbapi_cursor.fetchall()
^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
(Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation)
2024-01-14 04:12:06.496 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.2024-01-14T04:12:06.495956+00:00 in order to allow startup to proceed
When it happens i recover my database from daily backups but its not the way it should work.
Any ideas?
I did not migrate to MariaDB as its not faultless and sqlite3 has been significantly updated. Also dont want to loose actual history.