Fix corrupted / malformed SQLite database (home-assistant_v2.db)

I restored a snapshot when switching hardware / sd card (HASS OS on Pi 2 B 32 bit to HASS OS on Pi 3 B+ 64 bit).

Result:

The home-assistant_v2.db from a restored snapshot is malformed or corrupted. This leads so error messages and not functional Home Assistant (history graphs not generated/shown, sensor state changes aren´t stored etc.).

I tested it several times, in every test case the SQLite database got corrupted. Either during backup (creating the snapshot) or restore process.

sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
2020-12-19 16:08:43 ERROR (SyncWorker_6) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
(Background on this error at: http://sqlalche.me/e/13/4xp6)

As I was searching the HA community I quickly discovered this is a common issue, there are lots of topics related to this problem, seems to be a MASSIVE one.

The “solution” (in my opinion a workaround, at best) proposed in almost all those topics: delete the database, start from scratch. I was really wondering how everyone can accept the “just delete that damn sqlite db and your good to go!” workaround so all history data is gone.

For me personal it´s just not acceptable to loose all my history data every now and then. Especially when you´re not yet into putting medium and long-term data in e. g. InfluxDB. You never know when you´ll need to restore a snapshot, that´s what backups are for: being prepared for the worst case. So currently that´s not a reliable backup and restore progress at all.

So I decided to go the hard, not that quick way to keep my database (took me 10 to 15 minutes, saving many days of data):

I ran a “pragma integrity_check;” on it and several findings were returned:
grafik

When I take a look at the home-assistant_v2.db from the running system before the snapshot, the integrity check passes (I had access to the original SD card):
grafik

Assumption: either the snapshot creation process is faulty or the restore snapshot process harms the database. It´s very unlikely it´s a storage issue (“corrupt SD cards”), especially cause in my case they´re brand new.

In theory there should basically be three ways to fix this issue WITHOUT loosing all data (no need to create a new database):

  1. Manually backup the home-assistant-v2.db next to the snapshot. Maybe automate it. Use that one for restore purposes.

  2. Perform a “pragma integrity_check;” on the corrupted database and fix those issues.
    (see https://serverfault.com/questions/8048/how-can-i-verify-that-a-sqlite-db3-file-is-valid-consistent)

  3. Re-create the database while keeping its data:
    (source: https://zngguvnf.org/2019-12-17--homeassistant-backup-and-restore.html - see also direct post links DB image malformed. How to fix it? and DB image malformed. How to fix it? for commands on a running system)
    Copy your (corrupt) database (home-assistant_v2.db) to your local linux system and do:
    “sqlite3 home-assistant_v2.db “.dump” | sqlite home-assistant_v2.db_fixed”
    Now delete your old (corrupt database) and replace it with home-assistant_v2.db_fixed. To do that delete the corrupt one and rename home-assistant_v2.db_fixed to home-assistant_v2.db.

I personally succeeded in using option #1 in an advanced version: luckily I had access to the original disk (SD card) so I just grabbed the database, performed an integrity check (passed) and put that database back in place, restarted HA and everything´s fine again, meaning: all recorder, history and logbook data is there.

— Some developers could take a look at it (why is the database corrupted that often?) and especially check the snapshot creation and restore progress. —


2 Likes

@Eraser
Wow! I respect and salute your dauntless deep dive into the matter.

Short fix, not to go trough this hassle is to install mariadb or mysql and be done with it (the hassio supervisor has it as addon)

mariadb addon config

databases:
  - homeassistant
logins:
  - username: homeassistant
    password: StOeMeLlEnDgHaFrEtMiJnDbOp!
rights:
  - username: homeassistant
    database: homeassistant

config yaml

recorder:
  db_url: mysql://homeassistant:[email protected]/homeassistant?charset=utf8

et voila!
Bonus; much faster load time for history

4 Likes

The root-cause behind this issue is likely a way how HA supervisor performs the backup. It just copy files incl. db file while HA is running storing data into that. It ends with data inconsistency aka malformed database file.

You succeeded to use db from your old card because this instance of HA had been properly stopped, and no process were modifying data in this database at time you were copying this file out.
I suppose you can succeed with creating 100% working backup by stopping HA first, then creating backup.

Just reminder: no database should be backed up this way (copying of its files) during database operations. Even transactional databases may be damaged this way (the probability is lower but its still possible). It’s valid for MiariaDB, Postgres, MS SQL, Oracle, Sybase…
Such a binary copy can be performed only if database is stopped (or at least with no operations performed during copy process - still with some risk). Such databases offer option to create consistent backup during runtime, usually by calling special command.

1 Like

Fully agree. There is for sure a better way to snapshot the SQLite database. It’s just that no one cares cause „it’s just the corset database, throw it away, go loose some days of data“. I‘m just a bit tired otherwise I‘d create a feature request for this on GitHub. Meanwhile I dump my important data to InfluxDB in parallel so that one has become more important.

For the moment I‘m done with it and as soon as better hardware with more RAM is finally shipped I might go for the MySQL/MariaDB addon, but would first need to find out how snapshots are created for those addons/containers - don’t want to end up the same way like with SQLite.

Good work!
For me it didn’t work with the “sqlite3 home-assistant_v2.db “.dump” | sqlite home-assistant_v2.db_fixed” command. Ended with an empty file.
What worked for me was “sqlite3 home-assistant_v2.db “.recover” | sqlite home-assistant_v2.db_fixed”
(result: almost same size as original and no errors when copied back to HA)

2 Likes

Thanks for this, will give it a go. Part of my problem is that I do have long-term values, especially from utility_meter values (and the underlying sensors) that I do not want to lose.

I see in my case the problem is in the events table – is there any way to only recreate the events table and keep the rest?

Delete all from the events table? or drop table and recreate? https://www.home-assistant.io/docs/backend/database/

This worked perfectly – thanks! My recovered DB ended up about a third of the original, but
a) it works, and,
b) the state history is there, and so are my utility_meter values.

Thanks!!

That´s not necessarily because of loosing data, but possibly due to the former version not been repacked for some time. Purging only limits growth, repacking throws away old data according to the purge_keep_days setting (https://www.home-assistant.io/integrations/recorder).