1. Problem description

A MySQL service is started locally using docker compose. The default image tag is latest. The yaml file is as follows:

## Resource files required for local development
version: '3'
services:
  ### mysql (https://hub.docker.com/_/mysql/)
  db_mysql:
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: 1qaz
      TZ: Asia/Shanghai
    image: mysql
    ports:
      - 3306:3306
    restart: always

Nodejs uses typeorm and the following configuration to link to local MySQL:

DB_TYPE=mysql
DB_HOST=localhost
DB_PORT=3306
DB_USERNAME=root
DB_PASSWORD=1qaz
DB_DATABASE=test
DB_SYNC=true

Bootloader reported an error:

{ Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (/usr/src/app/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
... ...

Problem definition: permission verification error occurred when MySQL client links to MySQL server

2. Problem investigation

MySQL 8.0 – Client does not support authentication protocol requested by server; Consider upgrading MySQL client: the scheme actually executes SQL script, but does not work

ER_ NOT_ SUPPORTED_ AUTH_ Mode after upgrade: this solution also implements SQL to modify the root user’s permission. It is easy to use after many attempts on GitHub. This solution is adopted, and the result is work. This problem is solved

ER_ NOT_ SUPPORTED_ AUTH_ Mode – MySQL server: this solution is not very safe and the problem has been solved in the previous step, but not adopted

Root cause: the client driver of MySQL only supports mysql_ native_ password/mysql_ old_ Password authentication mode, and the default authentication mode after 5.7 is: plugin. MySQL provides two plug-ins:

sha256_ password: Implements basic SHA-256 authentication

caching_ sha2_ password: Implements SHA-256 authentication (like sha256_ password), but uses caching on the server side for better performance and has additional features for wider applicability

Mysql8.0 uses caching by default_ sha2_ Password instead of MySQL_ native_ Password , our solution is to modify the root authentication mode from caching_ sha2_ Password is the traditional mysql_ native_ password.

By querying user information:

mysql> select Host,User,plugin,authentication_string from user where User='root'\G
*************************** 1. row ***************************
                 Host: localhost
                 User: root
               plugin: auth_socket
authentication_string: 
1 row in set (0.00 sec)

In essence, client driver should support auth_ socket.

3. Solutions

Scheme 1: link to MySQL server and execute the following command:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Scheme 2: set the docker startup command and use native password

  db_mysql:
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: 1qaz
      TZ: Asia/Shanghai
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    ports:
      - 3306:3306
    restart: always

Restart nodejs service without errors, problem solved!