Database problem after 0.113.3 update: HA don't start

Configuration
1

I am upgrading to 0.113.3 from 0.111.
My homeassitant run on docker ubuntu 18.04 LTS in a very old pc so I understand it could take a while to update the database but is almost 3 hours it is offline the log seems to me show a stuck procedure, is it normal?. Can I delete the database and restart for fix this ?

> 2020-08-04 08:02:39 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:03:39 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:04:39 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:05:39 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:06:39 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:07:39 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:08:28 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
> [SQL: CREATE INDEX ix_events_context_parent_id ON events (context_parent_id)]
> (Background on this error at: http://sqlalche.me/e/13/4xp6)
> 2020-08-04 08:08:28 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (sqlite3.DatabaseError) database disk image is malformed
> [SQL: CREATE INDEX ix_events_context_parent_id ON events (context_parent_id)]
> (Background on this error at: http://sqlalche.me/e/13/4xp6) (retrying in 3 seconds)
> 2020-08-04 08:08:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 7
> 2020-08-04 08:08:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_parent_id to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 08:08:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_parent_id already exists on events, continuing
> 2020-08-04 08:08:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns old_state_id to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 08:08:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Column old_state_id already exists on states, continuing
> 2020-08-04 08:08:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_context_parent_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 08:08:39 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:09:39 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:10:39 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:11:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:12:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:13:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:14:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:15:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:16:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:17:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:18:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:19:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:20:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:21:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:22:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:23:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:24:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:25:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:26:12 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
> [SQL: CREATE INDEX ix_events_context_parent_id ON events (context_parent_id)]
> (Background on this error at: http://sqlalche.me/e/13/4xp6)
> 2020-08-04 08:26:12 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (sqlite3.DatabaseError) database disk image is malformed
> [SQL: CREATE INDEX ix_events_context_parent_id ON events (context_parent_id)]
> (Background on this error at: http://sqlalche.me/e/13/4xp6) (retrying in 3 seconds)
> 2020-08-04 08:26:15 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 7
> 2020-08-04 08:26:15 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_parent_id to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 08:26:15 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_parent_id already exists on events, continuing
> 2020-08-04 08:26:15 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns old_state_id to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 08:26:15 WARNING (Recorder) [homeassistant.components.recorder.migration] Column old_state_id already exists on states, continuing
> 2020-08-04 08:26:15 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_context_parent_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 08:26:40 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:27:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:28:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:29:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:30:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:31:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:32:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:33:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:34:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:35:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:36:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:37:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:38:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:39:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:40:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:41:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:42:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:43:41 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:43:53 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
> [SQL: CREATE INDEX ix_events_context_parent_id ON events (context_parent_id)]
> (Background on this error at: http://sqlalche.me/e/13/4xp6)
> 2020-08-04 08:43:53 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (sqlite3.DatabaseError) database disk image is malformed
> [SQL: CREATE INDEX ix_events_context_parent_id ON events (context_parent_id)]
> (Background on this error at: http://sqlalche.me/e/13/4xp6) (retrying in 3 seconds)
> 2020-08-04 08:43:56 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 7
> 2020-08-04 08:43:56 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_parent_id to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 08:43:56 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_parent_id already exists on events, continuing
> 2020-08-04 08:43:56 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns old_state_id to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 08:43:56 WARNING (Recorder) [homeassistant.components.recorder.migration] Column old_state_id already exists on states, continuing
> 2020-08-04 08:43:56 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_context_parent_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 08:44:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:45:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:46:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:47:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:48:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:49:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:50:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:51:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:52:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:53:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:54:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:55:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:56:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:57:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:58:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 08:59:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:00:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:01:28 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
> [SQL: CREATE INDEX ix_events_context_parent_id ON events (context_parent_id)]
> (Background on this error at: http://sqlalche.me/e/13/4xp6)
> 2020-08-04 09:01:28 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (sqlite3.DatabaseError) database disk image is malformed
> [SQL: CREATE INDEX ix_events_context_parent_id ON events (context_parent_id)]
> (Background on this error at: http://sqlalche.me/e/13/4xp6) (retrying in 3 seconds)
> 2020-08-04 09:01:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 7
> 2020-08-04 09:01:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns context_parent_id to table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 09:01:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Column context_parent_id already exists on events, continuing
> 2020-08-04 09:01:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns old_state_id to table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 09:01:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Column old_state_id already exists on states, continuing
> 2020-08-04 09:01:31 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_events_context_parent_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
> 2020-08-04 09:01:42 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:02:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:03:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:04:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:05:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:06:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:07:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:08:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:09:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:10:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:11:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:12:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:13:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:14:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:15:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:16:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
> 2020-08-04 09:17:43 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
1 Like
2

Hello Claustro,

I have the same problem. Are you able to login now?
If so, did you just waited?

Greetings,

3

I deleted the database (home-assistant_v2.db) and then rebooted. this solved my problem.
Maybe you can rename it , just in case , and then delete in case you 'll solve.
bye

1 Like
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)