(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: