I can't connect to MySQL 8 - ER_NOT_SUPPORTED_AUTH_MODE

[maxacarvalho]

I'm trying to load the project locally using the Docker command, when trying to connect to my local MySQL 8 database I get the following error:

Runtime Error
Cube.js was unable to connect to the specified database.

The database returned the following error:


>Database Error

Error: Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

It seems to me that the client used by Cube does not support MySQL 8 authentication method.

[keydunov]

It can be solved on the MySQL side, please see this https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server.

I'll keep it open to investigate whether something could be done on the Cube.js side as well -- @ovr

[maxacarvalho]

Hi @keydunov, thanks for your reply.

Although the proposed solution should work, it forces the downgrade to a less secure way to handle MySQL authentication, you can read about that following the same link you shared with me and checking out the second most voted answer, there's even a proposed solution for that.

I can downgrade the security on my local and staging environments but that will not be possible on my production environment, so it would make CubeJs impossible to use.

Any thoughts?

[keydunov]

Agreed. It makes sense to consider moving MySQL driver to mysql2 package. Would you be open to contributing it?

[maxacarvalho]

Hi @keydunov I'd love to, but my knowledge about TypeScript and even full-node application is limited. I'm mostly a PHP user that has the "necessary" knowledge about frontend. I don't know if would be "simply" the case of changing the package requirement here https://github.com/cube-js/cube.js/blob/master/packages/cubejs-mysql-driver/package.json#L33?

[ovr]

Hello @maxacarvalho ,

I think it's easier to do a workaround to solve this issue. I suggest change the configuration for the MYSQL server:
But it's not good from a security perspective.

--default-authentication-plugin=mysql_native_password

OR

ALTER USER the_user IDENTIFIED WITH mysql_native_password BY 'the_password';

I don't know if would be "simply" the case of changing the package requirement here

Nope, it's not all. There is a good example with mysql2, it's our driver for MongoDB

https://github.com/cube-js/cube.js/blob/master/packages/cubejs-mongobi-driver/src/MongoBIDriver.ts

BTW: There is a PR mysqljs/mysql#2233 in the MySQL package, which introduces support, but it is still not merged after few years 😄 Probably, we can do a fork and use it.

Thanks

[prattcmp]

+1

[AMMJ93]

Downgrading security to make an app work is not a solution in my opinion. Has this been picked up? +1

[github-actions]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you.
If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines.
You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[atomlab]

The same problem. Changing mysql configuration is not solution. This looks like a bug of cube.

[rahul-n18]

@maxacarvalho why don't you try to reconfigure mysql server and change the Authentication?

have you tried that method using mysql Installer?
hope this helps :)

[maxacarvalho]

Hi @rahul-n18
Thanks for the suggestion, but that would mean that I'd have to lower the security on my DB server, that's not an option, I'm afraid.

[igorlukanin]

I've added a note on the workaround to the docs: https://cube.dev/docs/product/configuration/data-sources/mysql#connecting-to-mysql-8

Until the MySQL driver is updated (contributions are welcome!), this is the recommended approach.