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
- I donât know where the SQL statement for creating the table âstatesâ can be changed.
- Would it be enough to manually create the table by modifying the statement?
- 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 