Core: Recorder not working with MS SQL Database

[gambith]

Home Assistant release with the issue:

Last working Home Assistant release (if known):
0.103.0b0

Operating environment (Hass.io/Docker/Windows/etc.):
Ubuntu 18.04 LTS / Docker /DB Server: Windows Server 2019 + MS SQL 2017

Integration:
Recorder

Description of problem:
After updating to 0.103.0 recorder is not working with database running on MS SQL

ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymssql' (retrying in 3 seconds)

Problem-relevant configuration.yaml entries and (fill out even if it seems unimportant):

configuration.yaml
recorder:  
   db_url:  mssql+pymssql://user:password@SERVER_IP/DB_NAME?charset=utf8

Traceback (if applicable):

Additional information:

[VDRainer]

Sorry, forget my comment.

[ocalvo]

This is most likely related to "Moving imports of Python packages" from the release notes.
@Bouni, @briglx, @djpremier, @exxamalte, @javicalle, @Michsior14, @Misiu, @mnigbur, @Quentame, @springstan, @thaohtp, @tulindo, If one of you can guide me trough the code or show me a similar PR, I can produce a PR for the fix.

[exxamalte]

While I cannot eliminate the possibility that moving the imports could have caused an issue with MS SQL Server in particular, I don't think that declaring a dependency on the MS SQL Server python library is the correct solution.

Have you had a look at the documentation? This lists manual steps for several databases including MS SQL Server.
https://www.home-assistant.io/integrations/recorder/#ms-sql-server

Maybe there is a smarter way to dynamically load the correct dependency for a particular database engine?

[ocalvo]

So my pr was closed because pymssql is deprecated.
We are supposed to use osbc, does anyone know how to migrate from mssql to osbc?

[exxamalte]

Doco has just been updated.

[GabrieleCastellani]

I have the same error, but reading the new documentation I'm not able to make it working.

How I can configure dsnname in Hassio?
How can I install dependencies in Hassio?

[johnkiddjr]

I have this issue as well.
On HassOS.
Changing config to use pyodbc does not work, and generates the following error message in logs:

Error during connection setup: (pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/rvf5) (retrying in 3 seconds)

[Morduann]

Same issue here on 0.103.0. Worked fine in 0.102.2. Running on Debian 9 + Docker.

[ocalvo]

I think there is a problem with the docs, the odbc string connection should specify the ms sql driver but the sample docs don't mention this.
could someone double check the docs?

[tiaanv]

After doing a bit of research, I have tried what I believe is the right format connection string with a hostname rather than a DSN name as per SQLAlchemy docs.

db_url: mssql+pyodbc://user:[email protected]\SQLEXPRESS/Hass?driver=SQL+Server+Native+Client+10.0

Still no joy: I'm sure I've just done something dumb here

2019-12-14 19:15:57 ERROR (MainThread) [homeassistant.components.hassio] Invalid config for [recorder]: [--db_url] is an invalid option for [recorder]. Check: recorder->recorder->--db_url. (See /config/configuration.yaml, line 99). Please check the docs at https://home-assistant.io/integrations/recorder/

Any help would greatly be appreciated.

EDIT:

Fixed this one by not having a commented line in the config....

Now getting this error:

Error during connection setup: (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server Native Client 10.0' : file not found (0) (SQLDriverConnect)")
(Background on this error at: http://sqlalche.me/e/dbapi) (retrying in 3 seconds)

I'm sure this is becuase the dependencies are not installed correctly... GOing to centure down this path now

[VDRainer]

Not sure if this will help you, but there's a PR to fix this in hassio-homeassistant.
https://github.com/home-assistant/hassio-homeassistant/pull/76

[tiaanv]

Thx.
I'm going to switch to MariaDB in the interim. Will monitor

[sqldiablo]

You can use https://hub.docker.com/r/sqldiablo/home-assistant-pymssql as a temporary fix until my changes make it into the next build of the stock container. You'll need to change your db URL to look like this:

'mssql+pyodbc://my-username:my-password@my-sql-server/my-db?charset=utf8;DRIVER={FreeTDS};Port=1433;'

Despite the container name having pymssql, it's actually pyODBC and the FreeTDS driver, like what I've added to the stock container.

[ocalvo]

@sqldiablo Any ETA of when is this going to be released? Do we need to wait until next year?

[sqldiablo]

I'm not sure, as I'm not involved in the release process. @pvizeli, can you help answer this?

[sqldiablo]

Scratch that. 103.1 just released 30 minutes ago, and that container includes this fix. This issue should be resolved now if you pull the latest tag from https://hub.docker.com/r/homeassistant/home-assistant/

[sqldiablo]

Ok, this is weird. When I pulled latest, I got 103.0 of Home Assistant, but the container does include the fix for this issue. So, you won't get 103.1 just yet, but you will get 103.0 with the FreeTDS driver.

[ocalvo]

@sqldiablo How do I pull the container in hassos/hassio?

[sqldiablo]

You'll need to wait until they release the 103.1 container so that hassio will see the update, then you can just update like you normally would, and it'll pull down the new container for you.

[johnkiddjr]

I reinstalled 103.0 about 20 minutes ago and it works now for me on HassOS. Then immediately after installing 103.0 again, I was offered 103.1

[ocalvo]

Updating to hassos 3.7 now....

[sqldiablo]

Yep, the 103.1 containers are out there now. I must have just jumped the gun a little.

[ocalvo]

Is the format still mssql+pyodbc://user:pass@/HOME_ASSISTANT ?

[sqldiablo]

Scroll up in the comments. I posted what the new format is.

[ocalvo]

I see, we should probably update the docs.

[ocalvo]

My update to 0.103.1 failed:

19-12-19 00:16:09 WARNING (MainThread) [hassio.homeassistant] Don't wait anymore of Home Assistant startup!
19-12-19 00:16:09 CRITICAL (MainThread) [hassio.homeassistant] HomeAssistant update fails -> rollback!
19-12-19 00:16:09 INFO (MainThread) [hassio.homeassistant] Update Home Assistant to version 0.103.0
19-12-19 00:16:09 INFO (SyncWorker_18) [hassio.docker.interface] Update image homeassistant/raspberrypi4-homeassistant:0.103.1 to homeassistant/raspberrypi4-homeassistant:0.103.0

[sqldiablo]

I'm having issues with 103.1 not starting as well. I've reverted back to 103.0.

[ocalvo]

Here is the issue

[gambith]

Seems to be fixed in 0.103.2

Add or change the following lines to configuration.yaml

recorder:
db_url: mssql+pyodbc://my-username:my-password@my-sql-server/my-db?charset=utf8;DRIVER={FreeTDS};Port=1433;