While I was away on holidays we had a storm and a short power outtage. A few days later I noticed entities had no historical data/statistics before 4am on 10th November 2024. When I looked in the config file, a new DB had been created and I could also see the corrupt DB named “home-assistant_v2.db.corrupt.2024-11-09T17:14:04.999561+00:00”.
When I got home I managed to copy over the corrupted DB to my mac and run “sqlite3 corrupted_database.db “.recover” > recovered.sql”, then “sqlite3 home-assistant_v2.db.recovered < recovered.sql” to import the data into a new DB. I then renamed this DB to home-assistant_v2.db and started home assistant. It had the data from before the 10th November which is good.
Now when I tried to merge these DB’s it appeared to work when running the commands but the historical data is not visible in home assistant. The DB has gone from around 400 MB to 1.6 Gb in size after merging but I cant see any historical stats. I used the below commands when attempting to insert the old data into the new DB (this was all done with copies of the DB offline). The only error I saw was the events table had 16 columns but the target only had 15. I wouldnt think this would impact historical data though?
sqlite3 home-assistant_v2.db
ATTACH 'home-assistant_v2.db.recovered' AS source_db;
INSERT OR IGNORE INTO event_data SELECT * FROM source_db.event_data;
INSERT OR IGNORE INTO event_types SELECT * FROM source_db.event_types;
INSERT OR IGNORE INTO migration_changes SELECT * FROM source_db.migration_changes;
INSERT OR IGNORE INTO recorder_runs SELECT * FROM source_db.recorder_runs;
INSERT OR IGNORE INTO schema_changes SELECT * FROM source_db.schema_changes;
INSERT OR IGNORE INTO state_attributes SELECT * FROM source_db.state_attributes;
INSERT OR IGNORE INTO states SELECT * FROM source_db.states;
INSERT OR IGNORE INTO states_meta SELECT * FROM source_db.states_meta;
INSERT OR IGNORE INTO statistics SELECT * FROM source_db.statistics;
INSERT OR IGNORE INTO statistics_meta SELECT * FROM source_db.statistics_meta;
INSERT OR IGNORE INTO statistics_runs SELECT * FROM source_db.statistics_runs;
INSERT OR IGNORE INTO statistics_short_term SELECT * FROM source_db.statistics_short_term;
INSERT OR IGNORE INTO events SELECT * FROM source_db.events;
Any ideas so I can merge my old stats into the new DB? I did often get “cp: read error: I/O error” when trying to copy over the corrupt DB so I couldn’t say for certain I have been able to work with the full corrupt DB if that makes sense. Any help would be great. Thanks