No historical data

Configuration
1

So let me explain first: for example, I have several BLE temperature sensors that I gather their data from over an wemos d1 and esphome. They update their values quite often, so I used to have a chart with a lot of “movement”.

MAybe also important: Recently I had to restore my install of HA, which worked fine thanks to a regular backup I arranged. This last restoring process worked fine and without any problems. However I can not exclude it as a possible reason for my problem

So now speeking about my problem: The temperature sensors still work and send values, which seems to arrive at HA but there is no historical data for these sensors, actually there seems to be no historical data for any sensor. I checked my config and included “history:” which did not worked.

Also I realized, that all history charts do not show any data. The mini graph card shows unreasonable values (or at least some I can not explain). The history chart by HA says: “no history data available” if I type in more than 1 sensor. I attach a screenshot. left: original “live” values (they are correct), middle top: history chart by HA with more than 1 sensor, middle bottom and left: mini graph card with incorrect values.

My log says:

  • Error saving events: This Session’s transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: (‘state_changed’, ‘{}’, ‘LOCAL’, ‘2020-09-16 19:55:49.150956’, ‘2020-09-16 19:55:49.156842’, ‘9e189037f85611ea9b32c783bc4eec1e’, None, None)] (Background on this error at: http://sqlalche.me/e/13/4xp6) (Background on this error at: http://sqlalche.me/e/13/7s2a)

  • 21:56:00 – Recorder (ERROR) - Die Nachricht ist zum ersten Mal um 21:39:19 aufgetreten und erscheint 348 mal

  • Error executing query: This Session’s transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: (‘state_changed’, ‘{}’, ‘LOCAL’, ‘2020-09-16 19:55:49.150956’, ‘2020-09-16 19:55:49.156842’, ‘9e189037f85611ea9b32c783bc4eec1e’, None, None)] (Background on this error at: http://sqlalche.me/e/13/4xp6) (Background on this error at: http://sqlalche.me/e/13/7s2a)

  • 21:56:00 – Recorder (ERROR) - Die Nachricht ist zum ersten Mal um 21:39:19 aufgetreten und erscheint 348 mal

  • Error adding state change: This Session’s transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: (‘state_changed’, ‘{}’, ‘LOCAL’, ‘2020-09-16 19:55:49.150956’, ‘2020-09-16 19:55:49.156842’, ‘9e189037f85611ea9b32c783bc4eec1e’, None, None)] (Background on this error at: http://sqlalche.me/e/13/4xp6) (Background on this error at: http://sqlalche.me/e/13/7s2a)

  • 21:55:59 – Recorder (ERROR) - Die Nachricht ist zum ersten Mal um 21:38:26 aufgetreten und erscheint 1823 mal

  • Error adding event: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: (‘state_changed’, ‘{}’, ‘LOCAL’, ‘2020-09-16 19:55:49.150956’, ‘2020-09-16 19:55:49.156842’, ‘9e189037f85611ea9b32c783bc4eec1e’, None, None)] (Background on this error at: http://sqlalche.me/e/13/4xp6)

  • 21:55:59 – Recorder (ERROR) - Die Nachricht ist zum ersten Mal um 21:38:26 aufgetreten und erscheint 355 mal

  • Error adding event: This Session’s transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: (‘state_changed’, ‘{}’, ‘LOCAL’, ‘2020-09-16 19:55:46.172732’, ‘2020-09-16 19:55:46.186721’, ‘9c5221e3f85611eaaadd5f37241c62ce’, None, None)] (Background on this error at: http://sqlalche.me/e/13/4xp6) (Background on this error at: http://sqlalche.me/e/13/7s2a)

  • 21:55:55 – Recorder (ERROR) - Die Nachricht ist zum ersten Mal um 21:38:26 aufgetreten und erscheint 1824 mal

  • Update for sensor.wohnung_oluftfeuchtigkeit_3_tage fails

  • 21:55:46 – components/recorder/util.py (ERROR) - Die Nachricht ist zum ersten Mal um 21:38:29 aufgetreten und erscheint 36 mal

  • Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: SELECT states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated FROM states WHERE states.last_updated < ? AND states.entity_id = ? ORDER BY states.last_updated DESC LIMIT ? OFFSET ?] [parameters: (‘2020-09-13 19:53:46.178110’, ‘sensor.wohnzimmer_luftfeuchtigkeit_vidtemp2’, 1, 0)] (Background on this error at: http://sqlalche.me/e/13/4xp6)

  • 21:55:46 – Recorder (ERROR) - Die Nachricht ist zum ersten Mal um 21:38:29 aufgetreten und erscheint 36 mal

  • Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: SELECT states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated FROM states WHERE states.last_updated < ? AND states.entity_id = ? ORDER BY states.last_updated DESC LIMIT ? OFFSET ?] [parameters: (‘2020-09-13 19:53:46.178110’, ‘sensor.wohnzimmer_luftfeuchtigkeit_vidtemp2’, 1, 0)] (Background on this error at: http://sqlalche.me/e/13/4xp6)

  • 21:55:46 – Recorder (ERROR) - Die Nachricht ist zum ersten Mal um 21:38:28 aufgetreten und erscheint 108 mal

  • Update for sensor.disk_space_custom_components fails

  • 21:55:46 – custom_components/diskspace/sensor.py (ERROR) - Die Nachricht ist zum ersten Mal um 21:38:46 aufgetreten und erscheint 35 mal

  • 21:55:46 – diskspace (WARNING) - Die Nachricht ist zum ersten Mal um 21:38:46 aufgetreten und erscheint 35 mal

  • Error in database connectivity during keepalive: This Session’s transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: (‘state_changed’, ‘{}’, ‘LOCAL’, ‘2020-09-16 19:43:03.976881’, ‘2020-09-16 19:43:03.980357’, ‘d60447aff85411ea91a58d0a30e9d204’, None, None)] (Background on this error at: http://sqlalche.me/e/13/4xp6) (Background on this error at: http://sqlalche.me/e/13/7s2a)

  • 21:55:34 – Recorder (ERROR) - Die Nachricht ist zum ersten Mal um 21:39:19 aufgetreten und erscheint 7 mal

I can understand that these logs seem to have something to do with my problem, but I do not understand them. Hopefully could explain?

Thank you all for you help!

Unbenannt
Unbenannt1603×437 35.5 KB

2

Make sure that you don’t have a corrupted SD card, reset any history, logbook, and recorder configs, delete home-assistant_v2.db, and restart HA.

1 Like
3

Thank you @KTibow
I deleted home-assistant_v2.db, reboot and everything worked again.

4

I am strongly wondering how everyone can accept the “just delete that damn sqlite db and your good to go!” workaround so all history data is gone.

I restored a snapshot and ended up in the same situation, things didn´t work. I tested it 3 times, in every test case the SQLite database got corrupted. Either during backup (creating the snapshot) or restore process.

Just not acceptable to me to loose all my history data every now and then. That´s not a reliable backup and restore progress at all!

Would be nice to see some core devs working on that issue because it´s a MASSIVE one and widely spread, see all the topics. For me personal I think I´m gonna switch to MySQL, i don´t trust that SQLite stuff anymore.

Update: Fix corrupted / malformed SQLite database (home-assistant_v2.db)

5

I cant speak for others, but I only store 2 days in the built in DB since it’s slower to access and grows quite large. I have a year+ of history in influx for long-term storage and display, which also takes up less space.

6

Yes that´s very likely the right concept. I´m just starting with HA and having a look at how to put all the data in an influx database (you got a hint how to achieve this?) is my next urgent step. Anyway, even if only a few days are lost, I don´t tolerate any data loss :slight_smile:

7

No specific hints, but it’s quite easy if you are using the InfluxBD addon!

I just checked it again, and the documentation for the addon covers everything. You install it, set any config options if desired (I don’t use any), then add it to configuration.yaml (there’s an example of what to add in the docs too).
And that’s it.

I also use Grafana for displaying the data. I have some dashboards open it to check, and some in lovelace as a webpage card. The information for connecting the grafana addon to influx is in the documentation for the grafana addon.

1 Like
8

Integrating InfluxDB was really quite easy as you said, 15 to 30 minutes job. Unfortunately the history data is not transferred, only the data starting from connecting InfluxDB to HA. Anyway, now I can purge and repack the SQLite database.

1 Like