Am I right in thinking that this means my database is fubar?

Sym0nd0 · August 1, 2020, 6:13pm

I updated to 0.113.3 the other day and noticed that HACS had disappeared from my install. I restored from a snapshot but then everything started to go wrong, with multiple errors in the log.

I’ve searched and found recommendations to deleted the db and restart, which I did and once back up and running I tried to restore from the oldest snapshot I have but still problems persist, HACs won’t run and the log shows this lot:

2020-08-01 18:57:19 WARNING (Recorder) [homeassistant.components.recorder] Ended unfinished session (id=170 from 2020-08-01 17:48:56.302401)
2020-08-01 18:57:22 ERROR (MainThread) [homeassistant.config] Platform error: sensor - Integration 'badnest' not found.
2020-08-01 18:57:22 ERROR (MainThread) [homeassistant.setup] Setup failed for hacs: Integration not found.
2020-08-01 18:57:22 ERROR (MainThread) [homeassistant.setup] Setup failed for garbage_collection: Integration not found.
2020-08-01 18:57:22 ERROR (MainThread) [homeassistant.setup] Setup failed for badnest: Integration not found.
2020-08-01 18:57:22 ERROR (MainThread) [homeassistant.config] Platform error: camera - Integration 'badnest' not found.
2020-08-01 18:57:32 ERROR (MainThread) [homeassistant.components.wled] Error fetching wled data: Invalid response from API: Error occurred while communicating with WLED device.
2020-08-01 18:57:32 WARNING (MainThread) [homeassistant.config_entries] Config entry for wled not ready yet. Retrying in 5 seconds
2020-08-01 18:57:33 ERROR (Recorder) [homeassistant.components.recorder] 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: ('service_registered', '{"domain": "recorder", "service": "purge"}', 'LOCAL', '2020-08-01 17:57:18.818894', '2020-08-01 17:57:33.969120', 'c3ba81c4963e492aa351588d11f41af7', None, None)]
(Background on this error at: http://sqlalche.me/e/13/4xp6)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, 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/src/homeassistant/homeassistant/components/recorder/__init__.py", line 379, in run
    self.event_session.flush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2523, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2664, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2624, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1014, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1127, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, 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, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: ('service_registered', '{"domain": "recorder", "service": "purge"}', 'LOCAL', '2020-08-01 17:57:18.818894', '2020-08-01 17:57:33.969120', 'c3ba81c4963e492aa351588d11f41af7', None, None)]
(Background on this error at: http://sqlalche.me/e/13/4xp6)
2020-08-01 18:57:34 ERROR (Recorder) [homeassistant.components.recorder] 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: ('service_registered', '{"domain": "recorder", "service": "purge"}', 'LOCAL', '2020-08-01 17:57:18.818894', '2020-08-01 17:57:33.969120', 'c3ba81c4963e492aa351588d11f41af7', None, None)]
(Background on this error at: http://sqlalche.me/e/13/4xp6) (Background on this error at: http://sqlalche.me/e/13/7s2a)
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 379, in run
    self.event_session.flush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2523, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2618, in _flush
    flush_context.transaction = transaction = self.begin(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 953, in begin
    self.transaction = self.transaction._begin(nested=nested)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 317, in _begin
    self._assert_active()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 289, in _assert_active
    raise sa_exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: 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

This isn’t the entirety of the log, but it mostly continues along the same lines and the size of the file grows quickly, it’s up to 65mb atm.

Is there anything that I can do to repair this or do I just need to admit defeat and start over from scratch?

Thanks.

KTibow · August 1, 2020, 6:17pm

I’d recommend shutting down your HA server, plugging the storage into another device, and delete anything that has db in its name. Then plug it back in and power on. If you use a third-party DB, try removing that.

Sym0nd0 · August 1, 2020, 7:03pm

Stupid question now, I’ve a Windows 10 laptop and a RPi Zero (along with my RPi 4 that I use for HA). Is there any way I can access the RAW partition using these?

I have Samba setup but assuming that as HA would be running to access that way it wouldn’t work.

KTibow · August 1, 2020, 7:04pm

Nope, I think only linux supports that. I use an RPi to access my RPi.

francisp · August 1, 2020, 7:22pm

Delete the database and restart = OK
But then you restore a snapshot, causing a restored corrupted database.
So the answer is : delete again and restart.

Sym0nd0 · August 1, 2020, 7:44pm

So, starting from fresh it is then? Deep down, I knew this would be the only way.

Thanks both of you.

Sir_Goodenough · September 22, 2020, 4:37am

You could try this: