An ungraceful restart of my Home Assistant host left me with a corrupted sqlite3 database. This is the database where Home Assistant core stores the history of all entity state and long-term statistics, like energy and power readings. I could have deleted the old database and started over, but a better solution is to fix it. Here’s how I fixed it by SSHing into Home Assistant.

Prerequisites:

  • You are able to SSH into Home Assistant, e.g., using the SSH & Web Terminal Addon.
  • You have enough free space on your Home Assistant box to temporarily hold the repaired copy of the database.

Run the SQLite3 “recover” command on your Home Assistant database

Log into the Home Assistant host via SSH, and change to the /config directory.

Check how much free space you have.

df -h /

Check the size of your corrupted database is smaller than the free space:

ls -lh home-assistant_v2.db

Stop Home Assistant core, so it’s not writing to the database while we’re operating on it.

ha core stop

(this might take a minute)

Run the sqlite3 .recover command to produce SQL statements to reconstruct the database, and pipe them into a new SQL database:

sqlite3 home-assistant_v2.db ".recover" | sqlite3 home-assistant_v2.db.fixed

This might produce some output corresponding to problems it found in the corrupted database.

Depending on the size of your database, recovery might take a long time. You can check progress by comparing the size of the recovered database with the original. From a new SSH session, run:

watch ls -lh home-assistant_v2.db home-assistant_v2.db.fixed

When it’s done, backup the old corrupted database and put the restored one in its place:

mv  home-assistant_v2.db home-assistant_v2.db.`date -I`.corrupted
mv home-assistant_v2.db.fixed home-assistant_v2.db

Start Home Assistant core again

ha core start

Log into Home Assistant and check everything is okay, e.g., by looking at the system logs and checking that the history of some entities is intact.

Still having problems?

If there’s still database errors in the logs, you might need to take a more radical approach. I needed to start over with a fresh database, so I wrote a script to import the long term statistics from a backup database that I had.

You can get a copy of the script over here on Github.