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…

Restore Steps using Backup (via Windows system)

1. Download Home Assistant Backup (Hopefully you have one but could try on existing file)
   1. \\unraid.local\rootShare\appdata\homeassistant\backups\be230b74.tar (Randomly named file)
2. Shutdown HomeAssistant Docker Container (UNRAID GUI)
3. Extract “home-assistant_v2.db” to HomeAssistant Directory (Overwritting existing DB)
   1. \\unraid.local\rootShare\appdata\homeassistant\home-assistant_v2.db
4. Startup HomeAssistant Docker Container (UNRAID GUI)
5. Go to: Developer Tools -> Services -> Recorder: Purge “Call Service”
6. Test and confirm if no issues with your energy data (you can also checkout the home-assistant.log)
   1. \\unraid.local\rootShare\appdata\homeassistant\home-assistant.log

Restore Steps using SQLite.exe Recovery (via Windows system)

1. 1. Download SQLite repair tool (This is in the “Precompiled Binaries for Windows” section, look for sqlite3.exe)
   2. Extract “sqlite3.exe” file to c:\temp
   3. Download corrupted Home Assistant DB to c:\temp
      1. \\unraid.local\rootShare\appdata\homeassistant\HHR0K5~S (Random file name)
   4. Rename bad DB “HHR0K5~S” to “home-assistant_v2.db_corrupt”
   5. Open CMD
   6. Run “cd c:\temp”
   7. Run: “sqlite3.exe home-assistant_v2.db_corrupt .recover > home-assistant_v2.sql”
   8. Run: “sqlite3.exe home-assistant_v2.db_corrupt .dump | sqlite3.exe home-assistant_v2.db”
   9. Shutdown HomeAssistant Docker Container (UNRAID GUI)
   10. Copy “home-assistant_v2.db” to HomeAssistant Directory (Delete or overwrite the existing DB)
       1. \\unraid.local\rootShare\appdata\homeassistant\home-assistant_v2.db
   11. Startup HomeAssistant Docker Container (UNRAID GUI)
   12. Go to: Developer Tools -> Services -> Recorder: Purge “Call Service”
   13. Test and confirm if no issues with your energy data (you can also checkout the home-assistant.log
       1. \\unraid.local\rootShare\appdata\homeassistant\home-assistant.log

   All sorted stats back and with purge data working again

   Example:

2. cd c:\temp
   sqlite3.exe home-assistant_v2.db_corrupt .recover > home-assistant_v2.sql

3.  For my scheduled backups I use the built in backup service: (Was previously using CA Appdata Backup on UNRAID but it stops the container I loose logging)

   alias: "Schedule: HA Backup"
   description: ""
   trigger:
   - platform: time
   at: "04:00:00"
   action:
   - service: backup.create
   data: {}
   - service: telegram_bot.send_message
   data:
   parse_mode: html
   title: 📆 <b>Schedule (Notification)</b> 📆
   message: HA Backup Complete
   disable_notification: true
   mode: single