I have run across a very weird issue, happened to me 3 times or so.
I am using the google drive backup addon which takes a backup daily and uploads to google drive at 8pm.
MariaDB is my database.
Around the same time all the sensors stop logging the history, their values get updated correctly but no history.
Last night:
Things that I noticed:
- The backup takes about 40mins which is a bit excessive
- My full backups compressed were around 500-600mb, now they are just over 1gb
- The event table is massive now and I have no idea what is causing it
- HA restart needed to fix the history
- Seeing this error in the logs:
2023-09-14 08:04:01.847 ERROR (MainThread) [homeassistant.components.websocket_api.http.connection] [545727831744] Error handling message: Unknown error (unknown_error) from 192.168.0.237 (Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.0.0 Safari/537.36)
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/websocket_api/decorators.py", line 26, in _handle_async_response
await func(hass, connection, msg)
File "/usr/src/homeassistant/homeassistant/components/history/websocket_api.py", line 524, in ws_stream
last_event_time = await _async_send_historical_states(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/history/websocket_api.py", line 281, in _async_send_historical_states
last_time_ts, last_time_dt, payload = await instance.async_add_executor_job(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/concurrent/futures/thread.py", line 58, in run
result = self.fn(*self.args, **self.kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/history/websocket_api.py", line 227, in _generate_historical_response
history.get_significant_states(
File "/usr/src/homeassistant/homeassistant/components/recorder/history/__init__.py", line 104, in get_significant_states
return _target(
^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 108, in get_significant_states
return get_significant_states_with_session(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 261, in get_significant_states_with_session
execute_stmt_lambda_element(session, stmt, None, end_time, orm_rows=False),
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 221, in execute_stmt_lambda_element
executed = session.connection().execute(stmt)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1413, in execute
return meth(
^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/lambdas.py", line 605, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1810, in _execute_context
conn = self._revalidate_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 665, in _revalidate_connection
self._invalid_transaction()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 655, in _invalid_transaction
raise exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b)
Any idea what is causing this and how to fix it?
Also how can I put an automation in place that alerts me when this happens again since the values do get updated still?