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…
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.
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 :
Stop HA via the SSH CLI using hassio core stop (I used a display and keyboard connected to the RPI4, login root, empty psswd))
Move home-assistant_v2.db to my windows computer using samba (Windows 10 \HOMEASSISTANT\config)
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.
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?
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…
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
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
Think just a few seconds actually. Did it spit out any output yet? Or total silence?