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

[Istria1704]

Not completely sure.
On a fresh HAOS VM, instead of making a new account, I choose to import the backup file. And when it was done doing that and the dashboard loaded, the graphs etc were populated with past data. So I assumed to import succeeded.

But its possible it was still working on it in the background and declared the database corrupt later. I don’t know how it works internally.

[Blogshot]

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’ve encountered this issue in March and April 2022 so it seems to be still not fixed entirely. Until then, I’ve 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’ve 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.

[e-raser]

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.

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:

• https://github.com/home-assistant/core/pull/60874
• https://github.com/home-assistant/core/pull/61161

The only negative side effect was the missing “config” 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’s 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 “restore 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]

[maxym]

@e-raser

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:

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

[e-raser]

Unfortunately one can not ask in both referenced GitHub PRs. Maybe it was only merged into the dev channel? I don’t 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’t even know bout that), has that ever been shipped with a stable HA release?

[t.huber (Tobias Huber)]

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

[e-raser]

Great news

As confirmed in

WTH: Finally reliable, restorable backups by not corrupting the default recorder database (SQLite) during backup creation anymore

They were both merged 10 months ago… I guess if you’re not aware, if something is merged then it is included in next major release. So in this case that was 2022.2.0 (and obviously every release after that). Sometimes commits are cherry picked into a patch release of the previous major version. One of those has the cherry picked tag so it actually came out even before 2022.2, was in one of the patches of 2021.12

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.

…which 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?!?).

• I’m gonna mark this post as “solution” (as it prevents databases getting corrupted during backup creation) even this topic is about fixing a corrupt database, no matter how that happened.
• Those options can be found in the original post @ Fix corrupted / malformed SQLite database (home-assistant_v2.db).

[j1234]

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?

[e-raser]

• 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?

[j1234]

I’m 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.

[e-raser]

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

• 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

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’s a “flat line” right from the start of the backup until it finishes.
  After the backup is done, opening the same history shows all information, the “flat 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 “remove 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.

[rico365 (rico)]

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).

[rico365 (rico)]

Eek! It happened again this morning, same time, same error and same corruption. I’ve just done the pragma integrity_check again but not sure why it’s happening at the same time each day. I mentioned backups (samba addon) but that’s 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?

[e-raser]

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

Recorder

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‘s no absolutely safe, risk-free and guaranteed way at the moment).

[Phoenix-DH]

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’t 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 …

[e-raser]

Those (amongst others) really don’t 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.

[Phoenix-DH]

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?

[e-raser]

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

If you absolutely don’t 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’t want to loose that history.

[Phoenix-DH]

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’t 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.

[madbrain]

@e-raser

“sqlite3 home-assistant_v2.db “.dump” | sqlite home-assistant_v2.db_fixed”

I tried this, but I end up with the following error
“Incomplete 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.