core: Unable to update from 2021.1.5 due to schema migration (MSSQL)

The problem

When trying to update from docker image homeassistant/home-assistant:2021.1.5 to homeassistant/home-assistant:2021.5.5, home assistant fails hard.

I tried to follow the recommend way is mentioned here: https://www.home-assistant.io/integrations/recorder/#ms-sql-server, making my own Docker image: gimlichael/home-assistant-mssql:2021.5.5

My setup consist of a Microsoft SQL Server Developer (64-bit) 15.0.4123.1 running in Docker - and the problem has been consistent at least from version 2021.2.3.

Please let me know if I can help you guys to iron out this annoying wrinkle hitting some of us running MSSQL, so I may again enjoy the continuous improvements of HA.

What is version of Home Assistant Core has the issue?

2021.5.5

What was the last working version of Home Assistant Core?

2021.1.5

What type of installation are you running?

Home Assistant Container

Integration causing the issue

Recorder

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder/#ms-sql-server

Example YAML snippet

No response

Anything in the logs that might be useful for us?

HA image:

2021-05-22 13:35:32 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 10
2021-05-22 13:35:32 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_states_old_state_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
2021-05-22 13:35:32 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_states_old_state_id already exists on states, continuing
2021-05-22 13:35:32 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: 'options'
2021-05-22 13:35:32 ERROR (Recorder) [homeassistant.components.recorder] Database error during schema migration
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 51, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 64, in migrate_schema
_apply_update(instance.engine, session, new_version, current_version)
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 393, in _apply_update
_update_states_table_with_foreign_key_options(connection, engine)
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 283, in _update_states_table_with_foreign_key_options
not foreign_key["options"]
KeyError: 'options'
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 954, in _rollback_impl
self.engine.dialect.do_rollback(self.connection)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 667, 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 587, in _migrate_schema_and_setup_run
migration.migrate_schema(self, current_version)
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 67, in migrate_schema
_LOGGER.info("Upgrade to version %s done", new_version)
File "/usr/local/lib/python3.8/contextlib.py", line 131, in __exit__
self.gen.throw(type, value, traceback)
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 61, in session_scope
session.close()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1728, in close
self._close_impl(invalidate=False)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1770, in _close_impl
transaction.close(invalidate)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 920, in close
transaction.close()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2279, in close
self._do_close()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2497, in _do_close
self._close_impl()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2483, in _close_impl
self._connection_rollback_impl()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2475, in _connection_rollback_impl
self.connection._rollback_impl()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 956, 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 1987, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 954, in _rollback_impl
self.engine.dialect.do_rollback(self.connection)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 667, 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/14/f405)
2021-05-22 13:35:32 WARNING (MainThread) [homeassistant.helpers.template] Template variable warning: 'dict object' has no attribute 'led_indication' when rendering '{{ value_json.led_indication }}'
2021-05-22 13:35:33 ERROR (Dummy-11) [sqlalchemy.pool.impl.QueuePool] Exception during reset or similar
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 676, in _finalize_fairy
fairy._reset(pool)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 881, in _reset
pool._dialect.do_rollback(self)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 667, in do_rollback
dbapi_connection.rollback()
pyodbc.ProgrammingError: ('25000', '[25000] [FreeTDS][SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (3903) (SQLEndTran)')

Custom image:

2021-05-22 19:42:12 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 10
2021-05-22 19:42:12 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index `ix_states_old_state_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
2021-05-22 19:42:12 WARNING (Recorder) [homeassistant.components.recorder.migration] Index ix_states_old_state_id already exists on states, continuing
2021-05-22 19:42:12 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: 'options'
2021-05-22 19:42:12 ERROR (Recorder) [homeassistant.components.recorder] Error during schema migration
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 587, in _migrate_schema_and_setup_run
    migration.migrate_schema(self, current_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 64, in migrate_schema
    _apply_update(instance.engine, session, new_version, current_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 393, in _apply_update
    _update_states_table_with_foreign_key_options(connection, engine)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 283, in _update_states_table_with_foreign_key_options
    not foreign_key["options"]
KeyError: 'options'

Additional information

Might be related to these:

https://github.com/home-assistant/core/pull/46678 https://github.com/home-assistant/core/issues/49492 https://github.com/home-assistant/core/pull/49999