After update to 2021.5.0 recorder fails to start due to connection failure.
Error during connection setup to mssql+pyodbc://[email protected]/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.
/J
Same issue here !
Same here !
Same issue here…
Also same problem, reverted back to 2021.4.6.
Me five. Just rolled it back.
Weird, after the rollback to 2021.4.6. I updated again and now everything is working.
Just got this warning:
WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 13
But that was explained in the release notes of 2021.5.0 (Database upgrade).
I just tried this. Still broken for me. 
I’m SQL Server as the database for HA and it’s also broken for me. In my connection string I have to include the DRIVER=FreeTDS, maybe that is where the issue is.
I managed to get one step further. I updated the connection string according to Microsoft SQL Server — SQLAlchemy 1.4 Documentation
Previous syntax: mssql+pyodbc://usr:[email protected]/homeassistant?charset=utf8;DRIVER={ODBC Driver 17 for SQL Server};Port=1433;
New syntax: mssql+pyodbc://usr:[email protected]:1433/homeassistant?charset=utf8&driver=ODBC+Driver+17+for+SQL+Server
Now home assistant manages to connect but throws
sqlalchemy.exc.DataError: (pyodbc.DataError) ('22007', '[22007] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Couldn't convert to date and/or time from character string. (241) (SQLExecDirectW)')
[SQL: INSERT INTO schema_changes (schema_version, changed) OUTPUT inserted.change_id VALUES (?, ?)]
[parameters: (14, '2021-05-07 06:21:46.507725 +00:00')]
Yes!!! That worked for me!
Setting port number after the host. eg host:port
Noppp To fast… Same error…
The SQL datatype is ‘datetime’. I created a quick table to do some testing using SSMS (SQL 2017 in a docker container)
-- Conversion failed when converting date and/or time from character string.
INSERT INTO asdf(test) VALUES('2021-05-07 13:12:36.967296 +00:00')
-- This works (note the omission of timezone and less precision on the seconds fraction)
INSERT INTO asdf(test) VALUES('2021-05-07 13:12:36.967')
datetime (Transact-SQL) - SQL Server | Microsoft Docs
For now, I went back to the old mssql+pymssql connection string (I’m building my own docker) for now.
In Home Assistant 2021.5.0 Sqlalchemy was upgraded to 1.4.11. I checked the changelog (for sqlalchemy) and found that in 1.4.12 a bug was fixed:
…
Fixed regression caused by #6306 which added support for DateTime(timezone=True) , where the previous behavior of the pyodbc driver of implicitly dropping the tzinfo from a timezone-aware date when INSERTing into a timezone-naive DATETIME column were lost, leading to a SQL Server error when inserting timezone-aware datetime objects into timezone-native database columns.
…
This seems to be the root of the db-error that is thrown.
So no current solution…
OK… Your on to something there… Nice work!
Just playing around with Python, not sure if this would break all the other database types:
import datetime
import pytz
import pytz.tzinfo as pytzinfo
UTC = pytz.utc
def process_timestamp_to_utc_isoformat(ts):
"""Process a timestamp into UTC isotime."""
if ts is None:
return None
if ts.tzinfo != UTC:
ts = ts.astimezone(UTC)
return ts.replace(tzinfo=None).isoformat(timespec='milliseconds')
def test_me(value):
result = process_timestamp_to_utc_isoformat(value)
print(f'({value.tzinfo}): {value} => {result}')
unsetnow = datetime.datetime.now()
localnow = unsetnow.astimezone()
utcnow = unsetnow.astimezone(UTC)
test_me(unsetnow)
test_me(localnow)
test_me(utcnow)
(None): 2021-05-07 09:58:55.151784 => 2021-05-07T14:58:55.151
(Central Daylight Time): 2021-05-07 09:58:55.151784-05:00 => 2021-05-07T14:58:55.151
(UTC): 2021-05-07 14:58:55.151784+00:00 => 2021-05-07T14:58:55.151
A change something like that would result in something MSSQL could handle. It might break every other database, I haven’t a clue.
Maybe the aforementioned sqlalchemy change will fix handling the timezone and the excessive precision on the seconds. That would be the preferred case of course.
Edit: I can confirm that if the MSSQL column type is datetimeoffset(6) then this works: (as mentioned here: Implement mapping to MSSQL "DateTimeOffset" type when using SQLAlchemy generic type Datetime(Timezone=True) · Issue #6306 · sqlalchemy/sqlalchemy · GitHub)
INSERT INTO asdf(test2) VALUES('2021-05-07 13:12:36.967296 +00:00')
To get Home Assistant 2021.5.0 running with mssql odbc-drivers it is possible to do the following:
alter table dbo.[events]
alter column time_fired datetimeoffset(6) null;
alter table dbo.[events]
alter column created datetimeoffset(6) null;
6. Update the column data to match your timezone (mine is w.europe)
update [events] set
time_fired = convert(datetime2, time_fired) at time zone ‘W. Europe Standard Time’,
created = convert(datetime2, created) at time zone ‘W. Europe Standard Time’
Not leaving any guarantees… please backup before doing anything so that you can revert if necessary. Or wait to see if next release has an updated version of sqlalchemy that can handle this without manual alterations 
Is #6 required? I thought the code was inserting UTC dates into the database.
Not that matters from a space saving perspective but datetimeoffset(6) would more closely match the 6 digit precision the code was setting
You’re absolutely right!