sqlite3.DatabaseError: database disk image is malformed

Hi,
Does anybody able to see from log below why my sqlite database disk image is malformed?
I had the issue already two days before and was able to restore my HA yesterday.
Unfortunately DB has the corruption again and I don’t know why this happen.
Yes, the database is huge (~10 GB) as energy and other data has been collected since the beginning of 2022.
I have already come to terms with the fact that I will probably have to setup a completely fresh HA and do without my collected data.
But to avoid the same issue for a new installation, it would be interesting if someone could identify the root cause from the log.

Thanks!

2024-03-12 00:05:55.562 WARNING (MainThread) [custom_components.samsungtv_smart.media_player] media_player.samsung_tv_gq32q50r - Connection to SmartThings restored
2024-03-12 00:41:40.137 ERROR (MainThread) [aiohttp.server] Error handling request
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/aiohttp/web_protocol.py", line 350, in data_received
messages, upgraded, tail = self._request_parser.feed_data(data)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "aiohttp/_http_parser.pyx", line 557, in aiohttp._http_parser.HttpParser.feed_data
aiohttp.http_exceptions.BadHttpMessage: 400, message:
Data after `Connection: close`:
b'HELP'
^
2024-03-12 03:20:17.016 ERROR (MainThread) [homeassistant.components.xiaomi_miio] Timeout fetching Roborock data
2024-03-12 04:22:07.317 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
(Background on this error at: https://sqlalche.me/e/20/4xp6)
Traceback (most recent call last):
File "/usr/local/lib/python3.12/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 86, in purge_old_data
has_more_to_purge |= _purge_states_and_attributes_ids(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/purge.py", line 193, 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 253, in _select_state_attributes_ids_to_purge
).all():
^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/result.py", line 1379, in all
return self._allrows()
^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/result.py", line 548, in _allrows
rows = self._fetchall_impl()
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/result.py", line 2270, in _fetchall_impl
return list(self.iterator)
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/loading.py", line 219, in chunks
fetch = cursor._raw_all_rows()
^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/result.py", line 540, in _raw_all_rows
rows = self._fetchall_impl()
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 2103, in _fetchall_impl
return self.cursor_strategy.fetchall(self, self.cursor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 1139, in fetchall
self.handle_exception(result, dbapi_cursor, e)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 1080, in handle_exception
result.connection._handle_dbapi_exception(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2335, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.12/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: https://sqlalche.me/e/20/4xp6)
2024-03-12 04:22:07.342 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed
(Background on this error at: https://sqlalche.me/e/20/4xp6)
Traceback (most recent call last):
File "/usr/local/lib/python3.12/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 929, in _process_one_task_or_event_or_recover
return task.run(self)
^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 112, in run
if purge.purge_old_data(
^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 643, in wrapper
return job(instance, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/purge.py", line 86, in purge_old_data
has_more_to_purge |= _purge_states_and_attributes_ids(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/purge.py", line 193, 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 253, in _select_state_attributes_ids_to_purge
).all():
^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/result.py", line 1379, in all
return self._allrows()
^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/result.py", line 548, in _allrows
rows = self._fetchall_impl()
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/result.py", line 2270, in _fetchall_impl
return list(self.iterator)
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/loading.py", line 219, in chunks
fetch = cursor._raw_all_rows()
^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/result.py", line 540, in _raw_all_rows
rows = self._fetchall_impl()
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 2103, in _fetchall_impl
return self.cursor_strategy.fetchall(self, self.cursor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 1139, in fetchall
self.handle_exception(result, dbapi_cursor, e)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/cursor.py", line 1080, in handle_exception
result.connection._handle_dbapi_exception(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2335, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.12/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: https://sqlalche.me/e/20/4xp6)
2024-03-12 04:22:07.373 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-03-12T03:22:07.373286+00:00 in order to allow startup to proceed

I had this a few months ago when I tried to read the db using sqlite browser, which apparently affects the db even if you donot update any record.
The two times this happened I managed to repair it without a restore of the backup

sqlite3 mydata.db ".dump" | sqlite3 new.db

Thanks a lot vingerha!
I have tried also the following command:

sqlite3 mydata.db ".recover" | sqlite3 new.db

But both commands creates after a long run just an empty new.db file.
(yes, I’ve inserted for mydata.db the real filename of my db file)

yes…I made a mistake in copying … used .recover too and that worked for me.
So no choice then to go for the backup I guessx
Sorry