MS SQL Database recorder errors continue

My recorder errors are getting worse, it no longer starts the recorder, so no history is available.Since I’d been having these errors for a while, I decided to delete my HomeAssistant database and start over.That didn’t help, same type of errors. Below is the first error that happened.

2021-02-05 17:41:07 ERROR (Recorder) [sqlalchemy.pool.impl.QueuePool] Exception during reset or similar

Traceback (most recent call last):

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context

self.dialect.do_execute(

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 609, in do_execute

cursor.execute(statement, parameters)

pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Introducing FOREIGN KEY constraint 'FK__states__old_stat__6477ECF3' on table 'states' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. (1785) (SQLExecDirectW)")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 749, in _rollback_impl

self.engine.dialect.do_rollback(self.connection)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 559, in do_rollback

dbapi_connection.rollback()

pyodbc.ProgrammingError: ('25000', '[25000] [FreeTDS][SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (3903) (SQLEndTran)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 273, in run

self._setup_connection()

File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 615, in _setup_connection

Base.metadata.create_all(self.engine)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4553, in create_all

bind._run_visitor(

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2095, in _run_visitor

conn._run_visitor(visitorcallable, element, **kwargs)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1656, in _run_visitor

visitorcallable(self.dialect, self, **kwargs).traverse_single(element)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single

return meth(obj, **kw)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 783, in visit_metadata

self.traverse_single(

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single

return meth(obj, **kw)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 827, in visit_table

self.connection.execute(

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute

return meth(self, multiparams, params)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection

return connection._execute_ddl(self, multiparams, params)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1068, in _execute_ddl

ret = self._execute_context(

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context

self._handle_dbapi_exception(

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1505, in _handle_dbapi_exception

self._autorollback()

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 82, in __exit__

compat.raise_(value, with_traceback=traceback)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_

raise exception

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1505, in _handle_dbapi_exception

self._autorollback()

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 888, in _autorollback

self._root._rollback_impl()

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 751, in _rollback_impl

self._handle_dbapi_exception(e, None, None, None, None)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1405, in _handle_dbapi_exception

util.raise_(

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_

raise exception

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 749, in _rollback_impl

self.engine.dialect.do_rollback(self.connection)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 559, in do_rollback

dbapi_connection.rollback()

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('25000', '[25000] [FreeTDS][SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (3903) (SQLEndTran)')

(Background on this error at: http://sqlalche.me/e/13/f405)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 697, in _finalize_fairy

fairy._reset(pool)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 893, in _reset

pool._dialect.do_rollback(self)

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 559, in do_rollback

dbapi_connection.rollback()

pyodbc.ProgrammingError: ('25000', '[25000] [FreeTDS][SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (3903) (SQLEndTran)')

2021-02-05 17:41:07 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (pyodbc.ProgrammingError) ('25000', '[25000] [FreeTDS][SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (3903) (SQLEndTran)')

(Background on this error at: http://sqlalche.me/e/13/f405) (retrying in 3 seconds)

2021-02-05 17:41:10 ERROR (Recorder) [sqlalchemy.pool.impl.QueuePool] Exception during reset or similar

This is with SQL Server 2019 on Windows 10.

System Health

version core-2021.2.1
installation_type Home Assistant OS
dev false
hassio true
docker true
virtualenv false
python_version 3.8.7
os_name Linux
os_version 5.4.86
arch x86_64
timezone America/Los_Angeles
Home Assistant Community Store
GitHub API ok
Github API Calls Remaining 4763
Installed Version 1.10.1
Stage running
Available Repositories 709
Installed Repositories 7
Home Assistant Cloud
logged_in false
can_reach_cert_server ok
can_reach_cloud_auth ok
can_reach_cloud ok
Hass.io
host_os Home Assistant OS 5.10
update_channel stable
supervisor_version supervisor-2021.01.7
docker_version 19.03.13
disk_total 52.2 GB
disk_used 10.9 GB
healthy true
supported true
board ova
supervisor_api ok
version_api ok
installed_addons Samba share (9.3.0), Mosquitto broker (5.1), Duck DNS (1.12.4), Terminal & SSH (9.0.0), File editor (5.2.0), Visual Studio Code (3.0.1), Grafana (6.0.0), Node-RED (8.0.1), FTP (3.5.0), roombapw Docker Image (1.0.0), rest980 Docker Image (20200205), php-nginx Docker Image (latest)
Lovelace
dashboards 1
resources 6
views 6
mode storage

Any ideas would be appreciated. Thanks.

I am seeing these errors too, just started after doing the upgrade to 2021.2.1

I was about to rebuild my recorder DB too, but it sounds like it wont help.

I actually downgraded for a min and saw recorder work again - all my history was there.

But unfortunately the downgrade broke some of my integrations - so I just reverted back to a snapshot I took before (in Hyper-V). But this seems to be related to the 2020.2 release.

Is anyone else seeing this or has anyone figure out what the problem is?

I think related.

1 Like