DB image malformed. How to fix it?

CZBios · November 1, 2018, 10:01pm

Hi, after some hussel around sd card images (i.e. restoring snapshots) and some updates this errors occures:

2018-11-01 22:37:01 ERROR (SyncWorker_4) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT events.event_id AS events_event_id, events.event_type AS events_event_type, events.event_data AS events_event_data, events.origin AS events_origin, events.time_fired AS events_time_fired, events.created AS events_created, events.context_id AS events_context_id, events.context_user_id AS events_context_user_id \nFROM events LEFT OUTER JOIN states ON events.event_id = states.event_id \nWHERE events.event_type IN (?, ?, ?, ?, ?, ?) AND events.time_fired > ? AND events.time_fired < ? AND (states.last_updated = states.last_changed OR states.state_id IS NULL) ORDER BY events.time_fired'] [parameters: ('state_changed', 'logbook_entry', 'homeassistant_start', 'homeassistant_stop', 'alexa_smart_home', 'homekit_state_change', '2018-10-31 23:00:00.000000', '2018-11-01 23:00:00.000000')] (Background on this error at: http://sqlalche.me/e/4xp6)
2018-11-01 22:37:02 ERROR (SyncWorker_4) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT events.event_id AS events_event_id, events.event_type AS events_event_type, events.event_data AS events_event_data, events.origin AS events_origin, events.time_fired AS events_time_fired, events.created AS events_created, events.context_id AS events_context_id, events.context_user_id AS events_context_user_id \nFROM events LEFT OUTER JOIN states ON events.event_id = states.event_id \nWHERE events.event_type IN (?, ?, ?, ?, ?, ?) AND events.time_fired > ? AND events.time_fired < ? AND (states.last_updated = states.last_changed OR states.state_id IS NULL) ORDER BY events.time_fired'] [parameters: ('state_changed', 'logbook_entry', 'homeassistant_start', 'homeassistant_stop', 'alexa_smart_home', 'homekit_state_change', '2018-10-31 23:00:00.000000', '2018-11-01 23:00:00.000000')] (Background on this error at: http://sqlalche.me/e/4xp6)
2018-11-01 22:37:02 ERROR (MainThread) [aiohttp.server] Error handling request
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, 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.6/site-packages/aiohttp/web_protocol.py", line 390, in start
    resp = await self._request_handler(request)
  File "/usr/local/lib/python3.6/site-packages/aiohttp/web_app.py", line 366, in _handle
    resp = await handler(request)
  File "/usr/local/lib/python3.6/site-packages/aiohttp/web_middlewares.py", line 106, in impl
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/static.py", line 66, in staticresource_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/real_ip.py", line 34, in real_ip_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/ban.py", line 66, in ban_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/auth.py", line 68, in auth_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/real_ip.py", line 34, in real_ip_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/ban.py", line 66, in ban_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/auth.py", line 68, in auth_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/view.py", line 113, in handle
    result = await result
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/logbook.py", line 155, in get
    return await hass.async_add_job(json_events)
  File "/usr/local/lib/python3.6/concurrent/futures/thread.py", line 56, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/logbook.py", line 153, in json_events
    _get_events(hass, self.config, start_day, end_day, entity_id)))
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/logbook.py", line 338, in _get_events
    events = execute(query)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/recorder/util.py", line 64, in execute
    (row.to_native() for row in qry)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2988, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3011, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT events.event_id AS events_event_id, events.event_type AS events_event_type, events.event_data AS events_event_data, events.origin AS events_origin, events.time_fired AS events_time_fired, events.created AS events_created, events.context_id AS events_context_id, events.context_user_id AS events_context_user_id \nFROM events LEFT OUTER JOIN states ON events.event_id = states.event_id \nWHERE events.event_type IN (?, ?, ?, ?, ?, ?) AND events.time_fired > ? AND events.time_fired < ? AND (states.last_updated = states.last_changed OR states.state_id IS NULL) ORDER BY events.time_fired'] [parameters: ('state_changed', 'logbook_entry', 'homeassistant_start', 'homeassistant_stop', 'alexa_smart_home', 'homekit_state_change', '2018-10-31 23:00:00.000000', '2018-11-01 23:00:00.000000')] (Background on this error at: http://sqlalche.me/e/4xp6)
2018-11-01 22:37:04 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated, states.created AS states_created, states.context_id AS states_context_id, states.context_user_id AS states_context_user_id \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated \nFROM states \nWHERE states.last_updated >= ? AND states.last_updated < ? GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id \nWHERE states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?)'] [parameters: ('2018-10-29 20:44:05.094442', '2018-10-31 23:00:00.000000', 'zone', 'scene', 'zone', 'scene')] (Background on this error at: http://sqlalche.me/e/4xp6)
2018-11-01 22:37:05 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated, states.created AS states_created, states.context_id AS states_context_id, states.context_user_id AS states_context_user_id \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated \nFROM states \nWHERE states.last_updated >= ? AND states.last_updated < ? GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id \nWHERE states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?)'] [parameters: ('2018-10-29 20:44:05.094442', '2018-10-31 23:00:00.000000', 'zone', 'scene', 'zone', 'scene')] (Background on this error at: http://sqlalche.me/e/4xp6)
2018-11-01 22:37:05 ERROR (MainThread) [aiohttp.server] Error handling request
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, 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.6/site-packages/aiohttp/web_protocol.py", line 390, in start
    resp = await self._request_handler(request)
  File "/usr/local/lib/python3.6/site-packages/aiohttp/web_app.py", line 366, in _handle
    resp = await handler(request)
  File "/usr/local/lib/python3.6/site-packages/aiohttp/web_middlewares.py", line 106, in impl
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/static.py", line 66, in staticresource_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/real_ip.py", line 34, in real_ip_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/ban.py", line 66, in ban_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/auth.py", line 68, in auth_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/real_ip.py", line 34, in real_ip_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/ban.py", line 66, in ban_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/auth.py", line 68, in auth_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/view.py", line 113, in handle
    result = await result
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 332, in get
    entity_ids, self.filters, include_start_time_state)
  File "/usr/local/lib/python3.6/concurrent/futures/thread.py", line 56, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 93, in get_significant_states
    include_start_time_state)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 239, in states_to_json
    for state in get_states(hass, start_time, entity_ids, filters=filters):
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 214, in get_states
    return [state for state in execute(query)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/recorder/util.py", line 64, in execute
    (row.to_native() for row in qry)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2988, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3011, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated, states.created AS states_created, states.context_id AS states_context_id, states.context_user_id AS states_context_user_id \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated \nFROM states \nWHERE states.last_updated >= ? AND states.last_updated < ? GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id \nWHERE states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?)'] [parameters: ('2018-10-29 20:44:05.094442', '2018-10-31 23:00:00.000000', 'zone', 'scene', 'zone', 'scene')] (Background on this error at: http://sqlalche.me/e/4xp6)
2018-11-01 22:37:17 WARNING (MainThread) [homeassistant.components.sensor] Updating mitemp_bt sensor took longer than the scheduled update interval 0:00:30
2018-11-01 22:37:23 ERROR (SyncWorker_17) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated, states.created AS states_created, states.context_id AS states_context_id, states.context_user_id AS states_context_user_id \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated \nFROM states \nWHERE states.last_updated >= ? AND states.last_updated < ? GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id \nWHERE states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?)'] [parameters: ('2018-10-29 20:44:05.094442', '2018-10-31 23:00:00.000000', 'zone', 'scene', 'zone', 'scene')] (Background on this error at: http://sqlalche.me/e/4xp6)
2018-11-01 22:37:24 ERROR (MainThread) [aiohttp.server] Error handling request
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, 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.6/site-packages/aiohttp/web_protocol.py", line 390, in start
    resp = await self._request_handler(request)
  File "/usr/local/lib/python3.6/site-packages/aiohttp/web_app.py", line 366, in _handle
    resp = await handler(request)
  File "/usr/local/lib/python3.6/site-packages/aiohttp/web_middlewares.py", line 106, in impl
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/static.py", line 66, in staticresource_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/real_ip.py", line 34, in real_ip_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/ban.py", line 66, in ban_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/auth.py", line 68, in auth_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/real_ip.py", line 34, in real_ip_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/ban.py", line 66, in ban_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/auth.py", line 68, in auth_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/view.py", line 113, in handle
    result = await result
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 332, in get
    entity_ids, self.filters, include_start_time_state)
  File "/usr/local/lib/python3.6/concurrent/futures/thread.py", line 56, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 93, in get_significant_states
    include_start_time_state)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 239, in states_to_json
    for state in get_states(hass, start_time, entity_ids, filters=filters):
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 214, in get_states
    return [state for state in execute(query)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/recorder/util.py", line 64, in execute
    (row.to_native() for row in qry)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2988, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3011, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated, states.created AS states_created, states.context_id AS states_context_id, states.context_user_id AS states_context_user_id \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated \nFROM states \nWHERE states.last_updated >= ? AND states.last_updated < ? GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id \nWHERE states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?)'] [parameters: ('2018-10-29 20:44:05.094442', '2018-10-31 23:00:00.000000', 'zone', 'scene', 'zone', 'scene')] (Background on this error at: http://sqlalche.me/e/4xp6)
