search

home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.
https://www.home-assistant.io
Apache License 2.0
71.8k stars 30.06k forks source link

Core-2021.5.0 : broken MSSQL connection #50142

Closed JanneSaare closed 3 years ago

JanneSaare commented 3 years ago

The problem

After update to 2021.5.0 recorder fails to start due to connection failure. Error during connection setup to mssql+pyodbc://nnnnnnnnn@192.168.0.23/HomeAssistant?charset=utf8;DRIVER={FreeTDS};Port=1434;: (pyodbc.InterfaceError) (‘IM002’, ‘[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)’)

Every thing was working fine before this upgrade. Core 2021.4.nn

What is version of Home Assistant Core has the issue?

Core-2021.5.0

What was the last working version of Home Assistant Core?

core-2021.4.

What type of installation are you running?

Home Assistant OS

Integration causing the issue

Recorder

Link to integration documentation on our website

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:813
Integration: recorder (documentation, issues)
First occurred: May 5, 2021, 10:01:34 PM (10 occurrences)
Last logged: May 5, 2021, 10:02:01 PM

Error during connection setup to mssql+pyodbc://xxxxx:xxxx@192.168.0.23/HomeAssistant?charset=utf8mb4;DRIVER={FreeTDS};Port=1434;: (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/14/rvf5) (retrying in 3 seconds)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3142, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 301, in connect
    return _ConnectionFairy._checkout(self)

Additional information

No response

probot-home-assistant[bot] commented 3 years ago

recorder documentation recorder source (message by IssueLinks)

dolenec commented 3 years ago

Same error here:

Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:813
Integration: recorder (documentation, issues)
First occurred: 10:38:23 (10 occurrences)
Last logged: 10:38:50

Error during connection setup to mssql+pyodbc://XXXX:XXXX@192.168.XXX.XXX/ha?charset=utf8;DRIVER={FreeTDS};Port=1433;: (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/14/rvf5) (retrying in 3 seconds)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3142, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 301, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 761, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 419, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 142, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 362, in __init__
    self.__connect()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 605, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 599, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 583, in connect
    return self.dbapi.connect(*cargs, **cparams)
pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 557, in _setup_recorder
    self._setup_connection()
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 813, in _setup_connection
    Base.metadata.create_all(self.engine)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4744, in create_all
    bind._run_ddl_visitor(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3008, in _run_ddl_visitor
    with self.begin() as conn:
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2924, in begin
    conn = self.connect(close_with_result=close_with_result)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3096, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 92, in __init__
    else engine.raw_connection()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3175, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3145, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2004, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3142, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 301, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 761, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 419, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 142, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 362, in __init__
    self.__connect()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 605, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 599, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 583, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.InterfaceError: (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/14/rvf5)
dolenec commented 3 years ago

Another log:

Logger: homeassistant.components.sql.sensor Source: components/sql/sensor.py:68 Integration: sql (documentation, issues) First occurred: 10:39:28 (1 occurrences) Last logged: 10:39:28

Couldn't connect using mssql+pyodbc://****:****@192.168.XXX.XXX/ha?charset=utf8;DRIVER={FreeTDS};Port=1433; DB_URL: (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/14/rvf5)

dolenec commented 3 years ago

This problem is solved by setting host:port in the URL. eg mssql+pyodbc://:@192.168.XXX.XXX:1433/ha?charset=utf8;DRIVER={FreeTDS}; Please add/update documentation.

Not true!

Changed from: mssql+pyodbc://USERNAME:PASSWORD@192.168.XXX.XXX/ha?charset=utf8;DRIVER={FreeTDS};Port=1433; to mssql+pyodbc://USERNAME:PASSWORD@192.168.XXX.XXX:1433/ha?charset=utf8;DRIVER={FreeTDS};

Problem is still the same..

dolenec commented 3 years ago

_This problem is solved by setting host:port in the URL.

eg @.***XXX.XXX:1433/ha?charset=utf8;DRIVER={FreeTDS};

Please add/update documentation._

Not true!

Changed from:

@.***XXX.XXX/ha?charset=utf8;DRIVER={FreeTDS};Port=1433;

to

@.***XXX.XXX:1433/ha?charset=utf8;DRIVER={FreeTDS};

Problem is still the same..

From: JanneSaare @.> Sent: Friday, May 7, 2021 11:54 AM To: home-assistant/core @.> Cc: Simon Dolenec @.>; Comment @.> Subject: Re: [home-assistant/core] Core-2021.5.0 : broken MSSQL connection (#50142)

This problem is solved by setting host:port in the URL. eg @.***XXX.XXX:1433/ha?charset=utf8;DRIVER={FreeTDS}; Please add/update documentation.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/home-assistant/core/issues/50142#issuecomment-834226230 , or unsubscribe https://github.com/notifications/unsubscribe-auth/AD643BTMP7R2U2IIU4O34F3TMO2EXANCNFSM44GJO67Q . https://github.com/notifications/beacon/AD643BUWOA5Y6KT3TPCHADDTMO2EXA5CNFSM44GJO672YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOGG4UQNQ.gif

bjotje commented 3 years ago

I have tried changing the driver to something random like "hello", it will still give the same error. This might suggest either FreeTDS is missing/corrupted or an issue with mssql+pyodbc? I am yet to play around with it a little bit to figure out what is going on.

Error during connection setup to mssql+pyodbc://xxxxx:xxxxx@xxx.xxx.xxx.xxx/Homeassistant?charset=utf8mb4;DRIVER={hello};Port=1433;: (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/14/rvf5) (retrying in 3 seconds)

damousys commented 3 years ago

Same Issue here, also on 2021.5.1 I am on Debian BTW, so looks like no issue with HASSos

TinusBos commented 3 years ago

Same Issue with 2021.5.1 Error during connection setup to mssql+pyodbc://xx:xxx@192.xxx.xxx.xxx:1433/HomeAssistant?charset=utf8;DRIVER={FreeTDS}: (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/14/rvf5) (retrying in 60 seconds)

jonne013 commented 3 years ago

Same issue, core 2021.05.1, Home Assistant OS 5.13

Error during connection setup to mssql+pyodbc://xx:xx@192.xxx.xxx.xxx/homeassistant?charset=utf8mb4;DRIVER={FreeTDS};Port=1433;: (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/14/rvf5) (retrying in 3 seconds)

JanneSaare commented 3 years ago

Problem still exists in 2021.05.2 Has anybody with knowledge of the image content in Hassio reacted on this issue?

dolenec commented 3 years ago

Don't know why nobody fix this.. This is comolete fail from HA team - seems that nobody test working HA on different databases..

bjotje commented 3 years ago

I got the recorder working again! It seems to be an issue in the connection string. I changed from: mssql+pyodbc://xxxxx:xxxxx@xxx.xxx.xxx.xxx/Homeassistant?charset=utf8mb4;DRIVER={FreeTDS};Port=1433; to: mssql+pyodbc://xxxxx:xxxxx@xxx.xxx.xxx.xxx:1433/Homeassistant3?driver=FreeTDS?charset=utf8mb4

I created a new database for homeassistant because of conversion errors during migration, however everything is working again! I hope this might help one (or all) of you as well

damousys commented 3 years ago

@bjotje Issue isn't fixed over here

bjotje commented 3 years ago

@bjotje Issue isn't fixed over here

could you post your connection string?

DavidZidar commented 3 years ago

I also got it working by treating the connection string as a URL. I'm way out of my depth here, but I think the recorder documentation for Home Assistant might be wrong. It seems to me it's mixing up a pyodbc connection string with a sqlalchemy connection URL. There shouldn't be any semicolons in there, at least that's what I think goes wrong.

There is a bunch of documentation here https://github.com/sqlalchemy/sqlalchemy/blob/13d01a03c55890e19f5721405f6e5fb45d37cac4/lib/sqlalchemy/dialects/mssql/pyodbc.py

My working connection string looks something like this: mssql+pyodbc://user:pass@address:1433/hass?driver=FreeTDS&charset=utf8&autocommit=True

However, I also got migration errors so I had to drop my tables and let hass recreate them.

damousys commented 3 years ago

I am using sercrets.yaml

mssql_db: mssql+pyodbc://xx:xx@192.168.2.30:1433//HASSIO??driver=FreeTDS?charset=utf8mb4

bjotje commented 3 years ago

I also got it working by treating the connection string as a URL. I'm way out of my depth here, but I think the recorder documentation for Home Assistant might be wrong. It seems to me it's mixing up a pyodbc connection string with a sqlalchemy connection URL. There shouldn't be any semicolons in there, at least that's what I think goes wrong.

There is a bunch of documentation here https://github.com/sqlalchemy/sqlalchemy/blob/13d01a03c55890e19f5721405f6e5fb45d37cac4/lib/sqlalchemy/dialects/mssql/pyodbc.py

My working connection string looks something like this: mssql+pyodbc://user:pass@address:1433/hass?driver=FreeTDS&charset=utf8&autocommit=True

However, I also got migration errors so I had to drop my tables and let hass recreate them.

It indeed seems like the semicolons mess things up, it turned this: image into this: image

it combined the driver with the charset option, making it look for driver "FreeTDS;charset=utmf8mb4" which obviously doesn't exist.

bjotje commented 3 years ago

I am using sercrets.yaml

mssql_db: mssql+pyodbc://xx:xx@192.168.2.30:1433//HASSIO??driver=FreeTDS?charset=utf8mb4

Could you try doing it like this: mssql+pyodbc://xx:xx@192.168.2.30:1433/HASSIO?driver=FreeTDS?charset=utf8mb4

guerrerotook commented 3 years ago

This works for me but now I have a notification saying that the migration for the database fails.

bjotje commented 3 years ago

This works for me but now I have a notification saying that the migration for the database fails.

I had that too, it showed something in the logs regarding date/time conversion. I didn't bother to look into it as my database was already several days outdated and just created a new one. I think dropping tables might work as well.

guerrerotook commented 3 years ago

Yeah I manually applied the migration described here, https://github.com/home-assistant/core/blob/dev/homeassistant/components/recorder/migration.py#L417

DavidZidar commented 3 years ago

I tried to make a PR to fix the incorrect documentation at least.

bdraco commented 3 years ago

Might be related

https://docs.python.org/3/library/urllib.parse.html#urllib.parse.parse_qs

Changed in version 3.9.2: Added separator parameter with the default value of &. Python versions earlier than Python 3.9.2 allowed using both ; and & as query parameter separator. This has been changed to allow only a single separator key, with & as the default separator.

bdraco commented 3 years ago

The documentation has been updated via https://github.com/home-assistant/home-assistant.io/pull/17801

damousys commented 3 years ago

Fixed with the current documentation, also created a new database