Hi everyone,
I’ve the same issue and would realy like to recover the energy data from the db.corrupt file…
BR,
Hi,
I have the same issue. On Jun 18th I lost all my energy data, but now I know how to recover.
1. Important (I missed that and corrupted my db a couple times): STOP HA Core in ssh with “ha core stop” or via service call!
2. download the current home-assistant_v2.db and home-assistant_v2.db.corrupt.* databases and open the current db in SQLite Browser and attach also the corrupt database as corrupt
3. As some statistics_meta record could be missing, get those from corrupt db:
-- insert possibly missing statistics_meta records
insert into statistics_meta
select row_number() over () + (select max(id) from statistics_meta) as id, c.statistic_id, c.source, c.unit_of_measurement, c.has_mean, c.has_sum, c.name
from corrupt.statistics_meta c
left outer join statistics_meta m on m.statistic_id = c.statistic_id
where m.id is null;
4. Before inserting the records, update the id column of the existing data:
-- move existing ids, as all corrupt records are older
update statistics
set id = id + (select max(id) from corrupt.statistics);
5. Now, the statistics table is missing all those records from the corrupt database, so we import those with this sql:
-- import statistics from corrupt db
insert into statistics
select c.id, c.created, c.created_ts, m.new_id as metadata_id, c.start, c.start_ts, c.mean, c.min, c.max, c.last_reset, c.last_reset_ts, c.state, c.sum
from corrupt.statistics c
inner join (
select o.id as old_id
, n.id as new_id
, case when row_number() over (partition by o.id) > 1 or row_number() over (partition by n.id) > 1 then 0 else 1 end as test
from corrupt.statistics_meta o
inner join statistics_meta n on n.statistic_id = o.statistic_id
) m on m.old_id = c.metadata_id and m.test = 1
6. As the sum is incorrect for new values in current db, we update those as well:
-- update sum values causing mega negative values for day of corruption in Energy Dashboard
update statistics
set sum = sum + a.add_sum
from (
select s.id, a.statistic_id, sum(a.lag_sum) as add_sum
from statistics s
inner join (
select *
from (
select m.statistic_id, s.*, lag(s.sum) over (partition by s.metadata_id order by s.start_ts) as lag_sum
from statistics s
inner join statistics_meta m on m.id = s.metadata_id
where s.sum is not null
) t
where t.sum < t.lag_sum
) a on a.metadata_id = s.metadata_id and s.start_ts >= a.start_ts
group by s.id, a.statistic_id
) a
where a.id = statistics.id
7. Rebuild statistics_short_term from today’s statistics - it is needed so new statistic records have correct sum value
-- rebuild statistics_short_term
delete from statistics_short_term;
insert into statistics_short_term
select row_number() over () as id, c.created, c.created_ts, c.metadata_id, c.start, c.start_ts, c.mean, c.min, c.max, c.last_reset, c.last_reset_ts, c.state, c.sum
from statistics c
where date(c.start_ts, 'unixepoch') = DATE('now')
order by c.start_ts, c.id;
8. Start ha core again 
All my history in Energy Dashboard is now back again - running smooth for many hours now… 
6 Likes
Worked for me as well!
1 Like
Hmmm, I get a error during step 6 where you ‘select *’ from the first inner join, it then offers all of the available commands and repeats that a couple of times:
sqlite> update statistics
from (
select s.id, a.statistic_id, sum(a.lag_sum) as add_sum
from statistics s
inner join (
select *
from (
select m.statistic_id, s.*, lag(s.sum) over (partition by s.metadata_id order by s.start_ts) as lag_sum
...> set sum = sum + a.add_sum
...> from (
...> select s.id, a.statistic_id, sum(a.lag_sum) as add_sum
...> from statistics s
...> inner join (
...>
Display all 235 possibilities? (y or n)
ABORT ROW
ACTION ROWS
ADD SAVEPOINT
AFTER SELECT
ALL SET
Any ideas please, I just lost over two years of statistics after the home assistant database got corrupted itself this morning!
Hi here,
sorry - couldn’t reply earlier.
I’ve used SQLite Browser (Windows App) to attach both databases and the sql pane to run the commands.
It looks to me the statement got cut off probably?
I’d try testing a select on the query to check what’s going on:
select *
from (
select s.id, a.statistic_id, sum(a.lag_sum) as add_sum
from statistics s
inner join (
select *
from (
select m.statistic_id, s.*, lag(s.sum) over (partition by s.metadata_id order by s.start_ts) as lag_sum
from statistics s
inner join statistics_meta m on m.id = s.metadata_id
where s.sum is not null
) t
where t.sum < t.lag_sum
) a on a.metadata_id = s.metadata_id and s.start_ts >= a.start_ts
group by s.id, a.statistic_id
) a
where a.id = statistics.id
Cheers - cosote.
1 Like
I had the same problem. Every night at 04.12 the data disappeared. It took me a while (10 days) to find this solution. I hope that tonight it does not disappear.
I have, in several backups, most of the data of the past 10 days. I assume that copying that (energy) data back is possible, but I not a coder and not into database. Cosote, Thanks for posting the procedure and can you help me with a script?
PS: I did not do step 2, but opened the databases on my RPi. Before I could do that I had to make a copy of the corrupt database, because of the file name (home-assistant_v2.db.corrupt.2023-08-20T02:12:00.520929+00:00).
Great post! Worked like a charm for me!
Let me buy you a coffee
worked like a charm on postgresql. Just check the log everything is working. I had to update the incremental ID from the table statistic.
Link for a coffee?
Im also a victim to this… its crazy that it can happen so easliy and that we store the precoius long term data in it. I have it all in influx also but the energy dashboard is just to neat… I will move to maria and try to migrate the data from the corrupt db…
Hi,
this morning my DB got corrupted and I don’t know why.
However, when following your guide (thanks for this as I am a SQL rookie).
I get database disk image is malformed from DB Browser for SQLite when I want to attach the corrupt DB
Any suggestion?
EDIT:
I solved my issue using this guide:
Restore corrupt database after nightly purge - Installation / Home Assistant OS - Home Assistant Community (home-assistant.io)
1 Like
Hi Cosote,
Recently I ran into a database corruption as well, your procedure seems to have recoverd all my historical data, but, solar production data seems to have been imported incorrectly.
All recovered solar data has been divided by 1000, changing MWh values into kWh (and of course kWh into Wh). Additionally, the Energy dashboard indicators for ‘Self-consumed solar energy’ and ‘Self-sufficiency’ are greyed out and display 0% for any period touching recovered historical data.
As I’m not an SQL guru, any idea how to correct these?
I assume the statistics copy operation somehow does not check for, or includes the energy data quantity (Wh, kWh, MWh etc.).
For anyone trying to perform your procedure, some additional information about the procedure I performed:
When purely using the home assistant add-in Terminal & SSH, as soon as I stop ha core process, the add-on for ssh access is also no longer working using the webinterface and so is the file editor.
To circumvent this:
- Update the Terminal & SSH add-in configuration to include a password (Options section) and entered the SSH port (Network section).
- This allows for direct SSH access (I used Putty as I’m running Windows on my PC)
- Now I’m able to access Home Assistant (running on a Raspberry Pi 4) using SSH and transfer the running DB file and the corrupt one. I’ve used PSCP (part of Putty installation) to perform the file transfer. syntax:
pscp -P 22 root@:/config/home-assistant_v2.db c:\temp\home-assistant_v2.db`
Filetransfers using WinSCP for instance are way to slow and max out the Pi’s CPU
important: be aware the corrupt database filename uses characters not supported by Windows, so when copying the corrupt database file, replace the “:” characters in the target filename
now I was able to use SQLite browser on Windows to perform the procedure Cosote published.
Took me some time to figure the additional steps out, so shared them in case anyone else runs into the same challenges 
It works great! Thanks a lot!!! 
Got a little more details in the solar energy issue,
the new database, created after the corruption, indeed uses Wh instead of kWh, as can be seen in the statistics_meta table of the database.
I solved my issues by first adding the missing data from the corrupt database copy to the active database. Then correction the value of the state column within the statistics table by multiplying the value with 1000 for each entry dating from the source database.
Additionally I corrected the value in the sum column, which seems the be the actually used value for the energy dashboard.
Not completely sure why, but the sum correction sql query did not work correctly for me, so I’ve used a seperate query for the imported content and manually corrected the newer values to have the correct values throughout the total recorded timerange.
@cosote
Thank you for posting this, but I do have troubles.
I think my database version does not match yours? I am running Home Assistant Core 2022.6.6
I get a
Execution finished with errors.
Result: no such column: c.created_ts
error.
So after checking my statistics table:
I tried to modify the query (sorry I can only do very simple SQL, so your statements are wizard level to me). So I tried to remove the offending colloums and hoping it works (created_ts last_reset_ts etc)
-- import statistics from corrupt db
insert into statistics
select c.id, c.created, m.new_id as metadata_id, c.start, c.mean, c.min, c.max, c.last_reset, c.state, c.sum
from corrupt.statistics c
inner join (
select o.id as old_id
, n.id as new_id
, case when row_number() over (partition by o.id) > 1 or row_number() over (partition by n.id) > 1 then 0 else 1 end as test
from corrupt.statistics_meta o
inner join statistics_meta n on n.statistic_id = o.statistic_id
) m on m.old_id = c.metadata_id and m.test = 1
I get
Execution finished with errors.
Result: UNIQUE constraint failed: statistics.metadata_id, statistics.start
So I am kind of lost… any advice?
Or better - do you see how to rewrite it to my version of HA?
@domsl, in case you still need this.
I recently faced the DB corruption issue and fixed the original SQL script.
In my case, I had duplicated records, so the solution was to just ignore them.
Additionally, I changed the way we shift records in the current DB to make space for old(corrupted).
-- insert possibly missing statistics_meta records
insert into statistics_meta
select row_number() over () + (select max(id) from statistics_meta) as id, c.statistic_id, c.source, c.unit_of_measurement, c.has_mean, c.has_sum, c.name
from corrupt.statistics_meta c
left outer join statistics_meta m on m.statistic_id = c.statistic_id
where m.id is null;
-- move existing ids, as all corrupt records are older
update statistics set id = - (id + (select max(id) from corrupt.statistics)) where id > 0;
update statistics set id = - id where id < 0;
-- import statistics from corrupt db
insert or ignore into statistics
select c.id, c.created, c.created_ts, m.new_id as metadata_id, c.start, c.start_ts, c.mean, c.min, c.max, c.last_reset, c.last_reset_ts, c.state, c.sum
from corrupt.statistics c
inner join (
select o.id as old_id
, n.id as new_id
, case when row_number() over (partition by o.id) > 1 or row_number() over (partition by n.id) > 1 then 0 else 1 end as test
from corrupt.statistics_meta o
inner join statistics_meta n on n.statistic_id = o.statistic_id
) m on m.old_id = c.metadata_id and m.test = 1 order by id;
-- update sum values causing mega negative values for day of corruption in Energy Dashboard
update statistics
set sum = sum + a.add_sum
from (
select s.id, a.statistic_id, sum(a.lag_sum) as add_sum
from statistics s
inner join (
select *
from (
select m.statistic_id, s.*, lag(s.sum) over (partition by s.metadata_id order by s.start_ts) as lag_sum
from statistics s
inner join statistics_meta m on m.id = s.metadata_id
where s.sum is not null
) t
where t.sum < t.lag_sum
) a on a.metadata_id = s.metadata_id and s.start_ts >= a.start_ts
group by s.id, a.statistic_id
) a
where a.id = statistics.id;
-- rebuild statistics_short_term
delete from statistics_short_term;
insert into statistics_short_term
select row_number() over () as id, c.created, c.created_ts, c.metadata_id, c.start, c.start_ts, c.mean, c.min, c.max, c.last_reset, c.last_reset_ts, c.state, c.sum
from statistics c
where date(c.start_ts, 'unixepoch') = DATE('now')
order by c.start_ts, c.id;
I used DB Browser for SQLite.
Open the new (home-assistant_v2.db) file and then attach the old (home-assistant_v2.db.corrupt) as "corrupt" (it asks for the name when you select the file).
Execute the script and push the "Write Changes" button.
Tested on HA 2024.3.3.
1 Like
Hi,
Sorry for the late reply - I somehow got not notified about your post!
at the step
insert or ignore into statistics
select c.id, c.created, c.created_ts, m.new_id as metadata_id, c.start, c.start_ts, c.mean, c.min, c.max, c.last_reset, c.last_reset_ts, c.state, c.sum
from corrupt.statistics c
inner join (
select o.id as old_id
, n.id as new_id
, case when row_number() over (partition by o.id) > 1 or row_number() over (partition by n.id) > 1 then 0 else 1 end as test
from corrupt.statistics_meta o
inner join statistics_meta n on n.statistic_id = o.statistic_id
) m on m.old_id = c.metadata_id and m.test = 1 order by id;
I get the same error again:
Execution finished with errors.
Result: no such column: c.created_ts
At line 1:
insert or ignore into statistics
select c.id, c.created, c.created_ts, m.new_id as metadata_id, c.start, c.start_ts, c.mean, c.min, c.max, c.last_reset, c.last_reset_ts, c.state, c.sum
from corrupt.statistics c
inner join (....
Do you have an idea to fix it? Do you need a sample or something?
Thanks for much!
Maybe your DB schema version is old and not compatible. You may need to make your own script to make your old tables compatible first.
Sorry, I can’t help with that.
You may try to make HA open that old DB file, I think it may be able to update the schema for you.
Thanks for your reply!
I am not good enough in sql, so I guess I’ll just leave it and live with the missing data.
Hey, this is super helpful - greatly appreciate you posting this.
I was able to import all of my historic energy data from my corrupt DB using this. One thing though, I have a gas sensor setup and the values showing in my dashboard are now astronomically high for last year “1,989,615 ft^3”. Even if I divide the 1.9million by 100 (ft^3 → CCF), that value doesn’t seem right, way too high.
My gas meter reads in CCF, I guess there is some unit conversion that went bad when I ran the query. Any idea on where to start troubleshooting?
work for me too, thanks a lot. I can’t access to my HA with ssh so i make it with gparted and scp to take the file and repair. Thanks again