Support MySQL 8's new default: caching_sha2_password

[Abscissa]

MySQL v8.0.4 uses a new authentication mechanism by default. Mysql-native needs updates to support this. I suspect this is most likely the cause of the current CI failures on travis.

More info:

• https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/
• https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html
• Fix MySQL 8.0.x incompatibilities mysqljs/mysql#1962
• https://jira.mariadb.org/browse/CONC-312

UPDATE: Here's a temporary workaround for those who have control over their DB server:
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues

[breisig]

It would be nice to get caching_sha2_password for MySQL 8 integrated asap.

[FireController1847]

I agree. There's been the PR mentioned and many new things, unsure why this would not be a major priority.

[Abscissa]

I consider this mysql-native's current number one priority, but unfortunately I've been busy and haven't been able to get back to mysql-native as much as I would like. I still need to look closer at the details to work out the exact protocol needed, and how it differs from the library's current behavior.

As always, a PR that beats me to this would be most welcome, if anyone is so inclined. I'd be glad to answer any questions about the library's internals.

[schveiguy]

Hm... my Linux distro still uses 5.7. Which would mean a lot of work to get a test environment set up.

Looks like the biggest problem here is adding secure sockets as is needed for creating the cached password?

[Abscissa]

Looks like the biggest problem here is adding secure sockets as is needed for creating the cached password?

Uhh, I suppose? I've dug up those links above, but I haven't really had a chance to dive too heavily into them or fully reacquaint myself with mysqln's auth/handshake code.

At a brief glance, I'm not sure my distro even has mysql anymore (just mariadb). FWIW, all of the OSX jobs on mysqln's travis CI, all use an affected version of MySQL (I've set them to "allowed failure" for now since this is an already-known issue). Here's one example, but they all look the same:

https://travis-ci.org/mysql-d/mysql-native/jobs/497662046#L1968

That error message is coming from the server when mysqln tries to authenticate.

[schveiguy]

From reading the links (and I am not an expert on this by any means), it looks like it uses a different more secure hashing algorithm for authentication, but the token somehow needs to be shared over an encrypted connection. The server is going to require an encrypted connection for the initial "cache" of the token, and then afterwards, a secure connection is NOT required. But the server may at any time decide to redo the secure connection.

So mysqln will have to support SSL out of the box.

FWIW, all of the OSX jobs on mysqln's travis CI, all use an affected version of MySQL

According to the docs, you can still set the authentication mechanism back to the original, but not sure if you have the ability to do this on travis.

[Abscissa]

So mysqln will have to support SSL out of the box.

/facepalm, You know, I saw that too when I glanced through the materials, but somehow it just didn't register in my head that mysqln isn't already using SSL and will need to. (Too much on my mind!)

I haven't dealt directly with SSL sockets (just setting a vibe app behind an https reverse proxy and tossing https URLs at a client lib). Do you have familiarity with the D options available for this, what's good, and what dependencies they have? I assume Vibe probably has a mechanism for this (though I've no idea what libs it uses under the hood for it). AIUI, there's nothing directly in Phobos. I'd definitely hate to deal with OpenSSL directly (not the least of which because of all that keeping up with flaws/exploits being found in X version or Y version, and always chasing that rabbit, or v3-vs-not-v3, etc).

One thing that will be important to be mindful of is the fact that mysqln supports both Phobos sockets and Vibe sockets. And that the decision of which sockets are used is based on whether the Vibe is included in the user's project (Has vibe -> vibe sockets. No vibe -> phobos sockets).

FWIW, all of the OSX jobs on mysqln's travis CI, all use an affected version of MySQL

According to the docs, you can still set the authentication mechanism back to the original, but not sure if you have the ability to do this on travis.

Yea, I'll have to check into that. If it is possible, it shouldn't be too terribly difficult. In any case, I'll definitely want to keep at least one allow-failure job intentionally using the newer MySQL, so there's at least SOME way to test a fix (since neither of us appear to have it available on our own machines.)

[anon17]

As I understand, it's a SCRAM-like protocol (used in postgres), where you don't send the password in plain text. You don't need secure connection, server already has the password's hash, which is used as shared secret to verify that client has password too, but doesn't send it.

[anon17]

Ah, or not.

[hackdavid]

if you are facing "caching_sha2_password" error during connectivity of your mysql database using python ,
go through this
https://www.youtube.com/watch?v=YNq-EuQEJos