MySQL8: Client does not support authentication protocol requested by server

[benderillo]

Bug report

Required System information

• Node.js version: 16
• Strapi version: 4.2.2
• Database: MySQL 8
• Operating system: Mac

Describe the bug

TL;DR
Mysql database driver used by Strapi does not support the new authentication method that MySQL8 uses by default.
This generates error:

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

Long story
Currently Strapi uses mysql npm package as a driver to connect to MySQL database. This package does not support the new authentication method (caching_sha2_password ) which some MySQL8 hosted databases use by default.
For example, if you deploy on Railway.app, their mysql db will refuse connection.

The solution can be either lower the security of the Database by configuring it to use the old mysql_native_password auth method or use another package for mysql driver which supports the authentication: mysql2
The details can be found in this answer in stackoverflow: https://stackoverflow.com/a/56509065/985942

Unfortunately, using the mysql2 package as a driver for the DB fails because strapi passes the client name ('mysql') as a package name to "knex" so the "knex" fails to find such a package upon loading.
Specifying a "mysql2" as a client in the config file fails too as the Strapi database package has explicit checks for "mysql" client name.

This ticket is to ask to add support for the mysql2 driver to strapi so developers can opt to install it instead of the mysql.
Since the mysql2 package is mostly API compatible with the mysqljs it should be an easy fix to treat the driver mysql2 the same way as the mysql.

Steps to reproduce the behaviour

1. Create a new strapi project and configure it to use MySQL database
2. Install a MySQL8 database and configure project to connect to it.
3. Run Strapi project and observe it fails to connect because of the ER_NOT_SUPPORTED_AUTH_MODE error.
4. Install mysql2 npm install mysql2 and change the database configuration to use "mysql2" driver.
5. Try to run Strapi and observe that it fails as the driver is not supported.

Expected behaviour

Should be able to select "mysql2" as a driver for a MySQL database.

UPDATE
PR for this issue: #13775

[derrickmehaffy]

Not really a bug but related to: https://feedback.strapi.io/developer-experience/p/add-support-for-mysql2-mysql-v8-auth-system

[SunSparc]

MySQL version 8 was released 4 years ago. Seems like it is probably time to update the authentication methods to match.

[derrickmehaffy]

MySQL version 8 was released 4 years ago. Seems like it is probably time to update the authentication methods to match.

Please feel free to submit a PR :) Else for the moment it's not really a priority as the simple work around is using the native_password authentication method in MySQL 8

[AndreyKraynoff]

made database.js like this:

module.exports = ({ env }) => ({
  connection: {
    client: 'mysql2',
    connection: {
      host: env('DATABASE_HOST', '127.0.0.1'),
      port: env.int('DATABASE_PORT', 3306),
      database: env('DATABASE_NAME', 'DATABASE_NAME'),
      user: env('DATABASE_USERNAME', 'DATABASE_USERNAME'),
      password: env('DATABASE_PASSWORD', 'DATABASE_PASSWORD'),
      ssl: env.bool('DATABASE_SSL', true),
    },
  },
});

And got this error: Unknown dialect mysql2

[ricky11]

any update this? strangely it was working , but either railway (MySQL host) or strapi had some changes that broke when I try to run npm run develop.

[derrickmehaffy]

Not currently, we haven't had time yet to draft a solid PR