CLOVER🍀

That was when it all began.

Node.jsでMySQL 8.0のデフォルトの認証方式(caching_sha2_password)に対応するには、mysql2を使う

これは、なにをしたくて書いたもの?

MySQL 8.0になって、デフォルトの認証方式がcaching_sha2_passwordからmysql_native_passwordに変更されました。

For the server, the default value of the default_authentication_plugin system variable changes from mysql_native_password to caching_sha2_password.

MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.4 (2018-01-23, Release Candidate)

Node.jsでMySQLに接続するにはmysqlが有名だと思うのですが、mysqlではcaching_sha2_passwordが認証方式になっている場合は
接続できません。

mysql2であれば大丈夫なのですが、今回ちゃんと見ておくことにしました。

MySQL 8.0のデフォルトの認証方式とNode.jsのMySQLドライバー

MySQL 8.0.4のリリースのとおり、デフォルトの認証方式がcaching_sha2_passwordからmysql_native_passwordに変更されました。

For the server, the default value of the default_authentication_plugin system variable changes from mysql_native_password to caching_sha2_password.

MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.4 (2018-01-23, Release Candidate)

これは、認証プラグインの話になりますね。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.4.1 認証プラグイン

具体的には、MySQLのサーバーシステム変数default_authentication_pluginのデフォルト値がcaching_sha2_passwordになったという
変更です。

サーバーシステム変数 / default_authentication_plugin

caching_sha2_password自体も、MySQL 8.0で追加されたものですが。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.4.1.2 SHA-2 プラガブル認証のキャッシュ

以前はこちら(mysql_native_password)になります。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.4.1.1 ネイティブプラガブル認証

このため、caching_sha2_passwordに対応していないクライアントを使用する場合はデフォルトの認証方式をmysql_native_passwordとするか

default_authentication_plugin = mysql_native_password

ユーザー作成時にmysql_native_passwordを指定します。

mysql> create user [username] identified with mysql_native_password by '[password]';

で、Node.jsから接続する際によく使うmysqlはどうなっているかというと、caching_sha2_passwordには対応していないため上記のいずれかの
対応を行い、mysql_native_passwordに切り替える必要があります。

GitHub - mysqljs/mysql: A pure node.js JavaScript Client implementing the MySQL protocol.

issueもオープンのままです。

MySQL 8 incompatibilities · Issue #1959 · mysqljs/mysql · GitHub

以前に、近いことをこちらのエントリー内で書いたことがあります。

Promise-mysqlで、Node.jsからMySQLにアクセスする - CLOVER🍀

mysql2はどうかというと、caching_sha2_passwordに対応しています。

GitHub - sidorares/node-mysql2: :zap: fast mysqljs/mysql compatible mysql driver for node.js

ここで入ったみたいですね。

Mysql 8 fixes by sidorares · Pull Request #1021 · sidorares/node-mysql2 · GitHub

そもそもmysql2自体がmysqlの開発チームと共同で開発していることと、mysqlと主要機能な互換性はあるようなので今後はmysql2を使うべき
なのでしょう。

MySQL2 team is working together with mysqljs/mysql team to factor out shared code and move it under mysqljs organisation.

MySQL2 is mostly API compatible with mysqljs and supports majority of features.

では、ちょっと確認してみたいと思います。

環境

今回の環境は、こちら。

$ node --version
v16.16.0


$ npm --version
8.11.0

MySQLはこちら。172.17.0.2で動作しているものとします。

$ mysql --version
mysql  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

MySQLサーバーの認証方式は、デフォルトのcaching_sha2_passwordとします。

mysql> show variables where variable_name = 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.02 sec)

準備とお題

MySQLに対して、以下のようにデータベースと2種類のユーザーを作成します。

-- データベース作成
mysql> create database example;


-- MySQL 8のデフォルトの認証方式(caching_sha2_password)のユーザー
mysql> create user user_sha2_auth@localhost identified by 'password';
mysql> create user user_sha2_auth@'%' identified by 'password';
mysql> grant all privileges on example.* to user_sha2_auth@localhost;
mysql> grant all privileges on example.* to user_sha2_auth@'%';


-- MySQL 8以前の認証方式(mysql_native_password)のユーザー
mysql> create user user_native_auth@localhost identified with mysql_native_password by 'password';
mysql> create user user_native_auth@'%' identified with mysql_native_password by 'password';
mysql> grant all privileges on example.* to user_native_auth@localhost;
mysql> grant all privileges on example.* to user_native_auth@'%';

ユーザーは、ひとつはデフォルトの認証方式(caching_sha2_password)、もうひとつはmysql_native_passwordを使ったものですね。

これらのユーザーに対して、mysqlおよびmysql2から接続してみたいと思います。

確認はテストコードで行い、TypeScriptで書くことにします。

Node.js+TypeScriptのプロジェクトを作成

まずはNode.jsプロジェクトを作成します。

$ npm init -y
$ npm i -D typescript
$ npm i -D prettier
$ npm i -D @types/node@v16
$ npm i -D jest @types/jest
$ npm i -D esbuild esbuild-jest
$ mkdir src test

