Trying to migrate from MariaDB back to default SQLite. Migration completed but corrupted db

Configuration
1

I have been using MariaDB for about a year (with a fab keep_days: 720, till now that I learned about long term statistics), and now I want to migrate back to SQLite (the original one still had the data from my first few months with HA) after having purged to 10 days.

To migrate I followed this guide. It worked well, no errors.

But when booting HA with the new db I noticed history was empty, and errors in the log. Checked the db file and it showed corrupted.

Here are the error logs when HA loads with it. (Image here in case it’s easier to skim through)

So I searched how to fix that and found this. I followed the guide and used first the command:

sqlite3 ./home-assistant_v2.db ".recover" | sqlite3 ./home-assistant_v2.db.fix

Replaced the db, but no luck, it was detected as corrupted again.

Then tried source:

sqlite3 home-assistant_v2.db ".dump" | sqlite3 home-assistant_v2.db_fixed

With the same luck.

Then tried pragma integrity_check; using dbbrowser, and it returned the db was ok. No errors.

Not sure what else I can try to recover all the data in my db. :frowning: I still retain MariaDB so could migrate again, but not sure how that would help since the migration itself reported no errors.

Need help.

2

Solution was to repeat the migration, but taking care to do it on a brand new db.

The first migration I did was on my old original HA db. So essentially it tried to merge them. Probably the old one was a different version or format, and that’s why the resulting db was corrupted (my wild guess).

So after clearing any db from HA, I repeated the migration, and it created a brand new db (even if losing the data from the original sqlite db). But this resulting db worked perfect.

I could look into a way to try and migrate that old db data into the new one now, but in reality I won’t bother. Too old data.

More info here

1 Like