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

I also battled with these issues for a while.

Interestingly, #60874 was merged in December 2021, but as far as I can tell, it only appears in pre-release 2021.12.0b5 and not in any release changelogs since. I probably have missed it somewhere.

Still, I鈥檝e encountered this issue in March and April 2022 so it seems to be still not fixed entirely. Until then, I鈥檝e written a bash script that creates a backup every night via a conjob. So far, it seems to work fine and I wanted to share it with you guys.

The following script

  • Stops HA
  • Copies the database from the docker environment to the local filesystem
  • Performs an integrity check
  • Dumps, recreates and copies the database back to HA if the integrity check returns issues
  • Starts HA

For now, this seems to to the trick and I鈥檝e not seen any corrupted databases for a few days now.

#!/bin/bash
BASEDIR="/opt/ha-backups/"
SOURCE="homeassistant:/config/home-assistant_v2.db"
TARGET="$BASEDIR$(date +%F\_%H%M%S)_home-assistant_v2.db"

echo "$(date +%F\_%H%M%S) Starting Backup" >> $BASEDIR"log.txt"
echo -n "$(date +%F\_%H%M%S) Shutting down HA: " >> $BASEDIR"log.txt"
ha core stop >> $BASEDIR"log.txt"

echo "$(date +%F\_%H%M%S) Copying database file" >> $BASEDIR"log.txt"
docker cp $SOURCE $TARGET

echo -n "$(date +%F\_%H%M%S) Checking for issues..." >> $BASEDIR"log.txt"
RESULT=$(sqlite3 $TARGET "pragma integrity_check")

if [ "$RESULT" != "ok" ]; then
  echo "issues were found. Fixing..." >> $BASEDIR"log.txt"
  echo
  sqlite3 $TARGET -cmd ".output tmp.sql" ".dump"
  rm $TARGET

  sqlite3 $TARGET -cmd ".read tmp.sql" ".quit"
  rm tmp.sql

  echo
  echo "$(date +%F\_%H%M%S) The backup was dumped and rebuilt. Now copying fixed backup back to HA." >> $BASEDIR"log.txt"
  docker cp $TARGET $SOURCE
else
  echo " no issues were found :)" >> $BASEDIR"log.txt"
fi

echo -n "$(date +%F\_%H%M%S) Starting HA: " >> $BASEDIR"log.txt"
ha core start >> $BASEDIR"log.txt"

My crontab for this looks like this:

# HomeAssistant Backup
0 4 * * * /opt/ha-backups/backup-and-fix

I hope this scripts are useful for other people as well until these issues are finally solved.

3 Likes

Today I had to restore a Home Assistant (partial) backup - due to an irrepealable ShellyForHASS (shelly custom integration) issue. First time to restore a backup ever (since running HA productively). Well:

Database is fine after restore, passed all integrity checks, all data there. :white_check_mark:

So it seems like these mentioned fixes (How to make a full backup and restore with all the data as well? - #2 by tom_l) have been implemented/rolled out meanwhile:

The only negative side effect was the missing 鈥渃onfig鈥 SAMBA share:
It was available but not accessable, no content. Not via SAMBA clients, not for any addon.
Solution:

  1. Restart SAMBA share addon:
    鈫 now working for SAMBA clients and other addons, but: not for the host itself (HA OS) - see [*1] below. Therefore step 2:
  2. Restart Host / HA OS
    (maybe there鈥檚 also a magic remount command or sth to avoid a host reboot, did not investigate further)
    鈫 Now also the host is able to access /config again.

That is certainly not a perfect 鈥渞estore experience鈥 but related to the database pretty smooth. Makes me a lot more confident to restore backups in the future.

Anyway, that SAMBA thing is something probably worth reporting.
Where to? To that addon repository and the HA OS repository? What do you experts think.

[*1]

Maybe, or coincidentally there were no writes durring you backup creation. It deserves confirmation from someone who is confident about this improvement. I鈥檓 not aware it was mentioned in changelogs released during last half a year.

Unfortunately one can not ask in both referenced GitHub PRs. Maybe it was only merged into the dev channel? I don鈥檛 know.

What do you think @tom_l (as I referenced your OP [here](How to make a full backup and restore with all the data as well? - #2 by tom_l otherwise I wouldn鈥檛 even know bout that), has that ever been shipped with a stable HA release?

I had a empty file after fixing with 鈥.dump鈥 too.
Fixing with 鈥.recover鈥 resolved my problem !
Thanks !!

1 Like

Great news :partying_face:

As confirmed in

the two fixes taking care SQLite database is not being corrupted during the backup process (Allow to lock SQLite database during backup by agners 路 Pull Request #60874 路 home-assistant/core 路 GitHub and Refactor recorder queue handling by emontnemery 路 Pull Request #61161 路 home-assistant/core 路 GitHub) have been merged already a long time ago, roughly around February 2022.

鈥hich is the final confirmation of my experience of restoring a backup without having a corrupted database back in July this year (Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #23 by e-raser).

Really wondering if I missed that in the release notes or if they just shipped it silently (why not promoting this fix as it is really really REALLY great news?!?).

1 Like

Well my database corrupted last night during a restore of a backup from a few hrs eariler, so is this not supposed to happen any more?

  • What database do you use? Default one?
  • How did you create the backup?
  • What kind of corruption did you experience? Error definition/log output?
  • (How) Did you fix it meanwhile?

I鈥檓 using the default built-in database.
The backup I restored to was created automatically when core was updated; it ran for a few hrs then corrupted. The corruption was foreign key, iirc.
I fixed it by restoring from a different, previous backup.

Small update which confirms (in regard to Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #27 by e-raser) that backing up the SQLite database nowadays is quite safe because of

Observations:

  • When running a backup (HA only) I discovered, that entities states are updated in the UI. But when opening their history (graph e. g. for sensors), there鈥檚 a 鈥渇lat line鈥 right from the start of the backup until it finishes.
    After the backup is done, opening the same history shows all information, the 鈥渇lat line鈥 is gone.
  • Same with other write attempts: I was trying to remove wrong statistics using the dev-tools / statistics section. That created an error for every request/attempt/click on 鈥渞emove entity鈥 (*).
    After the backup process finished, those actions were performed automatically.
  • I also noticed, that after the process has finished, load (load-1/-5/-15) went up significantly for some time.

So I guess HA simply

  1. locks the database for write access during backup,
  2. collects all database write attempts to a queue and
  3. performs them after the backup has finished (database has been unlocked).

(*)

Logger: frontend.js.latest.202207071
Source: components/system_log/__init__.py:190
First occurred: 00:01:08 (33 occurrences)
Last logged: 14:15:33

:0:0 Script error.
http://my.home-assistant/frontend_latest/18edb15a.js:269:10429 RangeError: number argument must be finite
:0:0 ResizeObserver loop completed with undelivered notifications.
1 Like

wow, this post saved my stats for energy use after a corruption! thank you so much!

FYI my HA runs on an RPI4 with SSD via USB - the backup to Samba runs at 4AM and somehow my DB became corrupted. no restores, no reboots and no idea why鈥 I did the integrity check on the original DB (that HA copied at 4am) and it worked. all stats backed up (and full config backed up at 4am on schedule).

1 Like

Eek! It happened again this morning, same time, same error and same corruption. I鈥檝e just done the pragma integrity_check again but not sure why it鈥檚 happening at the same time each day. I mentioned backups (samba addon) but that鈥檚 happening at 2AM so something else is going on at 4AM.

I have mariadb installed and will look to use that going forward but my energy data is critical to me, thats what I need to somehow keep. any ideas if the sqllite data can be migrated to mariadb?

Check if the specific issue time matches the default purge time:

Best migration guides from SQLite to MySQL are at Migrating home assistant database from sqlite to mariadb (little warning: for some the migration created new issues especially for data and energy data, for others it worked without any issues - so there鈥榮 no absolutely safe, risk-free and guaranteed way at the moment).

Hello together,

every few days my HA is also crashing and I can find those errors in my log files.
I am absolutely not sure what causes the issue.

Yesterday I took a new SSD and did a fresh install and restored an old backup.
So I think that the DB can鈥檛 be corrupt.

Is that assumption correct?

I also did the following in the past:

  • on a running instance I copied the DB to my device
  • did the integrity check
  • no issue

Was that correct in the way to check?

Thank you. I have absolutely no clue on what I can check in addition 鈥

This are the errors found related to SQL:

2:10:25 PM 鈥 (WARNING) Home Assistant Supervisor - message first occurred at 2:10:24 PM and shows up 5 times

/addons/core_deconz/info return code 500

2:10:25 PM 鈥 (ERROR) Home Assistant Supervisor - message first occurred at 2:10:03 PM and shows up 8 times

Error while processing event StatisticsTask(start=datetime.datetime(2023, 5, 31, 12, 5, tzinfo=datetime.timezone.utc), fire_events=True): [Errno 30] Read-only file system: '//config/home-assistant_v2.db' -> '//config/home-assistant_v2.db.corrupt.2023-05-31T12:10:24.828445+00:00'

2:10:24 PM 鈥 (ERROR) Recorder

The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2023-05-31T12:10:24.828445+00:00 in order to allow startup to proceed

2:10:24 PM 鈥 (ERROR) Recorder

Can't read Supervisor data:

2:10:24 PM 鈥 (WARNING) Home Assistant Supervisor

Could not fetch changelog for core_deconz:

2:10:24 PM 鈥 (WARNING) Home Assistant Supervisor - message first occurred at 2:10:24 PM and shows up 5 times

/addons/core_deconz/changelog return code 500

2:10:24 PM 鈥 (ERROR) Home Assistant Supervisor - message first occurred at 7:49:08 AM and shows up 7 times

Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO statistics_short_term (created, created_ts, metadata_id, start, start_ts, mean, min, max, last_reset, last_reset_ts, state, sum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id] [parameters: (None, 1685535024.2400837, 63, None, 1685534700.0, 6108.217472046666, 6108.0, 6109.0, None, None, None, None)] (Background on this error at: https://sqlalche.me/e/20/4xp6)

2:10:24 PM 鈥 (ERROR) Recorder

Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO statistics_short_term (created, created_ts, metadata_id, start, start_ts, mean, min, max, last_reset, last_reset_ts, state, sum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id] [parameters: (None, 1685535024.2400837, 63, None, 1685534700.0, 6108.217472046666, 6108.0, 6109.0, None, None, None, None)] (Background on this error at: https://sqlalche.me/e/20/4xp6)

2:10:24 PM 鈥 (ERROR) Recorder

Lost connection to UniFi Network

And as mentioned, did a reboot, copied the DB and checked 鈥 no issues 鈥

Those (amongst others) really don鈥檛 sound that good (pretty obvious):

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

2:10:24 PM 鈥 (ERROR) Recorder

Error executing query: (sqlite3.DatabaseError) database disk image is malformed

Might be a storage (disk) issue? If it reboots regularly often low RAM is a basic issue.

It is a Pi4 with 8GB Ram.
And as mentioned, disk is a completely new HDD.

Recommendations?

I am not married with my history data, because it is at least only from the last 10 days.

Try to delete thw whole DB and restart?

The database is automatically recreated with empty tables according to the log messages you posted.

If you absolutely don鈥檛 care about data, it might of course be the easiest way to keep going. Usually people at least have some long term statistics like energy dashboard data which is why one doesn鈥檛 want to loose that history.

When I do a restart of the HA instance via power off/on. There are still values available.
So it seems to me that this message also might be not correct, don鈥檛 know.

My last crash was on the 31.05.
When I have a look to the history, for example temperature of a sensor, it is still available with data from also 24.05.

I tried this, but I end up with the following error :slight_smile:
鈥淚ncomplete SQL: ROLLBACK; 鈥 due to errors鈥

And the 鈥淿fixed鈥 file is 0 byte, unfortunately, while the input database is 2.5 GB.

I will try to go back to previous snapshots, but this one was the latest. Any idea what I could do ?
The DB does appear to function fine in HA until the 4am purge, at which point HA creates a brand new database from scratch - sigh.

I got sqlite3 鈥渄ump鈥 to work on one of my older snapshots.
I tried to overwrite it in the /config directory. I then restarted HA.
It promptly deleted the database and created a new one.
How are you all copying the files ? Is there a way to partially shut down HA to do this ?
I鈥檓 using HAOS, the SSH terminal add-on, and scp / cp commands to transfer and copy the DB files over.