Node + Express application returns 'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR'?

Question

I'm trying to build an application with Node and Express, it worked well yesterday until today I got this error:

{
  code: "PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR",
  fatal: false
}

Google says I should use createPool rather than createConnection, but I'm not sure how to do it in my case. Here is my code:

const express = require('express');
const cors = require('cors');
const mysql = require('mysql');
const app = express();

const SELECT_ALL_USERS_QUERY = 'SELECT * FROM `mySchema`.`myTable`;';
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'abcdefg',
    database: 'mySchema'
});

connection.connect(err => {
    if (err) {
        return err
    }
});

app.use(cors());

app.get('/', (req, res) => {
    res.send('go to /mySchema to see contents')
});

app.get('/myTable', (req, res) => {
    connection.query(SELECT_ALL_USERS_QUERY, (err, results) => {
        if (err) {
            return res.send(err)
        }
        else {
            return res.json({
                data: results
            })
        }
    })
});

app.listen(4000, () => {
    console.log('MySchema SQL server listening on PORT 4000');
});

Answer 1

MY SOLUTION: STEP 1) I had to check authenticy of MYSQL. go inside your mysql. In my case it was (I already gave privileged to my user John):

mysql -u john -p

Then write this in mysql:

SELECT user, host, authentication_string, plugin FROM mysql.user WHERE user = 'your_username';

Example:

SELECT user, host, authentication_string, plugin FROM mysql.user WHERE user = 'john';

STEP 2) Then write this:

ALTER USER 'your_username'@'your_host' IDENTIFIED WITH 'mysql_native_password' BY 'your_password';

Example:

ALTER USER 'john'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'StrongPassword##';

STEP 3) Finally write:

FLUSH PRIVILEGES;

Now try to run, hope it will work. Make sure to check your logs. I was using a ubuntu droplet in cloud and using pm2 for running the express.js app. From my pm2 logs:

User | Error connecting to MySQL: Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client.....

and the response error was same: 'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR'

So after researching I found that: My authentication method is set to caching_sha2_password, So I need to change it to mysql_native_password, which is more commonly supported by MySQL client libraries.

Answer 2

To help other people, thought I'd post the complete solution which worked for me:

const express = require('express');
const cors = require('cors');
const mysql = require('mysql');
const app = express();

app.use(cors());

app.get('/', (req, res) => {
    res.send('go to /mySchema to see contents')
});

const SELECT_ALL_USERS_QUERY = 'SELECT * FROM `mySchema`.`myTable`;';

const pool = mysql.createPool({
    connectionLimit: 10,
    host: 'localhost',
    user: 'root',
    password: 'abcdefg',
    database: 'mySchema',
    debug: false
});

pool.getConnection((err, connection) => {
    if (err) throw err;
    app.get('/myTable', (req, res) => {
        connection.query(SELECT_ALL_USERS_QUERY, (err, results) => {
            if (err) {
                return res.send(err)
            }
            else {
                return res.json({
                    data: results
                })
            };
        });
        console.log(connection);
    });

});


app.listen(4000, () => {
    console.log('MySchema SQL server listening on PORT 4000');
});

Answer 3

var mysql = require('mysql');
var pool  = mysql.createPool({
    connectionLimit : 10,
    host: 'localhost',
    user: 'root',
    password: 'abcdefg',
    database: 'mySchema'
});

// for an example.....
pool.getConnection(function(err, connection) {
      if (err) throw err; // not connected!

app.get('/myTable', (req, res) => {
  // Use the connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // When done with the connection, release it.
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});
});

If you would like to close the connection and remove it from the pool, use connection.destroy() instead. The pool will create a new connection the next time one is needed.

Connections are lazily created by the pool. If you configure the pool to allow up to 100 connections, but only ever use 5 simultaneously, only 5 connections will be made. Connections are also cycled round-robin style, with connections being taken from the top of the pool and returning to the bottom.

When a previous connection is retrieved from the pool, a ping packet is sent to the server to check if the connection is still good.

you can follow this link: https://github.com/mysqljs/mysql#pooling-connections