I migrated hassio from Pi to Ubuntu server and restored a snapshot. All appears to be functioning ok, except that I don’t have any state history for any of my sensor. For example, the screenshot shown below would have had a graph before the migration, but it is totally absent now. Any ideas on how to get this back?
The same situation in history too:
Database image is probably corrupt (look at the logs and you’ll probably see “database disk image is malformed” a bunch of times). Common when restoring snapshots. Delete home-assistant_v2.db and restart home assistant and it’ll start recording history again. Might also want to switch to a different database backend like mariadb to prevent this in the future
Any way to repare the DB instead of creating it new (like most sqlite tools provide it … e.g. via Dump or vacuum)?
After running
./sqlite3 ../home-assistant_v2.db ".recover" | ./sqlite3 ../fixed.db
I’ve got the data recovered in lost and found, mayb someone can help to find the right collums:
rootpgno,pgno,nfield,id,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10
516,17,8,341447,,state_changed,{"entity_id": "sensor.fritz_netmonitor", "old_state": {"entity_id": "sensor.fritz_netmonitor", "state": "online", "attributes": {"is_linked": true, "is_connected": true, "wan_access_type": "DSL", "external_ip": "84.130.77.36", "uptime": 53278, "bytes_sent": 558903106, "bytes_received": 2938360443, "transmission_rate_up": 312, "transmission_rate_down": 490, "max_byte_rate_up": 139500.0, "max_byte_rate_down": 1953000.0, "friendly_name": "fritz_netmonitor", "icon": "mdi:web"}, "last_changed": "2020-01-22T01:53:06.304451+00:00", "last_updated": "2020-01-25T16:37:04.274810+00:00", "context": {"id": "41836d280b0d4a72bb45143c55a99b33", "parent_id": null, "user_id": null}}, "new_state": {"entity_id": "sensor.fritz_netmonitor", "state": "online", "attributes": {"is_linked": true, "is_connected": true, "wan_access_type": "DSL", "external_ip": "84.130.77.36", "uptime": 53309, "bytes_sent": 558940715, "bytes_received": 2938487099, "transmission_rate_up": 1212, "transmission_rate_down": 4083, "max_byte_rate_up": 139500.0, "max_byte_rate_down": 1953000.0, "friendly_name": "fritz_netmonitor", "icon": "mdi:web"}, "last_changed": "2020-01-22T01:53:06.304451+00:00", "last_updated": "2020-01-25T16:37:35.287940+00:00", "context": {"id": "8a35782cb7ee4db98e953e15436a4a00", "parent_id": null, "user_id": null}}},LOCAL,2020-01-25 16:37:35.288113,2020-01-25 16:37:35.313568,8a35782cb7ee4db98e953e15436a4a00,,,,
Got my data back via:
INSERT INTO events (event_id, event_type, event_data, origin, time_fired, created, context_id)
SELECT id, c1, c2, c3, c4, c5, c6
FROM lost_and_found
Thanks for the tip. I had the same issue after a rebuild and restore (I was still running Resin OS somehow?!?!!).
I deleted the db and all looks perfect again now.
I guess in the official documentation, it may be worth adding a comment that the db may get corrupted and should either be excluded or repaired afterwards.
I also have the problem with “no state history found” after restoring a Snapshot.
I also use influxDB with grafana…
When i delete the database, will my influxDB Data still be there and will it just continue getting Data from hassio?
I dont want to loose my data in influxDB…
Thanks 
(I am using Hassio image on a Rpi 3b, no Docker container or anything…)
Deleting you HA database has no effect on the influxdb database.
I have a somewhat related problem, but only with two devices/entities. Two z-wave temp sensors (one Fibaro door/window sensor and NeoCoolcam PIR w/temp) that I have change the device and entity name does not show the “state history graph”. Tried deleting the home-assistant_v2.db to have it rebuilt by HA, but still “No statet history found.”
Any tips on how to solve this without removing and adding the devices?
Same issue here … Already deleted the db a few times and see no entries in log file regarding bad db … Also I notice the “History” tab on the left is missing … Any solutions or hints of what I could check/do ?
if the history tab is missing, you have to add
history:
to your configuration.yaml and restart HA
That did the trick, thanks!
Thank you.
I am using Home Assistant 2021.8.5 under raspi4/ubuntu/official docker container.
Up until 2-3 major revisions ago, all sensors could retain history data. Now, I get the dreaded “No state history found” message on the UI.
history: has been added to configuration.yaml, the database has been completely purged and left to fill up again for some days, the logging level was increased to debug… nothing! Still no history data and still no error message whatsoever regarding the db and/or historical data.
Anyone who experiences similar behavior or any suggestions?
Thanks!
Hi,
I have exactly this problem on an Intel Nuc with the official latest docker images. Using the mariadb image for data storage.
Maybe someone has an idea on how to investigate this problem so we can come to a fix.