History issues

I’m facing two kind of issues with history data. Mostly the history data just keeps loading but never show up. For couple of sensors it appears, but it is totally false; it shows only one value and it’s not even the current value. See the pictures. Is the history data corrupted somehow? Any help is apreciated!

Any database errors in your log?

Lots of errors. For example:

Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id) VALUES (?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{"entity_id": "sensor.iphone_battery_state", "old_state": {"entity_id": "sensor.iphone_battery_state", "state": "Charging", "attributes": {"Battery S ... (724 characters truncated) ... :00", "last_updated": "2019-06-17T17:39:17.022394+00:00", "context": {"id": "4b9fe3e", "parent_id": null, "user_id": null}}}', 'LOCAL', '2019-06-17 17:39:17.022612', '2019-06-17 17:39:17.065864', '4b9fe3ea966', None)] (Background on this error at: http://sqlalche.me/e/4xp6)
8:39 PM components/recorder/util.py (ERROR)

How to fix this? What could have caused this?

Delete your database and restart. You will lose any previous history.

If you are using the default SQLite db I have seen this occur when upgrading HA. Moving to the MariaDB addon (if using hassio) prevents this from happening.


Thanks! Where can I find it from? Actually, I found the name from somewhere else (home-assistant_v2.db), but I’m not able even to connect Samba on the device. Log says:

check_ntlm_password:  authentication for user [***] -> [***] -> [***] succeeded
Failed to fetch record!
pcap cache not loaded
idmap range not specified for domain '*'

False alarm. For some reason connection succeeded after booting my mac. I was able to delete the db and after that it works again (need to check the mariaDB usage). The db size was about 4GB, does the size just continue increasing or is some old historia data deleted periodically?

There is a purge interval and a number of days to kepp that you can set. Be aware though that restarting HA resets the purge interval timer. You can also exclude entities (or domains) you dont want to track with the recorder to reduce the file size: