Hi,
I’ve installed hassio with docker-ce, following this tutorial: https://github.com/home-assistant/hassio-installer
I noticed today it wasn’t working anymore: no web interface, no ssh login anymore.
Because I can’t login into hassio, I’ve checked the home-assistant.log into my host:
sqlalchemy.exc.DatabaseError: (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: ('homeassistant_stop', '{}', 'LOCAL', '2020-02-13 14:44:57.587756', '2020-02-13 14:44:57.864928', '568338bb7903479c82b4706d69046eb3', None)]
(Background on this error at: http://sqlalche.me/e/4xp6)
The stack traces are long and all that could be seen from those is that home assistant tried to log to the broken sqlite database that it’s trying to stop.
Also, I’m sorry to be the bearer of bad news, but that kind of thing happens with home assistant and sqlite, I’m personally not sure why. Some folks say they’ve had better luck with mysql, but I haven’t tried it. I’m also not sure how docker save that snapshots rely on interacts with databases (where we have writes happening in the middle of files).
I use influxdb for history of my own that I look at with grafana dashboards. InfluxDB uses lsm based storage, which means that files are only ever appended to. This is because I found sqlite slow, and also because once in a while I have to delete the sqlite db and start over. In the last year, I haven’t lost much data with influxdb. (only when my sd card died and I had to restore onto a new card from a previous daily snapshot - not lost because of influxdb but due to other reasons). It’s also much faster than sqlite and my database takes up less storage space compared to sqlite.