この時点での依存関係は、こんな感じ。

  "devDependencies": {
    "@types/jest": "^28.1.6",
    "@types/node": "^16.11.48",
    "esbuild": "^0.15.2",
    "esbuild-jest": "^0.5.0",
    "jest": "^28.1.3",
    "prettier": "^2.7.1",
    "typescript": "^4.7.4"
  },

scriptsはこんな感じで用意しました。

  "scripts": {
    "build": "tsc --project .",
    "build:watch": "tsc --project . --watch",
    "typecheck": "tsc --project ./tsconfig.typecheck.json",
    "typecheck:watch": "tsc --project ./tsconfig.typecheck.json --watch",
    "test": "jest",
    "format": "prettier --write src test"
  },

各種設定ファイル。

tsconfig.json

{
  "compilerOptions": {
    "target": "esnext",
    "module": "commonjs",
    "lib": ["esnext"],
    "baseUrl": "./src",
    "outDir": "dist",
    "strict": true,
    "forceConsistentCasingInFileNames": true,
    "noFallthroughCasesInSwitch": true,
    "noImplicitOverride": true,
    "noImplicitReturns": true,
    "noPropertyAccessFromIndexSignature": true,
    "esModuleInterop": true
  },
  "include": [
    "src"
  ]
}

tsconfig.typecheck.json

{
  "extends": "./tsconfig",
  "compilerOptions": {
    "baseUrl": "./",
    "noEmit": true
  },
  "include": [
    "src", "test"
  ]
}

.prettierrc.json

{
  "singleQuote": true
}

jest.config.js

module.exports = {
  testEnvironment: 'node',
  transform: {
    "^.+\\.tsx?$": "esbuild-jest"
  }
};

mysqlをインストールします。asyncawaitを使いたかったので、Promise-mysqlもインストールしておきます。

GitHub - CodeFoodPixels/node-promise-mysql: A wrapper for mysqljs/mysql that wraps function calls with Bluebird promises.

$ npm i mysql promise-mysql

mysql2もインストール。

$ npm i mysql2

型定義もインストール。

$ npm i -D @types/mysql

最終的に、依存関係はこうなりました。

  "devDependencies": {
    "@types/jest": "^28.1.6",
    "@types/mysql": "^2.15.21",
    "@types/node": "^16.11.48",
    "esbuild": "^0.15.2",
    "esbuild-jest": "^0.5.0",
    "jest": "^28.1.3",
    "prettier": "^2.7.1",
    "typescript": "^4.7.4"
  },
  "dependencies": {
    "mysql": "^2.18.1",
    "mysql2": "^2.3.3",
    "promise-mysql": "^5.2.0"
  }

テストコードを書いて確認する

あとは、テストコードを書いて確認するだけですね。

mysql

test/mysql-auth.test.ts

import mysql from 'promise-mysql';

test('connect, caching_sha2_password authentication user, failure', async () => {
  try {
    await mysql.createConnection({
      host: '172.17.0.2',
      port: 3306,
      database: 'example',
      user: 'user_sha2_auth',
      password: 'password',
    });
  } catch (e) {
    const error = e as Error;
    expect(error.message).toBe(
      'ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client'
    );
  }
});

test('connect, mysql_native_password authentication user', async () => {
  const connection = await mysql.createConnection({
    host: '172.17.0.2',
    port: 3306,
    database: 'example',
    user: 'user_native_auth',
    password: 'password',
  });

  try {
    const [rows, field] = await connection.query('select 1 as result');
    expect(rows).toEqual({ result: 1 });
  } finally {
    await connection.end();
  }
});

mysqlの場合、caching_sha2_passwordを認証方式(というかデフォルト)にしているユーザーには接続できていません。
認証方式をmysql_native_passwordとしているユーザーへは接続できています。

mysql2。

test/mysql2-auth.test.ts

import mysql2 from 'mysql2/promise';

test('connect, user_sha2_auth authentication user', async () => {
  const connection = await mysql2.createConnection({
    host: '172.17.0.2',
    port: 3306,
    database: 'example',
    user: 'user_sha2_auth',
    password: 'password',
  });

  try {
    const [rows, fields] = await connection.execute('select 1 as result');
    expect(rows).toEqual([{ result: 1 }]);
  } finally {
    connection.end();
  }
});

test('connect, mysql_native_password authentication user', async () => {
  const connection = await mysql2.createConnection({
    host: '172.17.0.2',
    port: 3306,
    database: 'example',
    user: 'user_native_auth',
    password: 'password',
  });

  try {
    const [rows, fields] = await connection.execute('select 1 as result');
    expect(rows).toEqual([{ result: 1 }]);
  } finally {
    connection.end();
  }
});

mysql2の場合は、認証方式がcaching_sha2_passwordであってもmysql_native_passwordであっても接続できます。

これで、動作確認できました、と。

まとめ

mysqlとmysql2の2つで、MySQL 8.0のデフォルトの認証方式であるcaching_sha2_passwordに対応しているか見てみました。

対応しているのはmysql2のみで、今後のことを考えるとmysqlよりもmysql2を使っていった方がよさそうですね。