With Mysql 8.0.26 - getting Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

[leonk-sportsbet]

Looks like some Auth mechanism is not supported for Mysql 8.0.26. It does work fine with 'mysql2' npm package - so unfortunately I can't use 'serverless-mysql' :( Any updates on this?

[jeremydaly]

You can use mysql2 as the library. This seems to be an issue with the mysql package.

[leonk-sportsbet]

Actually using 'serverless-mysql' like this gives me an error above:

const mysql = require('serverless-mysql')();
const env = process.env;

mysql.config({
  host     : env.DB_HOST,
  database : env.DB_DATABASE,
  user     : env.DB_USER,
  password : env.DB_PASS
});


async function query(sql, params) {
  try {
    let results = await mysql.query(sql, params)
    console('results: ' + results)
    await mysql.end()
    return results;
  } catch (err) {
    console.error('catch err: ' + err);
    const mysqlErrorList = Object.keys(HttpStatusCodes);
    // convert mysql errors which in the mysqlErrorList list to http status code
    err.status = mysqlErrorList.includes(err.code) ? HttpStatusCodes[err.code] : err.status;
    throw err;
  }
}

// like ENUM
const HttpStatusCodes = Object.freeze({
  ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: 422,
  ER_DUP_ENTRY: 409
});

module.exports = {
  query
}

But with 'mysql2' it works just fine:

const mysql = require('mysql2/promise');
const env = process.env;

const config = {
  db: {
    host     : env.DB_HOST,
    database : env.DB_DATABASE,
    user     : env.DB_USER,
    password : env.DB_PASS
  }
};

async function query(sql, params) {
  try {
    const connection = await mysql.createConnection(config.db);
    const [results, ] = await connection.execute(sql, params);
    // 'connection end' should be handled automatically - but looking at 'Threads_connected' value on the DB side, tells me that I should do it manually?
    await connection.end();
    return results;
  } catch (err) {
    console.error('catch err: ' + err);
    const mysqlErrorList = Object.keys(HttpStatusCodes);
    // convert mysql errors which in the mysqlErrorList list to http status code
    err.status = mysqlErrorList.includes(err.code) ? HttpStatusCodes[err.code] : err.status;

    throw err;
  }
}

// like ENUM
const HttpStatusCodes = Object.freeze({
  ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: 422,
  ER_DUP_ENTRY: 409
});

module.exports = {
  query
}

Ah I see - you're using 'mysql' in your 'serverless-mysql' :( Wish there was an easy fix.......

[AdiMarianMutu]

You can use mysql2 as the library. This seems to be an issue with the mysql package.

When trying to do so, it still does not work because for some reasons this part MYSQL = cfg.library || require('mysql') always falls on the mysql import rather than the value hold by the library key.

const db = require('serverless-mysql')({
  config: {
    host: process.env.MYSQL_HOST,
    port: process.env.MYSQL_PORT,
    database: process.env.MYSQL_DATABASE,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    library: require('mysql2')
  }
})

If manually replaced the require statement from line 354 to import mysql2 everything works just fine.

EDIT: Well, nvm, only now I've noticed that I've specified the library key inside the config object.

const db = require('serverless-mysql')({
  config: {
    host: process.env.MYSQL_HOST,
    port: process.env.MYSQL_PORT,
    database: process.env.MYSQL_DATABASE,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    library: require('mysql2')  // <-- NOT HERE
  },
  library: require('mysql2') // <-- HERE
})

Regarding the error, this answer on SO does explain it very well. Maybe you should update the lib to use by default mysql2 and allow to use mysql by defining a legacy key in the config.

[diegoalzate]

require('mysql2')

how can this be done in a ts & esm project?

EDIT: The way I managed to do this with Typescript and ESM was basically forcing types since require does not work in esm

import mysql from "serverless-mysql";
import mysql2 from "mysql2";

const db = mysql({
      config: {
        host: process.env.DB_HOST,
        database: process.env.DB_DATABASE,
        user: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
        port: Number(process.env.DB_PORT),
      },
      library: mysql2 as unknown as Function,
    });

[rain89146]

require('mysql2')

how can this be done in a ts & esm project?

EDIT: The way I managed to do this with Typescript and ESM was basically forcing types since require does not work in esm

import mysql from "serverless-mysql";
import mysql2 from "mysql2";

const db = mysql({
      config: {
        host: process.env.DB_HOST,
        database: process.env.DB_DATABASE,
        user: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
        port: Number(process.env.DB_PORT),
      },
      library: mysql2 as unknown as Function,
    });

first, install the mysql 2

npm install mysql2

then in the

library: require('mysql2')