Hopefully someone can help me. My database gets corrupted every night at 4:12 during the auto purge. I did a integrity check using DB Browser for SQLite and it found 3 issues.
My primary concern is my long term energy data I need to conserve, the other data points I can live with losing.
Conceptually I’d say I have 2 options, either fix this DB or add the energy data to the new DB.
Is there anyone that can help me fix this, ideally with a step by step approach. I have some computer/IT/programming skills, however databases are new to me.
After some more searching/trying/failing I found a solution that worked for me. This is largely based on solution provided in other threads, however for me some intermediate steps were unclear, so I’ll share what I did in a way that makes sense to me (for others like me or future-me when this happens again).
First things first, HA runs as VM on a windows NUC without monitor/keyboard, so I use remote desktop if I need to access it. Furthermore, important to have SAMBA or something similar setup to get files on/off HA.
I could have done this on the NUC via remote desktop, but chose to use my laptop.
What I did
Locate and copy your corrupted DB from HA to your laptop/PC
Place the .exe file from SQLite and the corrupted DB in the same folder
Open up Command Prompt (cmd) and change directory (cd) to the folder the DB is in
Run this command (on 1 line, don’t split it, the “|” is correct) and then WAIT and in my case, ignore the error messages you see (important I’ll explain why this threw me off) sqlite3 ./home-assistant_v2.db “.recover” | sqlite3 ./home-assistant_v2.db.fix
Where ./home-assistant_v2.db is your corrupted DB and ./home-assistant_v2.db.fix the name of your new fixed DB
Once this is done, you know because you can enter a new CMD line, you should have a new DB in your folder
Before you delete your corrupted DB in HA and replace it with the fixed one, you should stop HA Core. I did this by going to developer tools > Services, searched for Home Assistant Core Integration: Stop and ran that
Now using SAMBA I copied the fixed DB to the HA Folder, deleted the corrupted one and renamed the fixed DB to home-assistant_v2.db
I then restarted my HA instance via the VM (your interface wouldn’t work because you stopped Core. This could probably also be achieved via SSH, but I didn’t have that on my laptop, so went for the easy solution
For a couple of days now, the nightly purge happens without corrupting the DB, so fingers crossed this fixed for me, for now.
Lastly, why is the WAIT and ignore errors important? Very quickly after executing the cmd, I got an error/warning about a purge error on lineXXX and then nothing else. I thought that meant it stopped and my froze and I clicked away the CMD window. Going through this a couple of time and not understanding why it worked for others and not me, I got distracted by something and didn’t immediately close the CMD window. A while later, I got back to my laptop and saw that the cmd finished with a handful of similar error messages, and that is when I understood while it looked frozen, it actually continued to run.
Now I was afraid the errors meant I still had a bad DB, but tried to put it back anyway (nothing to lose kind of situation) and turned out, it worked.
Let me know if you have any questions or comments about the write up.
All the best!
Yours just fixed my issue and I am very grateful for this! (not loosing 12 months of historical energy data)
EDIT: Just for the record…
I just did one thing differently: I did not use the SQLite.exe but logged into my HA host with putty and used the sqlite3 of the HA host.
You are my hero too!
Did exactly the same, Copied the sqlite command from your post and paste it to my shell, had an error regarding the double quotes, they where incorrected pasted hahaha.
