"Client does not support authentication protocol" trying to connect to local MySql database with NestJs app

Question

I'm trying to connect my NestJs application to my MySql database locally. Fellowing the NestJs documentation .

I get this error message:

[Nest] 8696  - 24/08/2023 15:14:40   ERROR [ExceptionHandler] ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Here's my database provider :

import { DataSource } from 'typeorm';

export const databaseProviders = [
  {
    provide: 'DATA_SOURCE_LOCAL',
    useFactory: async () => {
      const dataSource = new DataSource({
        type: 'mysql',
        host: '127.0.0.1',
        port: 3306,
        username: 'root',
        password: 'root',
        database: 'myDb',
        entities: [myEntities],
        synchronize: true,
        insecureAuth: true,
      });

      return dataSource.initialize();
    },
  },

An example of provider (user.prividers.ts) :

import { DataSource } from 'typeorm';
import {User} from './user.entity';
import { Profile } from './profile.entity';

export const userProviders = [
  {
    provide: 'USER_REPOSITORY',
    useFactory: (dataSource: DataSource) => dataSource.getRepository(User),
    inject: ['DATA_SOURCE_LOCAL'],
  }
];

As the documentation preconizes, I'm using "mysql2" and "typeorm" : My package.json file :

{
  "name": "mysql-project",
  "version": "0.0.1",
  "description": "",
  "author": "",
  "private": true,
  "license": "UNLICENSED",
  "scripts": {
    "build": "nest build",
    "format": "prettier --write \"src/**/*.ts\" \"test/**/*.ts\"",
    "start": "nest start",
    "start:dev": "nest start --watch",
    "start:debug": "nest start --debug --watch",
    "start:prod": "node dist/main",
    "lint": "eslint \"{src,apps,libs,test}/**/*.ts\" --fix",
    "test": "jest",
    "test:watch": "jest --watch",
    "test:cov": "jest --coverage",
    "test:debug": "node --inspect-brk -r tsconfig-paths/register -r ts-node/register node_modules/.bin/jest --runInBand",
    "test:e2e": "jest --config ./test/jest-e2e.json"
  },
  "dependencies": {
    "@nestjs/common": "^9.0.0",
    "@nestjs/config": "^2.3.4",
    "@nestjs/core": "^9.0.0",
    "@nestjs/mapped-types": "*",
    "@nestjs/platform-express": "^9.0.0",
    "@nestjs/typeorm": "^9.0.1",
    "class-transformer": "^0.5.1",
    "class-validator": "^0.14.0",
    "mysql": "^2.18.1",
    "mysql2": "^3.6.0",
    "reflect-metadata": "^0.1.13",
    "rxjs": "^7.2.0",
    "typeorm": "^0.3.16"
  },
  "devDependencies": {
    "@nestjs/cli": "^9.0.0",
    "@nestjs/schematics": "^9.0.0",
    "@nestjs/testing": "^9.0.0",
    "@types/express": "^4.17.13",
    "@types/jest": "29.5.0",
    "@types/node": "18.15.11",
    "@types/supertest": "^2.0.11",
    "@typescript-eslint/eslint-plugin": "^5.0.0",
    "@typescript-eslint/parser": "^5.0.0",
    "eslint": "^8.0.1",
    "eslint-config-prettier": "^8.3.0",
    "eslint-plugin-prettier": "^4.0.0",
    "jest": "29.5.0",
    "prettier": "^2.3.2",
    "source-map-support": "^0.5.20",
    "supertest": "^6.1.3",
    "ts-jest": "29.0.5",
    "ts-loader": "^9.2.3",
    "ts-node": "^10.0.0",
    "tsconfig-paths": "4.2.0",
    "typescript": "^4.7.4"
  },

I precise a "funny" thing. Each time than I run the application, I get one (and ony one) new table/entity in the database.

I have 4 entities (Quiz, Question, User, Profile). Id the table "Quiz" is set in database, and I run the app, it will crash with the above message, but the "Question" table will be creatad.

I've already tried this method :

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

But, despite the fact than it's not working, it seems to not be a good practice (like in this topic) and no usable in every situations (cloud database for exemple).

I question myself on the use of "mysql" type in the database provider dans the need of "mysql2" from the documentation (which is founded nowhere in the app).

I read things about "mysql2" whith Node.js like add this to index.js:

const mysql = require('mysql2')

But I don't know how to use that with NestJs.

Answer 1

For me I had to uninstall mysql and then install mysql2

` npm uninstall mysql

` then

npm install mysql2

It worked for me.

Answer 2

The mysql npm module still doesn't support the caching_sha2_password authentication introduced in MySQL 8. So, the solution is to switch to the mysql2 npm module which does.