History issues

rantaki3 · June 17, 2019, 5:20pm

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!

tom_l · June 17, 2019, 5:38pm

Any database errors in your log?

rantaki3 · June 17, 2019, 5:42pm

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?

tom_l · June 17, 2019, 10:10pm

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.

rantaki3 · June 18, 2019, 3:18am

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 '*'

rantaki3 · June 18, 2019, 3:54am

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?

tom_l · June 18, 2019, 3:56am

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: