Home assistant History Error (database)

swiftvic · March 22, 2017, 12:48pm

The last couple of days I saw a bunch of errors appear in the error log. I am not sure what is causing it as I have recently added trackers and zoning. I commented those out of my configuration log but I still see these errors. Now the “history” part of HASS is not working and I believe it’s due to these errors. Can anyone help?

Running on a RPi3 using HASS 0.40

17-03-22 12:44:08 ERROR (Thread-10) [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 \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states \nWHERE states.created >= ? AND states.created < ? AND states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?) GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id'] [parameters: ('2017-03-22 01:54:26.100203', '2017-03-22 04:00:00.000000', 'zone', 'scene', 'zone', 'scene')]
17-03-22 12:44:08 ERROR (Thread-10) [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 \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states \nWHERE states.created >= ? AND states.created < ? AND states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?) GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id'] [parameters: ('2017-03-22 01:54:26.100203', '2017-03-22 04:00:00.000000', 'zone', 'scene', 'zone', 'scene')]
17-03-22 12:44:08 ERROR (Thread-10) [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 \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states \nWHERE states.created >= ? AND states.created < ? AND states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?) GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id'] [parameters: ('2017-03-22 01:54:26.100203', '2017-03-22 04:00:00.000000', 'zone', 'scene', 'zone', 'scene')]
17-03-22 12:44:08 ERROR (Thread-10) [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 \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states \nWHERE states.created >= ? AND states.created < ? AND states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?) GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id'] [parameters: ('2017-03-22 01:54:26.100203', '2017-03-22 04:00:00.000000', 'zone', 'scene', 'zone', 'scene')]
17-03-22 12:44:08 ERROR (MainThread) [aiohttp.server] Error handling request
Traceback (most recent call last):
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/default.py", line 470, 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 "/home/homeassistant/.homeassistant/deps/aiohttp/web_server.py", line 62, in handle_request
    resp = yield from self._handler(request)
  File "/home/homeassistant/.homeassistant/deps/aiohttp/web.py", line 270, in _handle
    resp = yield from handler(request)
  File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/http/ban.py", line 58, in ban_middleware_handler
    return (yield from handler(request))
  File "/usr/lib/python3.4/asyncio/coroutines.py", line 143, in coro
    res = yield from res
  File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/http/__init__.py", line 417, in handle
    result = yield from result
  File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/history.py", line 238, in get
    end_time, entity_id, self.filters)
  File "/usr/lib/python3.4/asyncio/futures.py", line 388, in __iter__
    yield self  # This tells Task to wait for completion.
  File "/usr/lib/python3.4/asyncio/tasks.py", line 286, in _wakeup
    value = future.result()
  File "/usr/lib/python3.4/asyncio/futures.py", line 277, in result
    raise self._exception
  File "/usr/lib/python3.4/concurrent/futures/thread.py", line 54, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/history.py", line 82, in get_significant_states
    return states_to_json(hass, states, start_time, entity_id, filters)
  File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/history.py", line 158, in states_to_json
    for state in get_states(hass, start_time, entity_ids, filters=filters):
  File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/history.py", line 139, in get_states
    return [state for state in execute(query)
  File "/srv/homeassistant/lib/python3.4/site-packages/homeassistant/components/recorder/util.py", line 63, in execute
    (row.to_native() for row in qry)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/orm/query.py", line 2790, in __iter__
    return self._execute_and_instances(context)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/orm/query.py", line 2813, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/default.py", line 470, 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 \nFROM states JOIN (SELECT max(states.state_id) AS max_state_id \nFROM states \nWHERE states.created >= ? AND states.created < ? AND states.domain NOT IN (?, ?) AND states.domain NOT IN (?, ?) GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id'] [parameters: ('2017-03-22 01:54:26.100203', '2017-03-22 04:00:00.000000', 'zone', 'scene', 'zone', 'scene')]

swiftvic · March 22, 2017, 2:42pm

Looks like the database got corrupted based on the following:

sqlite3.DatabaseError: database disk image is malformed

I solved the issue by deleting the database file in home assistant. I stopped the home assistant service, deleted the home-assistant_v2.db file and rebooted the service. It recreated a new one and now the errors for this disappeared.

Please close thread. This information might be useful for someone else who encounters similar issues.

efurban · March 9, 2018, 9:31pm

I am having exactly the same error on my hassio. but how to delete such file in hassio? any advise would be appreicated. Thanks.

efurban · March 9, 2018, 9:35pm

Found it: /config/home-assistant_v2.db

ArisSaraiva · May 20, 2018, 1:48pm

hello did you find out how to do it in hass.io? I´m afraid of broking everything

swiftvic · May 20, 2018, 5:16pm

Hope this solved it? I think efurban already posted it.

ArisSaraiva · May 20, 2018, 5:37pm

Should I just delete and restart?

swiftvic · May 20, 2018, 6:14pm

I would back up the file first or rename it should do it. Yes then restart home assistant service: sudo systemctl restart [email protected]

The home-assistant_v2.db file will recreate itself every time it boots if that file is missing. All you loose is history data of what turned on/off. To me it wasn’t vital data.

ArisSaraiva · May 20, 2018, 6:26pm

it worked thanks a lot!!!

swiftvic · May 20, 2018, 6:42pm

Glad it worked

Bakko65 · May 23, 2018, 8:40pm

Thank You! Now my Blinds (Covers) working again

swiftvic · May 24, 2018, 3:58am

Very happy for you. Pay it forward. I’ve had so many people help me on this forum. Good to give back

efurban · June 30, 2018, 3:52am

yes that’s it. Very simple

jean675 · April 21, 2019, 3:26pm

Same error, I deleted the database file and reboot, but it does nothing.
Now I still have the error and no database file.

gpbenton · April 23, 2019, 5:40am

If HA didn’t create a new .db file when it restarted then it is either configured to use some other location or you have a different problem.

jean675 · April 23, 2019, 12:09pm

I ended up by re-installing HA …

Lifelogger · April 28, 2019, 11:30am

Thanks for leaving this topic open. This worked just fine for me and saved me a lot of trouble.

eddriesen · June 26, 2019, 8:11pm

Crossposting a fix;

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

Pachangadad · November 7, 2019, 3:06pm

I encountered an error using the sqlite3 command, I had to remove the quotes around the .dump to run successfully.
I was able to recover though.
Thanks

pOison · November 18, 2019, 9:22pm

In your method did not work. But restored using SQLiteExpertProfessional.