MySQL through an SSL connection

Configuration
1

I sent in feedback for the documentation page for recorder as well, so hopefully it’ll be reviewed and updated but posting this here as well in case it helps anybody else solve my scenario.

The documentation suggests that you use the following config line for an SSL protected database:

mysql://user:password@SERVER_IP/DB_NAME?charset=utf8mb4;ssl=true

However this seemed to constantly fail thinking that “;ssl=true” was part of the charset:

Error during connection setup: (MySQLdb.OperationalError) (2019, “Can’t initialize character set utf8mb4;ssl=true (path: compiled_in)”) (Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation) (retrying in 3 seconds)

On a hunch I replaced the semicolon with an ampersand to match more common url parameters and also set the MySQL server to only allow SSL connections. This format seems to work:

mysql://user:password@SERVER_IP/DB_NAME?charset=utf8mb4&ssl=true

When I remove the “&ssl=true” section I get the expected failure result (confirming that this parameter isn’t just being ignored):
Error during connection setup: (MySQLdb.OperationalError) (3159, ‘Connections using insecure transport are prohibited while --require_secure_transport=ON.’) (Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation) (retrying in 3 seconds)

3 Likes
2

Thank you so much @ahlswede !!
I was facing the exact same issue, and you’ve saved my bacon! Thanks!

3

thanks

&ssl=true got me working again,
looks like it even needs to be used when ?charset=utf8mb4 is not even being used

1 Like
4

I just did an HA update today and now MySQL refuses to allow connection again, idea’s ?

no passwords or host names etc have been changed mysql settings have not been played with

[Warning] Access denied for user ‘homeassistant’@‘homeassistant.fritz.box’ (using password: YES)

  • Core2024.3.0
  • Supervisor2024.02.1
  • Operating System12.0
  • Frontend20240306.0

and after about 100 restarts using quick and full trying to change config combinations and even full power off / on , gave up and rolled back to a part backup from 10 hours ago

  • Core2024.2.5
  • Supervisor2024.02.1
  • Operating System12.0
  • Frontend20240207.1

and it connected again somethings up with Core2024.3.0

assuming

creating a proxmox snapshop of current machine

5

I had the same experience upgrading to core2024.3.0, but much like MasterCATZ, rolling back to 2024.2.5 seemed to restore external SQL connections and rolling forward to the next version 2024.3.0b0 introduced the same error state as 2024.3.0:

Error during connection setup: (MySQLdb.OperationalError) (3159, ‘Connections using insecure transport are prohibited while --require_secure_transport=ON.’) (Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation) (retrying in 3 seconds)

Feedback on the documentation was ultimately closed without being addressed due to volume and being stale:

Also, for anybody coming to this thread trying to find a fix who might fumble through Linux (like myself), you can rollback ha core with the command:
ha core update --version 2024.2.5

6

If you are updating Core to 2024.3, see this: https://github.com/home-assistant/core/issues/113847.

It looks like the answer is to update the mysql url again in your config. Where I used to have:

?charset=utf8mb4&ssl=true

I now have:

?charset=utf8mb4&ssl_mode=REQUIRED&ssl=true

This is the only change I needed to make to get things working again.

7

Actually the ssl=true isn’t needed at all any more so the end of the url just becomes:

?charset=utf8mb4&ssl_mode=REQUIRED
1 Like
8

I just tried to upgrade to 2024.7.2 and can confirm the below config now works for me:

?charset=utf8mb4&ssl_mode=REQUIRED

Thanks jw123!