Mysql 8 fixes

[sidorares]

still WIP, but tests pass and I think now it's only matter of adding docs

• "authSwitchHandler" is deprecated in favour of "authPlugins"
• mysql8 specific tests fixed ( mostly GRANT not creating users and standard geometry functions name update )
• Add SSL-enabled server to test matrix and test all combos of compression + ssl
• add default authPlugin for caching_sha2_password and mysql_native_password auth methods

this would not be possible without https://github.com/mysqljs/mysql/pull/1962

cc @ruiquelhas and @johannes

some related previous API discussion: https://github.com/mysqljs/mysql/issues/1793 https://github.com/sidorares/node-mysql2/issues/560

should fix: https://github.com/sidorares/node-mysql2/issues/906 https://github.com/sidorares/node-mysql2/issues/991

[RedactedProfile]

I know you just merged this into master 2 days ago, but might I ask when NPM will be triggered with a release for this? Thanks

[sidorares]

@RedactedProfile the plan is to bump major version, and just to be on the safe side release -alpha1 and have it for few days. I'll try to publish alpha release today, would be good to have you among early testers. I'll post a comment here when published

[ruiquelhas]

@sidorares I didn't have the chance to look at the code yet. But I'll gladly run some quick tests of that alpha release.

[sidorares]

@ruiquelhas published as mysql2@2.0.0-alpha1

[ghost]

mysql2@2.0.0-alpha1 tested and working with 8.0.17 and latest authentication mode.

The timing of this release could not have come at a better time!

[sidorares]

Thanks @robstev0 , I'll leave it for few more days in alpha1 and happy to cut 2.0 if no regressions

[ruiquelhas]

Works with MySQL 5.7 (mysql_native_password only), 8.0.3 (pre-caching_sha2_password), 8.0.11 (first GA with both authentication plugins) and 8.0.18 (current GA with both authentication plugins) using empty or non-empty passwords.

LGTM

Thanks @sidorares

[claytongulick]

Works like a charm for me!

[sidorares]

Thanks everyone who helped with testing, I'm planning to publish 2.0 in a day or two (same code as alpha1, maybe documentation updates only)

[treyhuffine]

Thanks for the hard work in making this happen @sidorares :)

[mwalden2004]

Looks like I'm still having issues with 8.0, and sha256_password. Getting:

Error: Server requests authentication using unknown plugin sha256_password. See TODO: add plugins doco here on how to configure or author authentication plugins.

[sidorares]

what's your server version @mwalden2004 ? Plugin name is different from what most 8.x server report ( notice it's sha256_password and not caching_sha2_password )

[mwalden2004]

Didn't notice, sorry! The version is 8.0.18. Trying everything I can to fix this error, but no matter what I change the users plugin type to, it doesn't work.

[sidorares]

you could manually alias it like this:

const caching_sha2_password = require('mysql2/lib/auth_plugins/caching_sha2_password.js');
const connection = mysql.createConnection({
  authPlugins: { 
      sha256_password: caching_sha2_password({})
  }
})

( didn't test that but hope you get the idea )

[mwalden2004]

Still getting the same error with that code, I made sure that I am running the latest version of mysql2. Don't know what to do from here. Thank you for the assistance so far.

[sidorares]

could you try to debug this lines ( with console.log or debugger ) ? https://github.com/sidorares/node-mysql2/blob/ebc2cb438d380d81d57ae6e2d227bfafc684eb2a/lib/commands/auth_switch.js#L42-L50

check spelling and what server expects vs what is actually in authPlugins

Note that I slightly updated the example, require('mysql2/lib/auth_plugins/caching_sha2_password.js') returns plugin factory function and you need to use plugin instance in the config

[sidorares]

@ruiquelhas any idea what servers return sha256_password as plugin name? Is it the same as caching_sha2_password? Should I add alias?

[mwalden2004]

Okay, looking into this now. So it looks like authSwitchRequest is called two times, the first it sends in sha256_password as the plugin name, and the second time standardAuthPlugins is undefined. After which it appears it errors out with the throw Error()

[mwalden2004]

I've added an alias inside the auth switch, and now it's trying to force me to use root! I'll look into this in the morning. Good night!

[ruiquelhas]

@sidorares sha256_password is a different plugin, but it's definitely not the default one. So, somehow, the default_authentication_plugin server option was overridden, either via config file or via the --default-authentication-plugin startup option.

https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html

[sidorares]

@mwalden2004 looks like using caching_sha2_password here is wrong approach, either you need to configure server to use different auth plugin or we need to add sha256_password support

[johannes]

Adding support for sha256_password isn't rocket science, but I would advise against usage ... it might be worthwhile with 5.7 or older servers as password storage on the server is more secure than mysql_native_password (which is relevant for threat cases where an attacker gains access to crypted passwords i.e. from a leaked server backup or something) but on 8.0 servers (thus anything new) the caching variant is notable faster, while bringing the same security benefits over the old native password.

[mwalden2004]

Well here is where this gets really interesting, I have two other projects running off this database on the server running mysql2, but this is the only one having any issues. I also am not using sha256_password, looks like it is being forced...

[sidorares]

auth plugin can be configured per user / ip. Are those same for two other projects?

[mwalden2004]

They are the same.

[pokurrunku]

@mwalden2004 did you find any solution for sha256_password

[Pezhvak]

this happened to me as well, it was working fine, all of the sudden it stopped working

[sidorares]

Hi @Pezhvak can you clarify exactly what stopped working?

Note that we recently added support for sha256_password auth plugin and it's still relatively experimental

[Pezhvak]

@sidorares it started giving me that error message which i didn't get before, it turns out to be a problem with terminal which runs it, if i run it on iterm i get that error, if i run it inside phpstorm i don't get that (using run feature of the ide), i can experiment more and let you know if you will.

[johannes]

@Pezhvak are you using the same node installation? I could imagine that you might have a global installation with a different version and one node installation picks it up, giving you old code. Running console.log(require.main.paths) and console.log(require.resolve('mysql2')) could help to verify this.

[Pezhvak]

i will check it and get back to you @johannes

[TheDirigible]

This just started happening to me out of nowhere, after Windows Terminal updated. I don't have any plugin, I don't know what that message is talking about.

[Pezhvak]

this happened to me, it seems to be something related to user or where from you call your file