sqlite3.DatabaseError: database disk image is malformed

Configuration
1

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
2

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
3

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)

4

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

5

Hi! I got the same error that was written in the title of this topic. I solved it by restoring a backup from a day before the incident. Now my problem is that it keeps happening at the very same time everyday. How could I identify the root problem? This is the error I’m getting:

sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
[SQL: UPDATE states SET old_state_id=? WHERE states.old_state_id IN (?, ?, ?, ?)]
[parameters: (None, 112328704, 112328705, 112328706)]
(Background on this error at: https://sqlalche.me/e/20/4xp6)

This is the short version of the error. The real one is the same just there’s like a 100 question marks and the same amount of increasing numbers.

Thanks in advance if someone can help me.

6

My personal view is that the error message will not help you identify the cause, it is a symptom. I myself have seen two sources sofar, 1. the database gets locked/accessed by a another application, in my case it is accessing it by an external db/query tool OR an improper integration (custom one).

7

I had exactly same error, now it’s the third time it happens. Always around the time of backup :
Backup says : March 21, 2025 at 04:57
DB Error : First occurred: 04:12:12

I did not try to restore the corrupted DB though. Should I stop HA Core before doing the SQL command ?

I’ll look further about this backup things, no other clue.

8

Hi,
I still have this problem every two to three months.
I’m pretty sure it’s related to the database purge run. This starts automatically at 04:00 in the morning and if I disable the cleanup (configuration.yaml → recorder: auto_purge: false), the problem goes away. I am also able to reproduce the error when I run the purge manually after a few weeks, then I get the same error.
Unfortunately, I am not able to find anything about which DB entries ultimately causes the error or where they came from.
I’ve also considered reinstalling the entire HA and completely reconfiguring it. However, that would take several days, and I don’t have the time.

9

Hello @Joo01
I have the same problem. Every month, always around the 9th at 4:12 AM (during the DB purge), the database gets corrupted. I always fix it by restoring the latest backup. I’ve already tried new hardware and reinstalled HA multiple times… but that didn’t help either. I am currently thinking of changing the DB.
Following version is installed:
Core: 2025.3.1
Supervisor: 2025.04.0
Operating System: 14.2

10

Do you think it needs to be a custom integration? I have only Frigate and Advanced Camera Card from HACS and some Add-ons. I wonder how we could identify the issue. What might be the common factor? I have several Home Assistant setups and this latest one is the only one having the issue and it is the most powerful of them as well with RPi5, 8 GB RAM and A2 card. There are probably the most number of entities as well.

Could the problem be DbStats add-on for me? hass-addons/dbstats/README.MD at 342ad5a8ead5f9b694e4767700468a74d84bbc00 · jehy/hass-addons · GitHub. I would assume it is reading only. However, there seems to be an issues of slow queries slow queries running in background after addon termination · Issue #6 · jehy/hass-addons · GitHub. Could that contribute to the issue?

Some things which might be different in this problematic setup are due to large number recorder updates from devices like RuuviTags and Shellys. The following might be special in this case.

  • recorder commit_interval: 30. Could a delayed write be an issue?
  • I have some template sensors configured
  • I have some number of helpers to combine entities to measure power and energy
  • I’m running purge.entities automation daily to remove selected noisy sensor data after 1 or 2 days
11

I have no clue, I can assume that it happens when an application locks a db(view), for me just opening (!) the db with dbbrowser already triggers this

12

I have create now a comman_line sensor (no glue if this is correct):

command_line:
  - sensor:
      name: SQLite_ERROR
      command: "grep -i 'database disk image is malformed' /config/home-assistant.log | tail -1"
      scan_interval: 300
      value_template: "{{ value | default('OK') }}"

and additional I have created an automatisation to send a notification to my cell, if the DB is corrupted again. Let see if I receive more details, when it happens the next time…