Database corruption at about 2.6Gb, there is a fix?

Installation Home Assistant OS
1

Hello
I tried to search for this issue but seems I cant get a valid fix/solution or a suggestion on how to avoid this.

I’m using HA OS from about 2 years now, but I always noticed that every a couple of months the old hystorical data from the sensors got lost. At first it wasnt so important because I never needed them, but now yes I need the hystorical energy data.

So i discoverd that everytime as the .db2 file got at about 2.6Gigabyte in space, there is a message in the log that says

Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed

and then

The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2024-07-25T02:12:22.791177+00:00 in order to allow startup to proceed

So the database starts fresh.

There is a suggestion on how to avoid that or fix it?
Seems that this happens due to the space that the db took on the disk, but I have more that 100GB free on that disk (and Infact I just noticed that there are more than 5 old corrupeted db files, each one about 2.5 / 2.6 GB but also one at justt 400 MB )

Thanks for any help

2

Hm, that is interesting, this seems to be well below any theoretical limit of SQLite from what I can tell (which seems to be 140TB or thereabout).

Maybe there is an architecture dependent limit? What hardware are you running on? Is your installation 32-bit or 64-bit?

3

I have a similar situation. see below, my database is 7,343,044 KB (7GB)
Running home assistant on a Nuc i5 proxmox with 4GB of ram I have 11GB free storage.

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:395
integration: Recorder (documentation, issues)
First occurred: 06:40:22 (1 occurrences)
Last logged: 06:40:22

The recorder backlog queue reached the maximum size of 93473 events; usually, the system is CPU bound, I/O bound, or the database is corrupt due to a disk problem; The recorder will stop recording events to avoid running out of memory

cheers

Lawrence

4

Hm, that is probablyi simply because the database has grown too big? :thinking:

In general, too many state changes can lead to databases growing that big. Try to lower the time state changes get stored. Statistics should be used for long term data.

You can also disable certain entities from getting recorded, see Recorder - Home Assistant. I exclude SNMP entities, which change state every couple of seconds.

5

HA OS is installed in a VM on Proxmox, so I think it’s 64 bit.
The storage is on a dedicated SSD partition just for HA (128 GB), but nothing changed when was installed on a “virtual disk” .

6

Just an update with an important and “strange” finding:

After I wrote this forum post, I restored a previous backup (I make a daily backup) from the previous day (24 July).
All was working fine.
Now I checked again and it happened again: the db got corrupted and started fresh.

But this time the “corrupted” db file size is the bigger I ever saw, almost 2.8 GB.

So I searched for other details and noticed that the in the error log when I wrote this post, the error was recorded at 4:12 AM and now, in the “new” error log, the corruption happened again at the same time, 4:12 AM !

So maybe something that runs on the system at about 4:00 AM is causing the corruption, but NOT everyday, only “some day”.

Thinking that the db restored from backup was full of “XDays - 1” of data, I’m wondering if it’s something that happens, maybe, at 4:12 every XX days of data recorded in the db.

I know that HA has an automatic “db purge” option, but I never setted it up or changed anything, so I dont think should be this… But I’m not an expert on this, so I would appreciate any other help or clue to try to find what happens at 4:12 each time the db got corrupted.

Anyway, I’m thinking to move to another DB, I found some examples on how to move to MariaDB or InfluxDB and since anyway I’m quite condamned to loose all the previous data, maybe it’s the right time to proceed.

Unfortunately I deleted all the old corrupted db files (I found 4 of them) so I cant check the dates to see if there is a regular time (like any 10 days or something like that) for the corruption to happens at 4:00 AM, but is something that maybe is a clue…

7

The 4:12 thing suggests it’s part of a known and well-reported issue:

It’s a long thread, read the whole thing. The link above should bring you to a post describing the fixes, and the fact that they won’t all be there until 2024.8.