Recorder-DB (SQLite): Unrecoverable sqlite3 database corruption detected: (sqlite3.OperationalError) locking protocol

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:

  1. Do I miss something from the logs?
  2. 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?
  1. How can I “provocate” such errors to stress-test the database / SD card (running HA OS)?
  2. In case my SQLite database would already been migrated to a MySQL/MariaDB: would this guarantee to not run into the same sqlite3.OperationalError error?
    (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)
  3. 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

  1. is a one-way action
  2. usually introduces new issues e. g. on HA OS updates (USB SSDs on Raspberry Pis are well-known for issues).
2 Likes

So no one else ever ran into this? What unbelievable luck I have.