Not able to authenticate my Node js(v10.15.3) app, npm mysql (v2.17.1) with MySQL(8.0.15) (ER_ACCESS_DENIED_ERROR)

Question

I am running a simple Nodejs(v10.15.3) app trying to access MySQL server (8.0.15). npm mysql package version is v2.17.1. The script is as shown below,

var mysql = require('mysql');

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'test',
    password: '2222',
    database: 'database',
    debug: true          
});

connection.connect(function(err) {
    if (err) {
        console.log(err.stack);
        throw err;            
    }

    console.log("Connected!");
});

Strange thing is I am able to login with same credentials from MySQL client. I have tried almost all approaches mentioned in other forums. Some of things which I have tried,

1. I have changed the password plugin type from caching_sha2_password to mysql_native_password for the user test.
2. Updated the password for the user and did flush privileges.

Attaching the entire stack trace.

<-- HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '8.0.13',
  threadId: 71,
  scrambleBuff1: <Buffer 16 5c 10 12 68 49 73 68>,
  filler1: <Buffer 00>,
  serverCapabilities1: 65535,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 50175,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 41 06 27 5a 0c 4c 4c 30 2f 72 0d 7e>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> (71) ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'test',
  scrambleBuff:
   <Buffer fa ee eb f2 89 a4 f0 91 a9 d1 aa f2 77 08 76 bb ec a2 51 8b>,
  database: 'simplify_meetup',
  protocol41: true }

<-- (71) ErrorPacket {
  fieldCount: 255,
  errno: 1045,
  sqlStateMarker: '#',
  sqlState: '28000',
  message:
   'Access denied for user \'test\'@\'localhost\' (using password: YES)' }

Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'test'@'localhost' (using password: YES)
    at Handshake.Sequence._packetToError (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Handshake.ErrorPacket (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\sequences\Handshake.js:123:18)
    at Protocol._parsePacket (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (D:\Development\simplify-meetup\node_modules\mysql\lib\Connection.js:91:28)
    at Socket.<anonymous> (D:\Development\simplify-meetup\node_modules\mysql\lib\Connection.js:525:10)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    --------------------
    at Protocol._enqueue (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Protocol.js:144:48)
    at Protocol.handshake (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Protocol.js:51:23)
    at Connection.connect (D:\Development\simplify-meetup\node_modules\mysql\lib\Connection.js:119:18)
    at D:\Development\simplify-meetup\routes\EventReminderAPI.js:20:16
    at Layer.handle [as handle_request] (D:\Development\simplify-meetup\node_modules\express\lib\router\layer.js:95:5)
    at next (D:\Development\simplify-meetup\node_modules\express\lib\router\route.js:137:13)
    at Route.dispatch (D:\Development\simplify-meetup\node_modules\express\lib\router\route.js:112:3)
    at Layer.handle [as handle_request] (D:\Development\simplify-meetup\node_modules\express\lib\router\layer.js:95:5)
    at D:\Development\simplify-meetup\node_modules\express\lib\router\index.js:281:22
    at Function.process_params (D:\Development\simplify-meetup\node_modules\express\lib\router\index.js:335:12)

Answer 1

Got it. If you have installed MySQL version 8.0.4 and higher, you will be given the option to select the latest authentication method or to support legacy authentication methods as well. If the first option is selected, MySQL server supports only caching_sha2_password. The other legacy option supports mysql_native_password.

Since, currently npm mysql client package does not support latest authentication method and me selecting latest authentication method at MySQL server, I was facing this issue.

The option screen will look as below. Post changing the option, it worked like charm.

Thanks @ruiquelhas for guiding me about the potential issue :)

Answer 2

Verify that the test user has permission to access the db; In a mysql shell run

SHOW GRANTS FOR 'test'@'localhost';

This will let you see all the databases that user has access to.