DB image malformed. How to fix it?

Hey now, we can all play nice here! It’s only Wednesday…looks like if people are bothering to try to keep their data, then they’re probably doing something with it. I’ve learned the hard way that capturing data with SQLite default unfortunately usually always fails… This sucks because the data science integration use

In my career I have analyzed a lot of data, and I’ll occasionally work with including large sqlite databases of cleaned up and/or transformed data subsets and ready for modeling, and have rarely had problems with them.

Out of curiosity do you happen to know why in HA this happens (excluding things like power failures causing corruption, etc).?

Well I think people are playing nice! I did say if you want to keep data you need to use something like influxdb. The standard database does seem to need to be deleted whenever you for example restore a snapshot. I chose to delete mine this week because I missed that the mariadb had changed the encoding… last week I found lots of entities were not updating probably because of that etc… anyway my database has nothing I NEED otherwise I’d be using influxdb to read in stuff I care about.

I tend to think it’s because it doesn’t shut down recorder when you make snapshots etc so you get a corrupted db but that’s just a guess. They also update and change the database schema a fair bit as well so maybe that can lead to corruption as well…

1 Like

all these people complaining about corrupt database… SQLite has BEGIN TANSACTION and COMMIT. EXT4 Filesystem is transactional and known good. The only problem could be improper SQLite transactions, eg. some atomic transfer is not being done atomically. Someone should figure out which transaction is causing the error, then look at the code that made that transaction.

1 Like

Thanks allot for this, worked perfect after migrating from Debian10 to HassioOS but my database from the snapshot got corrupted. Life saver!

Thanks, this was the trick that worked for me! :wink:

Thanks for the solution! It seems to work over here, my pi doesn’t disappear every 2 days…

:woman_cartwheeling:

You should change the last line of dump: ROLLBACK --> COMMIT
Without this action you get a new empty database.

Worked for me.
Thanks.

How to do it? How do I stop the supervised HA Docker and performe these commands?
Can you help me?

I just did as instructed in this thread.
Prior to do anything I downloaded sqlite CLI for windows from https://www.sqlite.org/download.html
Then I followed these steps :

  1. Stop HA via the SSH CLI using hassio core stop (I used a display and keyboard connected to the RPI4, login root, empty psswd))
  2. Move home-assistant_v2.db to my windows computer using samba (Windows 10 \HOMEASSISTANT\config)
  3. perform sqlite3 ./home-assistant_v2.db “.recover” | sqlite3 ./home-assistant_v2.db.fix (Windows 10 CMD)
  4. move home-assistant_v2.db.fix back using samba (Windows 10)
  5. rename the db-file (remove the .fix part)
  6. re-start HA via the SSH CLI using hassio ha start (RPI4)

Hope this helps.

1 Like

Hello! Do you remember how long .recover took to run? I got the exact same errors as you when running sqlite3 on my Pi. Then installed SQlite 3.36.0 on my Windows computer and ran .recover. However, it’s been at it for almost 24h now, and I didn’t get any meaningful logs or output.

That being said, I’ve seen that this command can take pretty long to run, and I do have a 4GB database, so it is possible that it just takes that long.

Ou, definitely not 24h :frowning: Think just a few seconds actually. Did it spit out any output yet? Or total silence?

Total silence. What kind of output did it print for you, and how big was your database?

I experimented with SQLite a bit, and found that I can still query most of my database - the corruption errors seem to appear in the few hours leading to the time the backup was made. Which makes a lot of sense.

I can’t really remember, sorry! I think it was not much. just the blocks it fixed. But total silence sounds like it can’t read the file or something… Did you try copying the files down from the SD card to your local fs?

My database was ~ 2GB.

Yes, I was doing this on my PC, because I got the no such table: sqlite_dbpage error when I tried running .recover on my Pi.

Thanks for this - helped me a lot after hours of struggling.

Thanks for pointing us to this option. It has some implications; but I am comfortable using recovery for historical data.

Do not forget to delete lost_and_found table, as it could consume a lot of disk space. in my case it was ~4GB

My small experience.
Recently on my test HA setup I got same “image malformed” error.
Tried to fix it with this method:

sqlite3 ./home-assistant_v2.db “.recover” | sqlite3 ./home-assistant_v2.db.fix

After replacing the database I managed to start HA w/o errors - but lost some data (for example, lost statistics). DB size changed to 2GB from 3.8 GB (was 3.8 for several weeks).
Who knows what else was changed in my DB.
I am a noob in databases, so finally I had to DELETE my whole database…

2 Likes

I’m quite surprised that this isn’t a more common issue with HA. In my case, like yours, the whole system became completely unreliable once the DB got over 4GB.

You can easily write an automation that runs every day that purges older entries… Not sure why this isn’t a built in feature for HA.

My HA runs on a Pi4 with with a 1T SSD (with almost 800G free ) and yet it starts to get flaky with a >3G DB

1 Like

What’s the latest on this? Overnight, my HA lost all of its automations, scripts, configuration, groups, etc. The log contains the following. Whilst I’m a techie, I’m not hugely comfortable with Unix/Linux-type stuff, so want to find out if there is any chance of recovery & how to do it prior to resorting to my last full backup (which unfortunately is not as recent as it should be).

Log details (ERROR)
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:347
Integration: Recorder (documentation, issues)
First occurred: 12:44:17 PM (1 occurrences)
Last logged: 12:44:17 PM

The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2024-02-07T12:44:17.504021+00:00 in order to allow startup to proceed




Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:305
Integration: Recorder (documentation, issues)
First occurred: 12:44:17 PM (1 occurrences)
Last logged: 12:44:17 PM

The database at //config/home-assistant_v2.db is corrupt or malformed
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 305, in validate_sqlite_database
    run_checks_on_open_db(dbpath, conn.cursor())
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 316, in run_checks_on_open_db
    sanity_check_passed = basic_sanity_check(cursor)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 292, in basic_sanity_check
    cursor.execute(
sqlite3.DatabaseError: database disk image is malformed