2018-11-01 22:37:30 ERROR (SyncWorker_7) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated, states.created AS states_created, states.context_id AS states_context_id, states.context_user_id AS states_context_user_id \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated \nFROM states \nWHERE states.last_updated >= ? AND states.last_updated < ? GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id \nWHERE states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?)'] [parameters: ('2018-10-29 20:44:05.094442', '2018-10-31 23:00:00.000000', 'zone', 'scene', 'zone', 'scene')] (Background on this error at: http://sqlalche.me/e/4xp6)
2018-11-01 22:37:30 ERROR (MainThread) [aiohttp.server] Error handling request
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, 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.6/site-packages/aiohttp/web_protocol.py", line 390, in start
    resp = await self._request_handler(request)
  File "/usr/local/lib/python3.6/site-packages/aiohttp/web_app.py", line 366, in _handle
    resp = await handler(request)
  File "/usr/local/lib/python3.6/site-packages/aiohttp/web_middlewares.py", line 106, in impl
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/static.py", line 66, in staticresource_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/real_ip.py", line 34, in real_ip_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/ban.py", line 66, in ban_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/auth.py", line 68, in auth_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/real_ip.py", line 34, in real_ip_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/ban.py", line 66, in ban_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/auth.py", line 68, in auth_middleware
    return await handler(request)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/http/view.py", line 113, in handle
    result = await result
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 332, in get
    entity_ids, self.filters, include_start_time_state)
  File "/usr/local/lib/python3.6/concurrent/futures/thread.py", line 56, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 93, in get_significant_states
    include_start_time_state)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 239, in states_to_json
    for state in get_states(hass, start_time, entity_ids, filters=filters):
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/history.py", line 214, in get_states
    return [state for state in execute(query)
  File "/usr/local/lib/python3.6/site-packages/homeassistant/components/recorder/util.py", line 64, in execute
    (row.to_native() for row in qry)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2988, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3011, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated, states.created AS states_created, states.context_id AS states_context_id, states.context_user_id AS states_context_user_id \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated \nFROM states \nWHERE states.last_updated >= ? AND states.last_updated < ? GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id \nWHERE states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?)'] [parameters: ('2018-10-29 20:44:05.094442', '2018-10-31 23:00:00.000000', 'zone', 'scene', 'zone', 'scene')] (Background on this error at: http://sqlalche.me/e/4xp6)
Connected

I already tried to delete log file from /homeassistant/config/home-assistant.log, but this error still occures everytime I am trying to access History tab.

Any ideas please?

vloris · November 6, 2018, 1:20pm

This has nothing to do with home-assistant.log. The file you want to delete is home-assistant_v2.db. That is the one containing the history database. If you delete it, and restart home-assistant it will recreate an empty database.

Mike_Boiko · April 23, 2019, 5:18pm

thanks, that did the trick!

rudgr · June 14, 2019, 8:04am

Experiencing the same issue after SD card snapshot restore:

