I had few times issues with the default SQLite database already (meaning it was automatically marked as corrupted and HA created a new empty one - which of course is VERY bad when it comes to long-term statistics, energy dashboard etc.).
Every time either immediately or after a bit of investigation I knew, what was the cause for this.
Today (2nd time this year) a database corruption happened again, but this time I have no idea what caused it (like this colleague did: OperationalError in sqlite db) as it came out of nothing and the log entries reference a very generic DB error message.
Every check performed on the “corrupted” DB file like
PRAGMA quick_check;PRAGMA integrity_check;-
PRAGMA foreign_key_check;
passed perfectly (OK, no issues). So before restoring last night’s backup I decided to give it a shot and restarted HA just with that “corrupted” database file. And it worked/works. Everything there, all states, energy dashboard, just fine. Only lost about 45 minutes.
HA log tells me:
click to enlarge logfile content
2022-07-19 17:15:06 ERROR (Recorder) [homeassistant.components.recorder.core] Unrecoverable sqlite3 database corruption detected: (sqlite3.OperationalError) locking protocol
[SQL: SELECT state_attributes.attributes_id
FROM state_attributes
WHERE state_attributes.hash = ? AND state_attributes.shared_attrs = ?]
[parameters: (4287559711, '{"source_type":"router","ip":"xxx.xxx.xxx.xxx","mac":"XX:XX:XX:XX:XX:XX","host_name":"printer","last_time_reachable":"2022-07-19T15:14:51+00:00","icon":"mdi:printer","friendly_name":"Printer (WiFi)"}')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: locking protocol
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 607, in _process_one_task_or_recover
return task.run(self)
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 211, in run
instance._process_one_event(self.event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 703, in _process_one_event
self._process_state_changed_event_into_session(event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 807, in _process_state_changed_event_into_session
if attributes_id := self._find_shared_attr_in_db(attr_hash, shared_attrs):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 722, in _find_shared_attr_in_db
if attributes_id := self.event_session.execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/lambdas.py", line 509, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) locking protocol
[SQL: SELECT state_attributes.attributes_id
FROM state_attributes
WHERE state_attributes.hash = ? AND state_attributes.shared_attrs = ?]
[parameters: (4287559711, '{"source_type":"router","ip":"xxx.xxx.xxx.xxx","mac":"XX:XX:XX:XX:XX:XX","host_name":"printer","last_time_reachable":"2022-07-19T15:14:51+00:00","icon":"mdi:printer","friendly_name":"Printer (WiFi)"}')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
2022-07-19 17:15:07 ERROR (Recorder) [homeassistant.components.recorder.pool.RecorderPool] Exception closing connection <sqlite3.Connection object at 0x7f996d7a80>
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: locking protocol
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 607, in _process_one_task_or_recover
return task.run(self)
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 211, in run
instance._process_one_event(self.event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 703, in _process_one_event
self._process_state_changed_event_into_session(event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 807, in _process_state_changed_event_into_session
if attributes_id := self._find_shared_attr_in_db(attr_hash, shared_attrs):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 722, in _find_shared_attr_in_db
if attributes_id := self.event_session.execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/lambdas.py", line 509, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) locking protocol
[SQL: SELECT state_attributes.attributes_id
FROM state_attributes
WHERE state_attributes.hash = ? AND state_attributes.shared_attrs = ?]
[parameters: (4287559711, '{"source_type":"router","ip":"xxx.xxx.xxx.xxx","mac":"XX:XX:XX:XX:XX:XX","host_name":"printer","last_time_reachable":"2022-07-19T15:14:51+00:00","icon":"mdi:printer","friendly_name":"Printer (WiFi)"}')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 247, in _close_connection
self._dialect.do_close(connection)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 688, in do_close
dbapi_connection.close()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 548016909112 and this is thread id 548239535640.
2022-07-19 17:15:07 ERROR (Recorder) [homeassistant.components.recorder.pool.RecorderPool] Exception closing connection <sqlite3.Connection object at 0x7f9b226a80>
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: locking protocol
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 607, in _process_one_task_or_recover
return task.run(self)
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 211, in run
instance._process_one_event(self.event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 703, in _process_one_event
self._process_state_changed_event_into_session(event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 807, in _process_state_changed_event_into_session
if attributes_id := self._find_shared_attr_in_db(attr_hash, shared_attrs):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 722, in _find_shared_attr_in_db
if attributes_id := self.event_session.execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/lambdas.py", line 509, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) locking protocol
[SQL: SELECT state_attributes.attributes_id
FROM state_attributes
WHERE state_attributes.hash = ? AND state_attributes.shared_attrs = ?]
[parameters: (4287559711, '{"source_type":"router","ip":"xxx.xxx.xxx.xxx","mac":"XX:XX:XX:XX:XX:XX","host_name":"printer","last_time_reachable":"2022-07-19T15:14:51+00:00","icon":"mdi:printer","friendly_name":"Printer (WiFi)"}')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 247, in _close_connection
self._dialect.do_close(connection)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 688, in do_close
dbapi_connection.close()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 548048726840 and this is thread id 548239535640.
2022-07-19 17:15:07 ERROR (Recorder) [homeassistant.components.recorder.pool.RecorderPool] Exception closing connection <sqlite3.Connection object at 0x7f9b2267b0>
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: locking protocol
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 607, in _process_one_task_or_recover
return task.run(self)
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 211, in run
instance._process_one_event(self.event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 703, in _process_one_event
self._process_state_changed_event_into_session(event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 807, in _process_state_changed_event_into_session
if attributes_id := self._find_shared_attr_in_db(attr_hash, shared_attrs):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 722, in _find_shared_attr_in_db
if attributes_id := self.event_session.execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/lambdas.py", line 509, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) locking protocol
[SQL: SELECT state_attributes.attributes_id
FROM state_attributes
WHERE state_attributes.hash = ? AND state_attributes.shared_attrs = ?]
[parameters: (4287559711, '{"source_type":"router","ip":"xxx.xxx.xxx.xxx","mac":"XX:XX:XX:XX:XX:XX","host_name":"printer","last_time_reachable":"2022-07-19T15:14:51+00:00","icon":"mdi:printer","friendly_name":"Printer (WiFi)"}')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 247, in _close_connection
self._dialect.do_close(connection)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 688, in do_close
dbapi_connection.close()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 548060568376 and this is thread id 548239535640.
2022-07-19 17:15:07 ERROR (Recorder) [homeassistant.components.recorder.pool.RecorderPool] Exception closing connection <sqlite3.Connection object at 0x7f9b226990>
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: locking protocol
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 607, in _process_one_task_or_recover
return task.run(self)
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 211, in run
instance._process_one_event(self.event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 703, in _process_one_event
self._process_state_changed_event_into_session(event)
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 807, in _process_state_changed_event_into_session
if attributes_id := self._find_shared_attr_in_db(attr_hash, shared_attrs):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 722, in _find_shared_attr_in_db
if attributes_id := self.event_session.execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/lambdas.py", line 509, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) locking protocol
[SQL: SELECT state_attributes.attributes_id
FROM state_attributes
WHERE state_attributes.hash = ? AND state_attributes.shared_attrs = ?]
[parameters: (4287559711, '{"source_type":"router","ip":"xxx.xxx.xxx.xxx","mac":"XX:XX:XX:XX:XX:XX","host_name":"printer","last_time_reachable":"2022-07-19T15:14:51+00:00","icon":"mdi:printer","friendly_name":"Printer (WiFi)"}')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 247, in _close_connection
self._dialect.do_close(connection)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 688, in do_close
dbapi_connection.close()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 548055018296 and this is thread id 548239535640.
2022-07-19 17:15:07 ERROR (Recorder) [homeassistant.components.recorder.util] The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2022-07-19T15:15:07.859359+00:00 in order to allow startup to proceed
2022-07-19 17:20:39 WARNING (MainThread) [homeassistant.helpers.frame] Detected integration that uses deprecated `async_get_registry` to access entity registry, use async_get instead. Please report issue to the custom component author for shelly using this method at custom_components/shelly/__init__.py, line 423: await self.hass.helpers.entity_registry.async_get_registry()
2022-07-19 17:20:41 ERROR (MainThread) [aiohttp.server] Error handling request
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/aiohttp/web_protocol.py", line 435, in _handle_request
resp = await request_handler(request)
File "/usr/local/lib/python3.9/site-packages/aiohttp/web_app.py", line 504, in _handle
resp = await handler(request)
File "/usr/local/lib/python3.9/site-packages/aiohttp/web_middlewares.py", line 117, in impl
return await handler(request)
File "/usr/src/homeassistant/homeassistant/components/http/security_filter.py", line 60, in security_filter_middleware
return await handler(request)
File "/usr/src/homeassistant/homeassistant/components/http/forwarded.py", line 100, in forwarded_middleware
return await handler(request)
File "/usr/src/homeassistant/homeassistant/components/http/request_context.py", line 28, in request_context_middleware
return await handler(request)
File "/usr/src/homeassistant/homeassistant/components/http/ban.py", line 79, in ban_middleware
return await handler(request)
File "/usr/src/homeassistant/homeassistant/components/http/auth.py", line 220, in auth_middleware
return await handler(request)
File "/usr/src/homeassistant/homeassistant/components/http/view.py", line 137, in handle
result = await result
File "/usr/src/homeassistant/homeassistant/components/media_player/__init__.py", line 1123, in get
data, content_type = await player.async_get_media_image()
File "/usr/src/homeassistant/homeassistant/components/androidtv/media_player.py", line 329, in async_get_media_image
media_data = await self._adb_screencap()
File "/usr/src/homeassistant/homeassistant/components/androidtv/media_player.py", line 181, in _adb_exception_catcher
return await func(self, *args, **kwargs)
File "/usr/src/homeassistant/homeassistant/components/androidtv/media_player.py", line 322, in _adb_screencap
return await self.aftv.adb_screencap()
File "/usr/local/lib/python3.9/site-packages/androidtv/basetv/basetv_async.py", line 158, in adb_screencap
return await self._adb.screencap()
File "/usr/local/lib/python3.9/site-packages/androidtv/adb_manager/adb_manager_async.py", line 409, in screencap
result = await self._adb.shell("screencap -p", decode=False)
File "/usr/local/lib/python3.9/site-packages/adb_shell/adb_device_async.py", line 809, in shell
return await self._service(b'shell', command.encode('utf8'), transport_timeout_s, read_timeout_s, timeout_s, decode)
File "/usr/local/lib/python3.9/site-packages/adb_shell/adb_device_async.py", line 678, in _service
return b''.join([x async for x in self._streaming_command(service, command, transport_timeout_s, read_timeout_s, timeout_s)])
File "/usr/local/lib/python3.9/site-packages/adb_shell/adb_device_async.py", line 678, in <listcomp>
return b''.join([x async for x in self._streaming_command(service, command, transport_timeout_s, read_timeout_s, timeout_s)])
File "/usr/local/lib/python3.9/site-packages/adb_shell/adb_device_async.py", line 1255, in _streaming_command
async for data in self._read_until_close(adb_info):
File "/usr/local/lib/python3.9/site-packages/adb_shell/adb_device_async.py", line 1208, in _read_until_close
cmd, data = await self._read_until([constants.CLSE, constants.WRTE], adb_info)
File "/usr/local/lib/python3.9/site-packages/adb_shell/adb_device_async.py", line 1182, in _read_until
await self._okay(adb_info)
File "/usr/local/lib/python3.9/site-packages/adb_shell/adb_device_async.py", line 1110, in _okay
await self._io_manager.send(msg, adb_info)
File "/usr/local/lib/python3.9/site-packages/adb_shell/adb_device_async.py", line 341, in send
await self._send(msg, adb_info)
File "/usr/local/lib/python3.9/site-packages/adb_shell/adb_device_async.py", line 485, in _send
await self._transport.bulk_write(packed, adb_info.transport_timeout_s)
File "/usr/local/lib/python3.9/site-packages/adb_shell/transport/tcp_transport_async.py", line 130, in bulk_write
self._writer.write(data)
AttributeError: 'NoneType' object has no attribute 'write'
2022-07-19 17:22:19 WARNING (MainThread) [homeassistant.core] Timed out waiting for shutdown stage 1 to complete, the shutdown will continue
2022-07-19 17:23:19 WARNING (MainThread) [homeassistant.core] Timed out waiting for shutdown stage 2 to complete, the shutdown will continue
2022-07-19 17:23:49 WARNING (MainThread) [homeassistant.core] Timed out waiting for shutdown stage 3 to complete, the shutdown will continue
(note that few contents have been blackened)
- So that basically was a READ attempt (simple SELECT statement) which failed once and five more times.
Didn’t know that this could “kill” a database or motivate HA to recreate it. - The referenced Error Messages — SQLAlchemy 1.4 Documentation talks about those OperationalErrors
Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc.
This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.
The OperationalError is the most common (but not the only) error class used by drivers in the context of the database connection being dropped, or not being able to connect to the database. For tips on how to deal with this, see the section Dealing with Disconnects.
So for the peace of my mind I got these primary questions:
- Do I miss something from the logs?
- What can I make out of that?
- Maybe was the answer from the read attempt (SELECT) too slow?
- Maybe due to a dying SD card?
- How can I “provocate” such errors to stress-test the database / SD card (running HA OS)?
-
In case my SQLite database would already been migrated to a MySQL/MariaDB: would this guarantee to not run into the same
sqlite3.OperationalErrorerror?
(we’re still looking for a safe and reliable way to migrated, for few very promising attempts see Migrating home assistant database from sqlite to mariadb) - Any other ideas / tips / lecture to recommend?
And yes, just to rule out the SD card thing I am in the process of thinking “lets outsource the data partition from the SD card to an external SSD”. Unfortunately that
- is a one-way action
- usually introduces new issues e. g. on HA OS updates (USB SSDs on Raspberry Pis are well-known for issues).