I’m using the Energy dashboard for some of weeks now (since the end of september approximately). I use SlimmeLezer to extract the power- and gas usage, and my SMA-integration for the solar grid production.
This was all working fine, but since this morning my Energy dashboard lost all of its data, or at least it’s not showing any data anymore. Only the data of today is shown correctly.
I have tried to update to the last HA release (2021.11.1) today, but unfortunately no luck.
Just checked the log and I see the log is giving me errors for my Energy entities, which is probably the cause of my problem(?):
2021-11-05 09:02:57 ERROR (SyncWorker_2) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) no such column: statistics_meta.name
[SQL: SELECT statistics_meta.id AS statistics_meta_id, statistics_meta.statistic_id AS statistics_meta_statistic_id, statistics_meta.source AS statistics_meta_source, statistics_meta.unit_of_measurement AS statistics_meta_unit_of_measurement, statistics_meta.has_mean AS statistics_meta_has_mean, statistics_meta.has_sum AS statistics_meta_has_sum, statistics_meta.name AS statistics_meta_name
FROM statistics_meta
WHERE statistics_meta.statistic_id IN (?, ?, ?, ?, ?, ?)]
[parameters: ('sensor.energy_consumed_tariff_1', 'sensor.energy_consumed_tariff_2', 'sensor.energy_produced_tariff_1', 'sensor.energy_produced_tariff_2', 'sensor.pv_gen_meter', 'sensor.gas_consumed')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Please see screenshot below. This is shown for all days before today. Anyone knows what went wrong here and how can I recover the data?
Not sure if it’s possible to recover this?
If not, is it OK to delete this file? I tried backing up the file via FTP, but HA does not seem to like that…
Filezilla can’t finish the copy from HA directory to local machine and during the copy process HA freezes.
yes, probably your database got somehow corrupted (I had this too in the past, whenever I wanted to fix a bad reading in the DB)…
unfortunately I haven’t found any way to fix this kind of issues - resulting in a data loss until now.
This is, why I have changed to a MariaDB as recorder, running on a different server than HomeAssistant.
I think, maria DB would be a bit more reliable than such a SQLite file… and I could create backups more easily…
I think, there’s a way to restore such old values - BUT, it would require a lot of manual effort and some basic knowledge about databases…
you could stop HomeAssistant, backup all existing database files (even the corrupted ones) - export the data.
You could then import the data into a new DB - but you probably need to do some recalculations on the statistics… and with some luck, Homeassistant could use the newly created database.
But since I haven’t done tests on it, I am not sure if this could work.
anyone have a write up on the mariadb option? I’m pretty frustrated with constantly losing my energy data… this was the 6th time in 2 years to wipe it out. Would be great for some type of integrity checker to be worked into update workflows.
EDIT/UPDATE: found some youtube videos on it… going mysql now
quick reply to the thread… was able to recover the corrupted sqlite3 db
NOTE: I am using docker, so was able to perform this from the host:/usr/share/hassio/homeassistant/
then checked the integrity with… sqlite3 home-assistant_v2.db "PRAGMA integrity_check"
Once you get an “OK” from the integrity check, you can delete these files. (They will rebuild)
home-assistant_v2.db-shm
home-assistant_v2.db-wal
then start homeassistant back up. I did have a single day of anomalies on my Energy dashboard. I believe this was related to the corruption, so I was fine in waiting a day to get through that part.
Afterwards, I perform the conversion and imported the DB into mariadb so I am no longer plagued with this in the future.
Just a few questions (my DB is corrupt as well):
Shouldn’t this be hadump.sql as the home-assistant_v2.db is corrupt.
What is the dumped text file otherwise?
