(SOLVED) My database has a corrupt record (sqlite3.OperationalError), how to delete the record

Configuration
1

System:
HAOS 12.0
Core 2024.3.0
sqlite database 16.9 Gb

Everytime recorder.purge runs I get the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Could not decode to UTF-8 column 'attributes_id' with text '0g��S��|)('
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2024-03-10 20:33:14.096 WARNING (Recorder) [homeassistant.components.recorder.util] Error executing purge: (sqlite3.OperationalError) Could not decode to UTF-8 column 'attributes_id' with text '0g��S��|)('
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Result is that the database is discarted (home-assistant-v2.db.corrupt), a new database is grown and all my statistics data of the last years is… gone.

So killing the database and have HA generate a new one is not the answer I am looking for.

Please advise on a way to find this “faulty record”, delete it and save my statistics data.

Kind regards Han

2

Solved it myself.

  1. stopped core
  2. renamed database to home-assistant_v2.statistics.db
  3. started core (new database was build)
  4. stopped core again
  5. renamed database to home-assistant_v2.states_events.db
  6. copied both databases to my laptop
  7. exported both database from DB Browser selecting only the state & event tables from the “new” database and statistics from the “old” database.
  8. imported sql #1 into a new database called “home-assistant_v2.db”
  9. imported sql #2 into the database
  10. copied database back to Home Assistant
  11. core start

after this the state and event tables were nearly empty (do not care) but the error when running purge is gone. Statistics are there :grinning:

3

Hi Han,
Ih have exact the saem issue.
Solution sounds feasible, but what do you mean with sql #1 and sql #2 at the end?

kind Regards Ruudini

4

As far as I can remember :smiling_face_with_sunglasses: I pointed towards the dumps I made from the databases that I imported again.

5

Hi Han,
thanks for the reply, I will test soon.

Just to note my understanding:

  1. core stop
  2. rename database “home-assistant_v2.db” to “home-assistant_v2.statistics.db”
  3. core start (new database was build)
  4. core stop (again)
  5. renamed database to “home-assistant_v2.db” to “home-assistant_v2.states_events.db”
  6. copy both databases to a laptop
  7. export databases from DB Browser:
    a. selecting only the state & event tables from the “new” database to sql #1
    b. selecting statistics from the “old” database to sql #2.
  8. imported sql #1 into a new database called “home-assistant_v2.db”
  9. imported sql #2 into the database
  10. copied database back to Home Assistant
  11. core start

(needed to writ that down before I try out)
As my database is just growing, testing is possible framed by backup and restores around the method above with out too much dataloss.

6

Hope everything goes well @Ruudini :ok_hand:

7

First attempt:

download from HA ok
export to sqls done integrity check would do some modifications, not saved
import of sqls done with some mistakes: foreign key restrictions were violated, should repair
ignored both, uploaded to HA via Samba as …db.new

ls command lists the file in green. ignored, stopped core and renamed to …db
core start: fail, new blank database created.

so over all: no success.
btw. upload via file-editor didn’t work

What did I do wrong? I am barely a user with som talents on command lines (not runnign away when it gets to them). I hav a strom feeling that there might be more to do in the DB Browser.

Just testing the method, no harm done no repair done to the actual db

8

The process should be without any errors. I do not know if the structure of the database changed in time when I did this, but in fact it should not matter. Try again making sure every step is error free.

9

I give up.
Nothing works.

I am too much DAU to understand really what to do. I will keep a Backup of the long term data for maybe further import options if I get motivation to try that.

Thanks for opening up a chance.
Maybe we have to overcome our “Datenverlust-Angst” :slight_smile:

Cheers Ruudini