Core-2021.5.0 broken MSSQL connection

JanneSa · May 12, 2021, 4:59am

Hi
The documentation on the Recorder has been updated.

Working connection string. It is formatted just like an URL. (no semicolons or curly brackets)

mssql+pyodbc://username:password@SERVER_IP:1433/DB_NAME?charset=utf8mb4&driver=FreeTDS

I was forced to recreate database due to date conversion as mentioned above.

KiLLeRRat · May 13, 2021, 12:05am

I tried this but it didn’t help me.

2021-05-13 12:02:31 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup to mssql+pyodbc://username:password@serverip:1433/homeassistant?charset=utf8mb4;driver=FreeTDS: (pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Mirabis · May 16, 2021, 1:37pm

Re-creating the database you lose your history right ;o? hmmm I’ll wait for another solution than.

KiLLeRRat · May 16, 2021, 7:26pm

Hey guys, Is there an open issue on GitHub for this one?

encor50 · May 17, 2021, 7:52am

i change the DB link now i get a migration fail … i dont know how to do the steps from burken
can anyone help please ?

Astrosteve · May 18, 2021, 11:20pm

MSSQL not working once again. It seems to be not working more often than it works.

PlayedIn · May 19, 2021, 3:38am

This worked for me. Thank you!

encor50 · June 3, 2021, 6:23am

again with the 2021.6 what changed this time ?

henri1 · June 3, 2021, 10:08am

Does someone know how the database should look like so it works? If I just try to manually do the required changes. Schema_changes table and all.

rhodges · June 3, 2021, 11:49am

Yup, broken…

2021-06-02 22:01:13 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 14
2021-06-02 22:01:13 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: 'options'
2021-06-02 22:01:13 ERROR (Recorder) [homeassistant.components.recorder] Error during schema migration
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 683, 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 452, in _apply_update
    _drop_foreign_key_constraints(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 322, in _drop_foreign_key_constraints
    and foreign_key["options"].get("ondelete")
KeyError: 'options'

henri1 · June 3, 2021, 8:15pm

Seems to be because of this Oracle DB support which requires schema version change to 16.

Works for me after I commented out this part of the code:

encor50 · June 4, 2021, 5:18am

Well thanks for the info but i dont know how to do that

bdraco · June 5, 2021, 5:22am

We currently don’t have a core developer that uses MSSQL so it doesn’t get active testing: Recorder - Home Assistant

This PR likely fixes it Handle missing options in foreign_key for MSSQL by bdraco · Pull Request #51503 · home-assistant/core · GitHub