My Problem:
Being a stats geek I was very disappointed after noticing that the HomeAssistant energy dashboard was missing some key information, the pretty little bar charts (Including all the history data with them). I could only see the previous few days worth of stats and nothing prior to this.
Some Home Assistant Info…
All entries in HomeAssistant are dumped into an SQLite DB file (By default) home-assistant_v2.db This file holds short term data (Which is purged every 10days by default) and long term “energy” data which makes up my colourful little charts.
When something goes bad with the home-assistant_v2.db file HomeAssistant will automatically recreate the file. When it recreates this file all your long term energy data goes “poof” with it in a black hole (Yeah don’t worry you just lost 18months of stats) The auto recreation has some pros and cons….
Pro: it get the system working again
Cons: If you don’t check your energy data daily to see missing stats then you can’t fix the issues without loosing stats.
My Setup…
I’m running UNRAID server with Docker Images, HomeAssistant Core edition (using the default SQLite DB) with external docker containers tied in (It’s not that bad without HA supervisor but it does add my pain when all the guides keep referencing supervisor config/guides). I’m a Windows fan boy and really don’t have much knowledge (or interest) typing Linux commands and CLI in when I could be looking at a GUI to click.
My issue…
After spotting the missing data (after 5 days!). I thought OK, backup restore! Pull the home-assistant_v2.db file from a backup (Daily scheduled automation for HA backup) and simply continue working… partly correct. The restored DB file worked but become corrupted again at 04:12 when the purge job kicks in. This resulting in a corrupted .db file, a new .db file being created and my pretty bar charts to be lost again (Groundhog Day) The DB errror can be seen in the home-assistant.log here:
2023-08-14 04:12:05.386 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
[SQL: UPDATE states SET old_state_id=? WHERE states.old_state_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
sqlite3.DatabaseError: database disk image is malformed
2023-08-14 04:12:05.410 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.2023-08-14T14:08:05.410328+00:00 in order to allow startup to proceed
After reading forum posts I found that although the DB work it was “malformed” which HA don’t like however it can be fixed…