Database issue in 0.91.1

Configuration
1

Just happened to glance at my logs this evening after upgrading to 0.91.1 and its full of this…

Log Details (ERROR)
Sat Apr 06 2019 18:53:30 GMT+0100 (British Summer Time)
Error saving event: <Event state_changed[L]: entity_id=sun.sun, old_state=<state sun.sun=above_horizon; next_dawn=2019-04-07T04:56:12+00:00, next_dusk=2019-04-06T19:24:18+00:00, next_midnight=2019-04-07T00:11:00+00:00, next_noon=2019-04-07T12:11:09+00:00, next_rising=2019-04-07T05:30:52+00:00, next_setting=2019-04-06T18:49:44+00:00, elevation=8.32, azimuth=270.61, friendly_name=Sun, custom_ui_state_card=state-card-custom-ui @ 2019-04-06T15:20:29.626109+01:00>, new_state=<state sun.sun=above_horizon; next_dawn=2019-04-07T04:56:12+00:00, next_dusk=2019-04-06T19:24:18+00:00, next_midnight=2019-04-07T00:11:00+00:00, next_noon=2019-04-07T12:11:09+00:00, next_rising=2019-04-07T05:30:52+00:00, next_setting=2019-04-06T18:49:44+00:00, elevation=8.17, azimuth=270.81, friendly_name=Sun, custom_ui_state_card=state-card-custom-ui @ 2019-04-06T15:20:29.626109+01:00>>
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlite3.DatabaseError: database disk image is malformed

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/homeassistant/components/recorder/__init__.py", line 298, in run
    session.flush()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2451, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2589, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2549, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1120, in _emit_insert_statements
    statement, params
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
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: ('state_changed', '{"entity_id": "sun.sun", "old_state": {"entity_id": "sun.sun", "state": "above_horizon", "attributes": {"next_dawn": "2019-04-07T04:56:12+00:00", "ne ... (1020 characters truncated) ... :00", "last_updated": "2019-04-06T17:53:30.009629+00:00", "context": {"id": "809e92aeb86745e781ba7f558dcd9431", "parent_id": null, "user_id": null}}}', 'LOCAL', '2019-04-06 17:53:30.009842', '2019-04-06 17:53:30.025069', '809e92aeb86745e781ba7f558dcd9431', None)]
(Background on this error at: http://sqlalche.me/e/4xp6)

Does not sound good…, any ideas

2

Looks like your db file is corrupt, or you have a filesystem problem.
This has nothing to do with the latest release.
First i would try do delete the db file (stop HA before, you will lose your history)

3

Just out of curiosity, is the database being stored on an SD card?

4

Cool, I’ve only been using HA for a few days, so would appreciate a few more specific instructions if you don’t mind…, I have SSH command line access to my system, so you can assume I’m logged in a the command prompt…

And yes, it’s a brand new class 10 32Gb SD card in a Raspberry Pi 3 B+. I was running on a Pi A and moved over to the B+ today, did a brand new install, then restored previous config from a snapshot.

Thanks.
Phil.

5

The file is home-assistant_v2.db and is located in the configuration directory.

As mentioned, it’s advisable to stop Home Assistant prior to deleting the database file. Instructions for stopping/re-starting Home Assistant depend on how you installed it.

Is it:
Hassbian
Hass.io
Venv on Linux
Docker

If it’s hass.io then you’ll need to remotely connect via SSH and run:
hassio ha stop

After you’ve deleted the database file, run:
hassio ha start

The reason I asked if you were using an SD card is because they have a limited number of write-cycles (far less than an SSD) and normally don’t do wear-leveling (they don’t avoid writing to the same ‘place’ on the SD card repeatedly).

If you are using a Raspberry Pi that can be subjected to power-interruptions (without the benefit of an orderly shutdown first) it can corrupt the SD card’s contents. Lastly, the Raspberry Pi is sensitive to undervoltage situations (i.e. a weak power supply). If there’s insufficient voltage, the RPi will throttle down its CPU and can become susceptible to rebooting itself unceremoniously and other undesirable things.

So:

  • Use a 5 VDC power supply able to deliver at least 2.4 Amperes.
  • Protect the RPi from random power interruptions (using an inexpensive battery pack or, even better, a UPS).

Following these protective measures helps to keep you from becoming part of the very unhappy minority of users who post bitter complaints about their RPi “eating” SD cards every few weeks (or months).

3 Likes
6

Excellent, thanks.

Stopped, deleted and restarted…, all looks good. no errors in the logs.

7

By the way even your problem is solved it might be your SD Card having some issues. Even it’s new ! SD Card are the weak point on raspberry :confused:

8

Hi Everybody,
I had the same issue today with my system and I was able to fix it a minute ago.
Thanks for brilliant instructions here!
Best regards,
Torsten

9

Thanks for the instructions :slight_smile: Had the problem today, fixed it. Have now ordered a 128 GB SanDisk Extreme Pro USB 3.1 Solid State Flash Drive. https://shop.westerndigital.com/products/usb-flash-drives/sandisk-extreme-pro-usb-3-1#SDCZ880-128G-A46

Brgds.
Poul