Error executing query: (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": "sensor.power_consumption_phase_l2", "old_state": {"entity_id": "sensor.power_consumption_phase_l2", "state": "1.949", "attributes": {" ... (534 characters truncated) ... :00", "last_updated": "2019-06-14T07:47:40.495309+00:00", "context": {"id": "0d0214ec3fdb4f1db157e5f81379d396", "parent_id": null, "user_id": null}}}', 'LOCAL', '2019-06-14 07:47:40.495385', '2019-06-14 07:47:40.587635', '0d0214ec3fdb4f1db157e5f81379d396', None)]
(Background on this error at: http://sqlalche.me/e/4xp6)

As I understand correctly, deleting home-assistant_v2.db will lose my history?

Is there no way to repair the home-assistant_v2.db file and preserve my history data?

tx a lot

eddriesen · June 26, 2019, 8:10pm

For all ya googlers (or ducckduckgoers). I fixed mine!

shut down HA
via shell cd into config folder and execute:

sqlite3 ./home-assistant_v2.db “.dump” | sqlite3 ./home-assistant_v2.db.fix
mv ./home-assistant_v2.db ./home-assistant_v2.db.broken
mv ./home-assistant_v2.db.fix ./home-assistant_v2.db

Start ha and check logs and history via ui if fixed
Delete home-assistant_v2.db.broken

Plevuus · June 30, 2019, 5:27pm

Thanks @eddriesen for posting this!

However, the command sqlite3 ./home-assistant_v2.db “.dump” | sqlite3 ./home-assistant_v2.db.fix generates an error in my case: -bash: sqlite3: command not found

Do you have any clue how to solve this?

eddriesen · June 30, 2019, 7:29pm

Might have an older sqlite? Type sqli and hit tab a few times. Might of be that your platform had another version installed.

It did it outside of the container Shell, via host os Shell in /var/lib/docker/volumes/havolumename/_data/ because my host os had sqlite 3 installed.

If not, install it via your guest/host os. Example “apt install sqlite3”

Plevuus · July 1, 2019, 8:28pm

Thanks again @eddriesen. I’m using Hass.io on a raspberry pi. I couldn’t figure out how to install sqlite3 in that ‘OS’. Seems that this is really beyond my current knowledge level. For now I think I just leave it. I made a backup of the database, deleted it and let Hass.io create a new database. It’s a pity I cannot see my old data anymore in Home Assistant, but it’s not critical.

dzungpv · July 20, 2019, 3:52pm

Thank you, it save my logs

Robert_Mulders · July 22, 2019, 11:22am

Same issue here after I needed to restore from a snapshot image so seems to be a common thing. Deleting home-assistant_v2.db did the trick.

sgrayban · August 18, 2019, 2:38pm

Not working for me… really pisses me off

antoweb76 · November 25, 2019, 11:24am

In hassio i cant install sqlite3. For this reason i have used windows:

First stop hassio than:

Download sqlite3 browser from https://sqlitebrowser.org/dl/
Apening home-assistant_v2.db with thsi program
On the menù export create a sql file
Close and reopen sqlitebrowser
On the menu import select the sql created before
Create a new db file
Copy the new db in /config of hassio
Rename broken db file in hassio
rename new db file to home-assistant_v2.db
start hassio

antoweb76 · November 25, 2019, 11:25am

Yes but so you lost the history

upwind_anderl · December 1, 2019, 8:30pm

is there no chance to install sqlite on the hass.io raspberry image?!

i’m also stucked in this

DenisO · December 15, 2019, 10:05pm

Faced same issue
But after deleting home-assistant_v2.db (renamed) Im unable to connect to my HA over SSH and HTTP, even its pingable
Any suggestions?

UPD: First issue on connected monitor - “Failed to mount HassOS overlay partition”

UPD2: Fixed with checking filesystem tool (ExtFS Paragon for MacOS)

redlegoman · December 30, 2019, 10:26am

If you are using hassio then sqlite3 can be installed from the CLI of the docker instance itself (install the ssh add on from the hass web interface) using

apk add sqlite

then you can continue and follow the instructions given by @eddriesen

eddriesen · December 30, 2019, 4:27pm

Little update;
After another corrupt db and bad overall performance (i have a LOT of sensors) i just went with mysql which was allready installed and used on the host system. Much faster at rendering history now!

Plevuus · January 3, 2020, 8:10pm

Thanks @redlegoman!
meanwhile I figured out annother way (inspired by the posts of @eddriesen and @antoweb76:

Stop HA via the SSH CLI using hassio ha stop
Move the database-file to my windows computer using samba
download sqlite CLI for windows from https://www.sqlite.org/download.html
perform sqlite3 ./home-assistant_v2.db_old ".dump" | sqlite3 ./home-assistant_v2.db_fix
move the new db-file back using samba
rename the db-file (remove the _fix part)
re-start HA via the SSH CLI using hassio ha start

Your solution seems even more simple though and I managed to install sqlite the way you mentioned. I’ll try it next time

baz123 · February 8, 2020, 3:03pm

@eddriesen - could you expand please? It looks to me like the engine is installed within the docker as I had to install it to fix the database.

baz123 · February 8, 2020, 3:16pm

I ended up with a zero size ‘fix’ file so I’m guessing the whole file is goosed!