Missing historical data since a few days - how to grab and merge from a backup?

[phrozen77]

Hey all,

first off - thank you for continuing the fantastic works of oziee, it's really much appreciated!

So, for one reason or another, since a few days now i'm missing my historical data - seems somehow it got corrupted, overwritten - dunno.

Since this has happened in the past already (but was quite stable for at least some time now), i'd love to get the old data back.

As i do daily backups (running in an LXC container on Proxmox VE, backed up by PBS) i think i could grab saved/old data and merge it with the new?

From what i could gather, it previously was saved to a .json file (solcast.json), glancing at the code (sorry, i'm really bad at reading Python) it seems that the data is now saved to the database?

I'd really appreciate it if someone could point me in the right direction!

[gcoan]

The JSON is just a cache of the current solar forecast and AFAIK it hasn't been changed.

The history has always been kept in the statistics database. If you can't find any history is it because you've changed the entity names when swapping to the BJreplay integration, or maybe you've had a corruption on the HA database and its lost ALL your history for every entity?

If the former can you rename your entities to what they were beforehand?

If the latter then you should see this in the System log. You can recover HA back to a backup taken before the corruption but I don't know of any way to merge history from an old backup into HA. I lost my own HA database about a month after I first started with HA, would love to recover that history but all the google searches I did came up with what appeared to be some very dodgy scripts and I feared I would corrupt the database again. If there was a published HA solution that'd be great.

[phrozen77]

It's just the historic Solcast forecast data that is missing, any other statistics are still there as always.

As for the entity names - i plain and simple don't know if they changed, but i'd suppose they didn't. I use a few forecast entities on a dashboard, still alive without me changing anything after migrating.

Regarding the energy dashboard, the configured entity there isn't really accessible to me in plaintext - it is in a sub-config of my PV array production:

But again, that wasn't changed by me after migrating.

I kinda suspect that the migration away from oziee's integration is not the culprit here...

Judging by my comment here, i migrated on the 14th - earliest daily production forecast datapoint i got now is from the 13th...

[gcoan]

I think I have the same problem, am sure that I had historical solar forecasts in the energy dashboard, but now it only seems to go back to early June

Looking at the long term statistics there are a couple of solcast entities with errors

but I guess its solcast_pv_forecast_today that's used, which doesn't have any errors, but looking at the history there are lots of zero's on historical dates and gaps in the data on some days as well

No idea

[gcoan]

I just counted the graph and I have 14 days of historic solar forecasts showing.

Which is exactly the same as my purge_keep_days for recorder: in configuration.yaml - increased from the default 10 days history to 14.

I managed to find an old screenshot of my energy dashboard and it WAS definitely using long term statistics and showing more than 14 days of history:

[autoSteve]

Mine goes back to 11 June.

[ProphetOfDoom]

Mine too

[autoSteve]

So different to @gcoan.

Does it coincide with installing a new release? 🤔 This should not mess with any history, and no sensor names/etc. have changed. And I just yesterday installed 4.0.32, so that's ten days of history only, where 730 days should be kept.

This happened around 11 June.

This from 6 June.

[autoSteve]

I wonder if changing the state_class for total_kwh_forecast_today to TOTAL is behind it?

This crept in as part of Oziee's v4.0.23 'missing' changes.

This in v4.0.28:

[gcoan]

Could well be that this change has zapped the history, and looking at my energy graph today, it's still got data for 7th June, 15 days ago.

Reading the documentation, measurement looks to be the more correct type https://developers.home-assistant.io/docs/core/entity/sensor/#available-state-classes but changing it back again may cause more problems ☹️

[ProphetOfDoom]

Sorry, but I have to disagree. You can measure instantaneous power (kW) but cannot do so for energy due to the time dimension, so I think HA is right when it warns that it should be a Total

[autoSteve]

I have restored a parallel instance dating back to Oziee's last release (9 June was all that was available that far back), and have upgraded it to BJReplay 4.0.31.

On restart all long term history is there. Presumably a job runs at some stage, maybe midnight to prune things. I'll leave it running overnight and see what happens.

(edit) I also updated it from 2024.6.1 to 2024.6.4. No change to long-term.

[autoSteve]

We have another data point.

Another user reporting an unrelated non-issue said no forecast history when I asked...

[autoSteve]

As a test, I set the state_class of the forecast today sensor to None, which is the same as it used to be in Oziee's 3.0.2x. Long term history did not magically come back.

How important is this long term history? Does it inform things like Predbat?

[phrozen77]

Important in the sense that i'd just like it to go back as far as possible, check prediction vs. reality etc..

Its just a grinds my gears kinda thing - not using Predbat (seems to be some kind of forecast for charging home batteries?).

[gcoan]

How important is this long term history? Does it inform things like Predbat?

Predbat doesn't use the statistics at all as its not granular enough, it uses the sensor history, but even then its only going back 7 or 10 days or so (depending on what you set your history to) to calculate projected daily load.

Personally I'd like to see the history because its interesting, but if its gone and there's no easy way to get it back I can live with it. Have no desire to be running SQL on my statistics db as I suspect that may have caused a db corruption I experienced last October.

[autoSteve]

Thankfully no one's going to die because of this. It'll feel like it to the melodramatic. 😂

[phrozen77]

No, but my feelings are hurt! Hurt, i tell you! 🫨

[autoSteve]

Well done for working this out, @phrozen77. In retrospect it is obvious, and probably nothing to do with the Oziee changes.

But I want to know why. (Code staring tonight for me...) Does the integration drop the solcast.json file for any reason? Don't think so... why would it? Why did this seemingly happen to everyone's install?

[autoSteve]

To the original question, @phrozen77...

This fascinates: https://community.home-assistant.io/t/custom-integration-to-import-long-term-statistics-from-a-file-like-csv-or-tsv/689793

I'm definitely going to try this!

Export data to .CSV from History.

[gcoan]

This fascinates: https://community.home-assistant.io/t/custom-integration-to-import-long-term-statistics-from-a-file-like-csv-or-tsv/689793

I'd found that one previously as well, was planning on trying it recover some energy statistics. Be interesting to see how it goes

Export data to .CSV from History.

You are lucky by having all the data in your history, I've only got 14 days. If this does work I'd have to create a parallel HA instance and recover from monthly backups, but with 14 days history in each I'd still have gaps ☹️

[autoSteve]

Righto.

Adds 'unit' column.
Reformats datetime like 'dd.mm.yyyy hh:00'.
Finds out what 'sum' should be. Decides it's phurphy because applies to total_inceasing, but adds 'sum' column with nulls.

Transfers file.

Spells Australia right.

Clicks import and gets a big green tick.

Stats do not show on the energy dashboard.

I have failed you.

[autoSteve]

but with 14 days history in each

My backup from June 9, back in Oziee time had all history, @gcoan.

[gcoan]

Stats do not show on the energy dashboard.

Is it worth giving it a couple of hours to propagate to the energy dashboard? There is a delay normally, not sure if this is due to the stats routine only running hourly or something in the workings of the energy dashboard

[ProphetOfDoom]

Don't know if it will help, and you use it at your peril, but I put this ERD together in august last year when I was exploring how the HA DB works when I had a data issue caused by adding/removing integrations in short succession and not really understanding what I was doing at the time.
Download the previous HA backup of interest. Open it in 7-Zip, drill down into the homeassistant.tar.gz file within, and extract the home-assistant_v2.db database file.
Download and Install DB Browser for SqlLite (https://sqlitebrowser.org/) or similar and use it to open the extracted database file
Explore the tables and write an Sql query to extract the history data you want

[autoSteve]

This is an interesting experience.

I restored to 9 June / Oziee last version. History there.

I messed up. With this instance I had not upgraded to JBReplay Solcast.

Upgraded to BJReplay 4.0.31. Got a forecast. Turned off forecast schedule.

History still there.

I got nothin'.

[autoSteve]

From an Oziee latest install, changing to state_class = SensorStateClass.TOTAL from None definitely kills the history for that sensor, so this is the confirmed root cause.

Setting it back to None and restarting instantly brought the history back.

Try that. In sensor.py, comment out the stat_class on line 47, restart HA and see if the history comes back. If it doesn't then there may be a periodic purge, but I have absolutely no idea whether that is the case.

(edit) Commenting the line did not work on my 'prod' instance. According to forum posts, LTS are never purged, so this confuses me.

It now begs the question, what should we do about it? I'm tending towards reverting that change even if this test doesn't work for you to prevent others hitting it.

[phrozen77]

First try: changing TOTAL to None
Result: Integration crashed on restart
14 days historic forecast was there, nothing else.

Second try: commenting out line #47
Result: Integration was working again, no further historic data beyond the 14 days back..

Third try: changing TOTAL to MEASUREMENT
Result: Integration still working, still no further historic data tho...

Edit: all attempts have been done with only a few minutes in between and with the latest BJReplay integration (4.0.31)

It now begs the question, what should we do about it?

Good question - i've reverted the state_class to TOTAL for now, thinking of trying to do the export-import CSV dance 🥹

[autoSteve]

I've PR-ed a proposed reversion of Oziee's change to the state class, commenting out that line 47 so that others don't hit this. I'm suggesting v4.0.33.

v4.0.32 is currently pre-release, but seems to be doing well so far. v4.0.33 wouldn't need a pre-release.

Can't wait to see how you get on with LTS import.

I can see how it should work for TOTAL_INCREASING sensors, but not TOTAL/None. For TOTAL_INCREASING there needs to be a 'sum' calculation of deltas since last value (in this example I pinched it happens to be reducing over time, hence the negatives).

Trying just blank in the .CSV didn't work. Trying deltas regardless of time of day didn't work. Trying using value in the 'sum' column for the first datetime at midnight local (the extract is zulu time), then deltas for the local rest-of-day, then rinse repeat didn't work.

[gcoan]

Can't wait to see how you get on with LTS import.

I can see how it should work for TOTAL_INCREASING sensors, but not TOTAL/None. For TOTAL_INCREASING there needs to be a 'sum' calculation of deltas since last value (in this example I pinched it happens to be reducing over time, hence the negatives).

If we restore a non-prod HA with a backup from prior to the Oziee change, we have the LTS statistics.

Could we not just export those LTS in a CSV, change the prod HA integration to be the same as prior to the Oziee change, and then reload the exported CSV back into prod HA?

In your test you created the CSV from the history so hence didn't have the 'sum' calculation column, but if we export direct from LTS we would be OK?
Might have to do the export as SQL rather than via the HA UI which of course adds to the complexity

[autoSteve]

I honestly think that this is being over-thought, and over-worked in trying to get the history back.

I know you're hurting, @phrozen77, and I hurt too, but surely this is like seeing whether a forecast high temperature from one year back was accurate. It doesn't matter. It mattered the day before, but now it's just an actual stat that happened.

For us, it's just a dotted line on a chart that's missing and makes our eye tick.

The bureau that made the temperature forecast would want to know that they messed up, and why, and how they could do better in future, like Solcast would, but we all are just disappointed that it was inaccurate, and just end the forecasted day with a muttered, "Tossers. I bet they buy some more kit at taxpayer expense to do better..."

There's nothing we can do about their inaccuracies but #rolleyes.

[ProphetOfDoom]

For what it's worth, I'm in agreement with this. The benefits of a renewed focus on resilience and reliability sadly comes with some (but not much) pain. If this integration had simply been left to rot on the vine, there would have been considerably greater pain for users down the line, especially if Solcast take further steps to limit the impact of hobby users on their servers. I think they are getting a good deal.

[phrozen77]

Guys, i managed to do it - in the end, it was as simple as i suspected in my first post here.

solcast.json is in (my case) the root of the docker volume: /var/lib/docker/volumes/hass_config/_data/solcast.json (which is also why i didn't find it at first - i was looking in custom_components/solcast_solar)

I merged the current file with one from the backup, restarted HA, done.

[gcoan]

I've successfully restored my missing Solar forecast in the energy dashboard, although it appears that the problem of losing forecast info from solcast.json has happened before (so there is an underlying weakness somewhere) as my recovered forecasts only go back to mid-March so I'll have to repeat the process from another older backup.

Here's what I did, step by step in case others want to repeat:

1. Restore a copy of /homeassistant/solcast.json from as close a backup to the missing period as you can (the file is presented as /config/solcast.json in HA but this is actually a symbolic link to /homeassistant)
2. Rename the restored solcast.json to something else (e.g. solcast-restored.json) so you don't get any conflict in HA
3. Copy solcast-restored.json into HA (I use Samba share)
4. Make a copy of the existing solcast.json just in case you muck it up...
5. In HA, open Visual Studio Server add-on and open both the solcast.json and solcast-restored.json files
6. The files will appear as a long single line string of JSON. Format the JSON document with right mouse click and choose Format Document, or press Shift+Alt+F. Should now appear as a hierarchical document
7. Click the little down arrow at line 4 to collapse the first site Forecasts. Repeat for the second set of site Forecasts if you have them
8. Then in solcast-restored.json, copy the entire Forecasts block and paste it in at the start of the Forecasts block in solcast.json
9. Expand the original Forecasts block, you'll get a warning in VSS about duplicate node. Remove the '"Forecasts": [' from the start of the second block and the ']' from the end of the first block, then add the extra comma after the end of the last half hour estimate
10. Check and remove any duplicated half hour forecasts between the original solcast.json and solcast-restored.json data. In my case I'd lost forecast data from about 6th June, recovered my solcast-restored.json file from a backup taken on 1st June, but of course this had 7 days of forward forecasts so there was about a day and a few slots of duplicated forecasts
11. Repeat steps 7-10 for second site
12. Close solcast.json in Visual Studio Server
13. Make a post-merge backup copy of solcast.json just in case
14. In HA, go to Integrations/Solcast, click the three dots and Reload the integration
15. And that's it, all your missing Solar forecasts should now be back in the Energy dashboard

[autoSteve]

v4.0.37 carries improvements to exception handling for things, including the startup sequence, and some thought about what could cause solcast.json (or its in-memory structure, really) to go AWOL, then get written as fresh.

If it never happens again then I have fixed it. If it does happen again, then I will eagerly await data points to analyse why.

Nice write-up.

[gcoan]

Additional tip:
6a. If the Format Document option doesn't appear for the JSON file, its probably due to the file size and you will need to increase the Visual Studio setting editor.max tokenisation line length:

If you are missing any solcast.json data from your backups then the Energy dashboard just draws the missing forecast as a straight line. I've recovered 4/1/24 to 7/3/24 and then 21/3/24 to 7/6/24 from two different monthly backups but am missing a bit in the middle:

Off to the January backup to try retrieve last year's data ....

[autoSteve]

That's confirmed from me, @phrozen77. It's a bit of mucking around for multiple rooftop sites, but it works.

This does pose the question: Why did solcast.json go missing around the time of the 'Oziee change' to use TOTAL for state_class was implemented? My belief was that these past forecasts were coming out of LTS, but that clearly isn't the case.

Where did it go? And why??? 🤔

[gcoan]

That's confirmed from me, @phrozen77. It's a bit of mucking around for multiple rooftop sites, but it works.

This does pose the question: Why did solcast.json go missing around the time of the 'Oziee change' to use TOTAL for state_class was implemented? My belief was that these past forecasts were coming out of LTS, but that clearly isn't the case.

Where did it go? And why??? 🤔

Are you suggesting that the historic solar forecasts in the energy dashboard are held in the solcast.json file, which presumably must grow forever in length?

And thus some clever restoring and merging of an old version would get the historic data back if we were that inclined ?

I too really thought the data came from LTS

[autoSteve]

Correct.

It's not forever though. Max 730 days are kept in solcast.json, or the file would get too huge. The code is rigged to purge super-old data.

        #this deletes data that is older than 730 days (2 years)
        pastdays = dt.now(timezone.utc).date() + timedelta(days=-730)
        _forecasts = list(filter(lambda x: x["period_start"].date() >= pastdays, _fcasts_dict.values()))

Mash together an old data set from a backup with the new in the 'forecasts' array for each site. If you get json it's a chore, not a show stopper.

My May view...

[gcoan]

Amazing, well you learn something new every day

Will have a go

[ProphetOfDoom]

@phrozen77 Great spot.

[autoSteve]

"Troubleshooting FAQ" post updated, BTW.

[phrozen77]

Where did it go? And why??? 🤔

But I want to know why. (Code staring tonight for me...) Does the integration drop the solcast.json file for any reason? Don't think so... why would it? Why did this seemingly happen to everyone's install?

Valid questions, tbh - all i can tell you is that it has happened for like 2 or 3 times already to me, which is why it "extra irked" me this time ;)

I kind of suspect it to be some kind of race condition when getting new values from solcast which, when it does fail, nulls the file...

[ProphetOfDoom]

Like the time we had a bug in an ATC Simulator system I one worked on, where the aircraft velocities and positions sometimes made no sense. Eventually tracked down to a bug in a library another team wrote which only threw out crap numbers on the second half of each minute in the second half of each hour!
Good hunting!

[isorin]

@autoSteve
I think the sensor history comes from HA so changing the sensor_class will impact it.
The energy dashboard history comes from solcast.json.
Not sure about the reason as to why we lost solcast.json content, maybe we should add some logging when we write the file, for instance the number of records that goes into it.
Also, SolcastApi.http_data() doesn't catch exceptions, I think we should catch them and log the error (in case writing to the file fails).

[autoSteve]

SolcastApi.http_data() doesn't catch exceptions

Good call. Nor does http_data_call().

(My God I hate underscores in code. Be kind to your typing fingers, people..! How about httpDataCall()?..)

From staring, I've still got nothing as to why solcast.json, or the data that it represents in memory went away. Catching the count in log (even current vs. last count seen) would probably be futile, as after a couple of restarts that log is gone. Plus it would probably be debug level.

[isorin]

http_data_call is called from two places - "load_saved_data" which catches the errors and "http_data"...

[autoSteve]

Then http_data() may be a probable culpret for the data loss moment. But that would mean that load_saved_data() earlier must not have done its job right, leaving the in-memory self._data empty, leading to http_data() not preserving history when it ran when triggered by the next automation call and saving solcast.json. But load_saved_data() does catch exceptions... And it's not that complex. But who would notice in time?

I still got nothing. But yet, maybe an earlier exception was logged and then the http_data() call was successful... This may have happened for many? Unlikely, but maybe. Unlikely, especially as I have been watching logs like a hawk lately.

[autoSteve]

FYI, I think I have uncovered the root of this data loss happening in the first place, and have proposed a change in v4.1.next.

Serialising solcast.json involved this:

              async with self._serialize_lock:
                   async with aiofiles.open(self._filename, 'w') as f:
                       await f.write(json.dumps(self._data, ensure_ascii=False, cls=DateTimeEncoder))

There are a couple of with blocks happening here. The first ensures that nothing messes with the file write. The second opens the file for writing. Then it writes. Then it closes and releases the mutex, because with.

@BJReplay and I both hit a circumstance today while testing code where an exception occurred in json.dumps(). This resulted in an empty file. I can only speculate, because I have not examined the aiofiles code we're using, but my belief is that what happens in this circumstance is the file gets opened for write, then immediately closed (because with block ends post exception). This is likely resulting in an empty file.

The proposal is to prepare the payload completely, then pass to a simple file write block that does not involve a JSON dump as string. This will avoid the issue.