MS SQL database states cannot be created: FK may cause cycles or multiple cascade paths

Tags: #<Tag:0x00007f326c6fd998>

Problem
The recorder integration cannot be loaded because of the following error:

2021-01-05 22:05:10 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (pyodbc.ProgrammingError) ('42000', "[42000] [FreeTDS][SQL Server]Introducing FOREIGN KEY constraint 'FK__states__old_stat__778AC167' 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)")

[SQL: 
CREATE TABLE states (
    state_id INTEGER NOT NULL IDENTITY(1,1), 
    domain VARCHAR(64) NULL, 
    entity_id VARCHAR(255) NULL, 
    state VARCHAR(255) NULL, 
    attributes VARCHAR(max) NULL, 
    event_id INTEGER NULL, 
    last_changed DATETIME NULL, 
    last_updated DATETIME NULL, 
    created DATETIME NULL, 
    old_state_id INTEGER NULL, 
    PRIMARY KEY (state_id), 
    FOREIGN KEY(event_id) REFERENCES events (event_id) ON DELETE CASCADE, 
    FOREIGN KEY(old_state_id) REFERENCES states (state_id) ON DELETE SET NULL
)

Current setup

  • Pi4 4GB
  • Home Assistant 2020.12.2
  • Home Assistant OS 5.9
  • Microsoft SQL Server 2019
recorder:
  db_url: mssql+pyodbc://homeassistant:[email protected]/HomeAssistant?charset=utf8;DRIVER={FreeTDS};Port=1433;autocommit=True;

What I have tried
First I tried setting up the connection with the official Microsoft ODBC Driver 17 for SQL Server. I got an error message that the lib couldn’t be opened:
Error during connection setup: (pyodbc.Error) (‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘ODBC Driver 17 for SQL Server’ : file not found (0) (SQLDriverConnect)”)

I confirmed that the driver is installed correctly:

➜  lib64 odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2
➜  lib64 cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
UsageCount=1

I checked the documentation but couldn’t figure out the problem. So I switched to FreeTDS which is included in Home Assistant OS. After I changed the db_url (see current setup) there was an error with the transaction handling:

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

So I added autocommit=True; to the connection string as suggested here.

Where I need help

  1. I don’t know where the SQL statement for creating the table “states” can be changed.
  2. Would it be enough to manually create the table by modifying the statement?
  3. I don’t know how to change the SQL statement. Or rather, I cannot estimate what the consequence would be of using ON DELETE NO ACTION. I am just starting to gain experience with SQL (among other things, at this point).

I would appreciate any support and will be happy to provide any further necessary information. Many thanks in advance :slight_smile:

I think there were some changes to the constraints for recorder databases using databases other than the built in SQLite, for example Mysql, Postgresql, and it looks like MSsql. These caused issues. These constraints are setup as HA builds it’s database on these db serves. I am not sure where it currently stands, aka depending on what version of HA you run, when you start HA with Recorder aimed at external database server without HA’s database and tables build, it builds them. But depending on version of HA, with or not the correct constraints. You might check your problem in github issues for HA Core. Have a look at this post, similar to your original issue:

https://github.com/home-assistant/core/issues/44736
1 Like

Thank you very much for your answer. Indeed this seems to be similar to my issue. Also I found another issue that mentions the same error:

https://github.com/home-assistant/core/issues/44396

The update to 2021.1.0 with had no effect on the error.

In the meantime I have created the table manually which seems to work just fine. I’ll wait and see what happens on GitHub. Unfortunately, due to lack of expertise, I can’t really help.