SQL Server

Installation
1

Hi,

I recently got a Raspberry Pi and installed Home Assistant on it. I am trying to get recorder to use one of my SQL Server database servers, but I am having some issues with that.

During startup, it creates three tables (events, recorder_runs, schema_changes), so it seems like it can get to the server. I pasted an extract from one of the error messages bellow.

What am I missing? I can’t tell if there are additional dependencies that are required for Home Assistant OS or not.

Thanks,
Tyrel

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 543, in do_rollback
dbapi_connection.rollback()
pyodbc.ProgrammingError: (‘25000’, ‘[25000] [FreeTDS][SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (3903) (SQLEndTran)’)

2

First thing I tried was adding autocommit=True; to the connection string. That moved the error from complaining about the missing begin tran to a complaint about a foreign key.

I found the following post where is talks about changing the code that creates the foreign key action to NO_ACTION. I found that file in \homeassistant\components\recorder\models.py in the source from Git, but those folders appear to be empty if I go to them using the terminal on my Raspberry. Are those folders hidden? The sudo command does not exist in that view. I am a Windows developer and I have not worked much in Linux, so it’s probably something simple. Any help? :slight_smile:

old_state_id = Column(
    Integer, ForeignKey("states.state_id", ondelete="NO ACTION"), index=True
3

Creating the states table by hand solved the problem. I restarted HA and off it went, no errors and stuff going into the DB.

1 Like