Corrupt database due to purge action at night

Configuration
1

Hi All,

Every night at 4:12 I have the same issue the last couple of days.
Something is wrong with my database, but I can not figure out what.
I have searched and read previous posts but I still do not know what to do or how to fix the issue.
My Database skills are almost non existent, but with help and documentation I will try to fix this.
My system is up to date with all updates.
Database size is 3,25 GB (I realy want to keep all my energy data)
If any other info is needed then I try to provide.

I can reproduce the issue if I do:

service: recorder.purge
data:
  repack: false
  apply_filter: false

This is from the log:

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:891
integration: Recorder (documentation, issues)
First occurred: 4:12:11 AM (1 occurrences)
Last logged: 4:12:11 AM

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.13/site-packages/sqlalchemy/engine/cursor.py", line 1136, in fetchall
    rows = dbapi_cursor.fetchall()
sqlite3.DatabaseError: database disk image is malformed

The above exception was the direct cause of the following exception:

I have tried one solution suggested in an other thread, to no success, my repaired fix file is zero bytes

sqlite3 ./home-assistant_v2.db ".dump" | sqlite3 ./home-assistant_v2.db_fix
2

the method you listed just makes sql dump of database to pipe it to a new one.
Try recover method (Recovering Data From A Corrupt SQLite Database)

All those operations have to be performed with Home Assistant turned off.
Also do not access this db through samba (not even for read only) if HA is active.
Just in case you don’t know it.

Sqlite is most fragile db I know. And if damaged, it’s really hard to recover, especially without HA outage and letting historical data there.

It should be stated in red color in HA docs: sqlite is not reliable enough to secure historical data.

People should be encouraged to use other dbs for this task. influx or timescaledb

3

Hi,

Thanks for you answer.
I tried your suggestion on a backup I had downloaded locally. I unzipped the file and took out the DB file.

sqlite3 ./home-assistant_v2.db ".recover" | sqlite3 ./home-assistant_v2.db.fix

This resulted in a shrink from 3,25GB to 2,7GB.
I need to see the actual result when I will do this on my recent DB.
Will report back later today.

4

Hi,

Did all actions,

On the console of the server I ran core stop command, then did a copy over Samba share to my laptop of the DB file, moved all DB and related logfiles to a new folder on the server as a backup.
On the laptop I ran the recover command, did a rename and then copied the file over to the server again.
On the server console did core start
When I have a look at my Energy history I still see all days with the data as expected.

As a test I also ran the recorder purge command from the Developer Tools, Actions tab and all still looks good.

I will keep a close eye on the DB, during the day and for sure tonight after 4 O clock.

5

Hi,

Issue seems resolved.
All nightly actions were performed without database corruption and loss of my Energy data.

6

Glad to hear that.
However if you rely on historical data, I encourage you to replicate this important information to a database which give better guarantees and even better, is designed to store timeseries data.

Recently I started to think about writing some tutorial of using timescaledb. Cannot give deadlines, but since i would like to share it to db chapter in my company, it shouldn’t take long

1 Like