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

Installation Home Assistant OS
25

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?

26

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

1 Like
27

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.

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

1 Like
28

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?

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

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.

32

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’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.
1 Like
33

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
34

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?

35

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

36

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 …

37

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.

38

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?

39

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.

40

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.

41

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

42

I got sqlite3 “dump” 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’m using HAOS, the SSH terminal add-on, and scp / cp commands to transfer and copy the DB files over.

43

Its something like copy db to my linux pc, on cli do
sqlite3 home-assistant_v2.db1 ".recover" |sqlite3 home-assistant_v2_fixed.db
ssh into HA, do
ha core stop
replace the db using samba addon
ha core start

44

Thanks. Are you talking about the “Samba share” add-on ?
Also, if I ssh into HA and do “ha core stop”, I can no longer ssh afterwards to “ha core start”. But I can go to the CLI on my HAOS VM terminal and type “ha core start” there.

45

i use Advanced SSH & Web Terminal