JavaScript

When attempting to connect to MySQL 8.0.21 server running Ubuntu 20.04 using NodeJS and mysql2 package, I receive the common error below: Server requests authentication using unknown plugin sha256_password I know that mysqljs and mysql2 do not support sha256, so I confirmed my user was setup for mysql_native_password:

ALTER USER 'userName'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

And have confirmed that default_authentication_plugin is set as mysql_native_password.

What makes this a strange issue, is that it only occurs when attempting to unit test the function in Mocha or Jest. When running the app normally, I am able to connect and make DB calls with no issues. To simplify troubleshooting, I created a new app.js file that only calls the dbQuery.getRow() function. Contents of those files and the output is given below.

app.js

(async function main () {
    require('dotenv').config({ path: __dirname + '/config/.env' });
    const dbQuery = require('./src/js/dbQuery');
    let result = await dbQuery.getRow('table', 'c9024a7aead711eab20be6a68ff5219c');
    console.log(result);
})();

dbQuery.js

const dbPool = require('./dbPool');
async function getRow(tableName, guid) {
    try {
        let sql = `
                SELECT *
                FROM `${tableName}`
                WHERE guid='${guid}'`;
        let [rows] = await dbPool.execute(sql);
        return rows[0];
    } catch (ex) {
        console.log('dbQuery getRow failed with error: ' + ex);
        return { error: true, message: ex };
    }
}

dbPool.js

const { env } = require('process');
const mysql = require('mysql2/promise');
const dbPool = mysql.createPool({
    host: env.DB_HOST,
    port: env.DB_PORT,
    database: env.DB_NAME,
    user: env.DB_USER,
    password: env.DB_PW,
    // waitForConnections: env.WAIT_FOR_CONNECTIONS.toUpperCase() == 'TRUE' ? true : false,
    connectTimeout: 10000,
    connectionLimit: parseInt(env.CONNECTION_LIMIT),
    queueLimit: parseInt(env.QUEUE_LIMIT)
});

module.exports = dbPool;

Terminal Output – Running the simplified app now returns the row as expected

node app.js
BinaryRow {
  guid: 'c9024a7aead711eab20be6a68ff5219c',
  name: 'spiffyRow',
  displayValue: 'Spiffy Display Value'
}

However, when I attempt to do the same DB call in either Jest or Mocha, I run into the issue again, where it appears mysql2 is attempting to use the wrong authentication plugin.

dbQuery.test.js – currently setup for Mocha, but Jest exposed the same issue

const dbQuery = require('../src/js/dbQuery');

describe('MySQL DB Operations', function () {
  describe('#getRow()', function () {
    it('Should return row with guid specified', async function (done) {
      let result = await dbQuery.getRow('table', 'c9024a7aead711eab20be6a68ff5219c');
      if (result.guid == 'c9024a7aead711eab20be6a68ff5219c') done();
      else done(result.error);
    });
  });
});

Terminal Output

npm test

MySQL DB Operations
    #getRow()
dbQuery getRow failed with error: Error: Server requests authentication using unknown plugin sha256_password. See TODO: add plugins doco here on how to configure or author authentication plugins.
      1) Should return row with guid specified


  0 passing (49ms)
  1 failing

Thanks in advance for any help, please let me know if any additional information is needed.

Answer

When executing the tests, my env variables were not being populated. The fix was as simple as adding require('dotenv').config({ path: 'path/to/.env' }); to my test file. I was thrown off by the error message returned by MySQL. I’m still not sure why MySQL responds stating sha256_password is requested when no credentials are provided, even when the default_auth_plugin is set to mysql_native_password, but once valid credentials were provided everything works as expected.

nodejs MySQL – Server requests authentication using unknown plugin

Advertisement

Answer