Mysql: Unable to log the fatal error, Cannot enqueue Query after fatal error

[sindhushaballa]

The following error is happening when I make 5 requests / second to one of our servers.

StackTrace : Error: Cannot enqueue Query after fatal error.
    at Protocol._validateEnqueue (/home/surendra/node-sample/node_modules/mysql/lib/protocol/Protocol.js:199:16)
    at Protocol._enqueue (/home/surendra/node-sample/node_modules/mysql/lib/protocol/Protocol.js:135:13)
    at PoolConnection.query (/home/surendra/node-sample/node_modules/mysql/lib/Connection.js:214:25)
    at runQueryWithClient (/home/surendra/node-sample/app/lib/db-connector/mysql.js:275:17)
    at runQuery (/home/surendra/node-sample/app/lib/db-connector/mysql.js:250:5)
    at MySQL.query (/home/surendra/node-sample/app/lib/db-connector/mysql.js:58:3)
    at runQuery (/node-sample/app/lib/model.js:406:15)
Message : Cannot enqueue Query after fatal error. 

We are able to log this error, but we are not able to catch what the fatal error is? We are using pooling for reusing the existing connections and creating new connections.

I tried the following to trace the fatal error but no use.

1. Kept console.trace() before each err.fatal=true in node-mysql module source code.
2. Added following lines of code to debug why the fatal error is happening,

var del = connection._protocol._delegateError;
connection._protocol._delegateError = function(err, sequence){
      if (err.fatal) {
        console.trace('fatal error: ' + err.message);
      }
      return del.call(this, err, sequence);
};

Can somebody help on solving this error?

Thanks

Surendra

[sindhushaballa]

Added following lines of code to debug why the fatal error is happening,

var del = connection._protocol._delegateError;
connection._protocol._delegateError = function(err, sequence){
      if (err.fatal) {
        console.trace('fatal error: ' + err.message);
      }
      return del.call(this, err, sequence);
};

I got to know that the fatal error is,

Trace: fatal error: Connection lost: The server closed the connection.
    at Protocol.connection._protocol._delegateError (/home/surendra/node-simple/app/lib/db-connector/mysql.js:256:19)
    at Protocol.connection._protocol._delegateError (/home/surendra/node-simple/app/lib/db-connector/mysql.js:258:20)
    at Protocol.connection._protocol._delegateError (/home/surendra/node-simple/app/lib/db-connector/mysql.js:258:20)
    at Protocol.connection._protocol._delegateError (/home/surendra/node-simple/app/lib/db-connector/mysql.js:258:20)
    at Protocol.connection._protocol._delegateError (/home/surendra/node-simple/app/lib/db-connector/mysql.js:258:20)
    at Protocol.connection._protocol._delegateError (/home/surendra/node-simple/app/lib/db-connector/mysql.js:120:18)
    at Protocol.end (/home/surendra/node-simple/node_modules/mysql/lib/protocol/Protocol.js:113:8)
    at Socket.<anonymous> (/home/surendra/node-simple/node_modules/mysql/lib/Connection.js:115:28)
    at emitNone (events.js:72:20)
    at Socket.emit (events.js:166:7)

But, I'm handling error at the following places,

1. Getting the connection from the pool.
2. Running the query

Where am I'm missing to log this error?

[nirmalgoswami]

same here but i am getting some time i am also trying to replicate issue,do you know how to replicate issue ?
as you know we can solve issue but for that we need to replicate issue but i am not able to replicate issue every time.

[LM1LC3N7]

I think that's because the connection was closed by the server. After that, you cannot use the connection variable anymore.
You must recreate a new one, in order to continue to do queries.

Here, what I use to create a connection, and handle some errors:

//- MYSQL Module
try{
    var mysql_npm = require('../node_modules/mysql');
}catch(err){
    console.log("Cannot find `mysql` module. Is it installed ? Try `npm install mysql` or `npm install`.");
}

//- Connection configuration
var db_config = {
    host         : 'localhost',
    user         : 'app',
    password     : 'super secret password',
    database     : 'app'
};

//- Create the connection variable
var connection = mysql_npm.createConnection(db_config);

//- Establish a new connection
connection.connect(function(err){
    if(err) {
        // mysqlErrorHandling(connection, err);
        console.log("\n\t *** Cannot establish a connection with the database. ***");

        connection = reconnect(connection);
    }else {
        console.log("\n\t *** New connection established with the database. ***")
    }
});

//- Reconnection function
function reconnect(connection){
    console.log("\n New connection tentative...");

    //- Destroy the current connection variable
    if(connection) connection.destroy();

    //- Create a new one
    var connection = mysql_npm.createConnection(db_config);

    //- Try to reconnect
    connection.connect(function(err){
        if(err) {
            //- Try to connect every 2 seconds.
            setTimeout(reconnect, 2000);
        }else {
            console.log("\n\t *** New connection established with the database. ***")
            return connection;
        }
    });
}

//- Error listener
connection.on('error', function(err) {

    //- The server close the connection.
    if(err.code === "PROTOCOL_CONNECTION_LOST"){    
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        connection = reconnect(connection);
    }

    //- Connection in closing
    else if(err.code === "PROTOCOL_ENQUEUE_AFTER_QUIT"){
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        connection = reconnect(connection);
    }

    //- Fatal error : connection variable must be recreated
    else if(err.code === "PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR"){
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        connection = reconnect(connection);
    }

    //- Error because a connection is already being established
    else if(err.code === "PROTOCOL_ENQUEUE_HANDSHAKE_TWICE"){
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
    }

    //- Anything else
    else{
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        connection = reconnect(connection);
    }

});

[nirmalgoswami]

@bsurendrakumar did you solved issue ? can i know how did you solved it ? i think @flexbrane approach is perfect . i am trying to solve issue using @flexbrane approach .

[sindhushaballa]

@nirmalgoswami I'm not able to solve this issue. As we are in hurry to finish the project in less time, we shifted to node-mariasql module.

[LM1LC3N7]

Actually, this was not the right answer. Here, the correct code:

//- MYSQL Module
try{
    var mysql_npm = require('../node_modules/mysql');
}catch(err){
    console.log("Cannot find `mysql` module. Is it installed ? Try `npm install mysql` or `npm install`.");
}

//-
//- Connection configuration
//-
var db_config = {
    host         : 'localhost',
    user         : 'app',
    password     : 'super secret',
    database     : 'app'
};


//-
//- Create the connection variable
//-
var connection = mysql_npm.createPool(db_config);


//-
//- Establish a new connection
//-
connection.getConnection(function(err){
    if(err) {
        // mysqlErrorHandling(connection, err);
        console.log("\n\t *** Cannot establish a connection with the database. ***");

        connection = reconnect(connection);
    }else {
        console.log("\n\t *** New connection established with the database. ***")
    }
});


//-
//- Reconnection function
//-
function reconnect(connection){
    console.log("\n New connection tentative...");

    //- Create a new one
    connection = mysql_npm.createPool(db_config);

    //- Try to reconnect
    connection.getConnection(function(err){
        if(err) {
            //- Try to connect every 2 seconds.
            setTimeout(reconnect(connection), 2000);
        }else {
            console.log("\n\t *** New connection established with the database. ***")
            return connection;
        }
    });
}


//-
//- Error listener
//-
connection.on('error', function(err) {

    //-
    //- The server close the connection.
    //-
    if(err.code === "PROTOCOL_CONNECTION_LOST"){    
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        return reconnect(connection);
    }

    else if(err.code === "PROTOCOL_ENQUEUE_AFTER_QUIT"){
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        return reconnect(connection);
    }

    else if(err.code === "PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR"){
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        return reconnect(connection);
    }

    else if(err.code === "PROTOCOL_ENQUEUE_HANDSHAKE_TWICE"){
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
    }

    else{
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        return reconnect(connection);
    }

});


//-
//- Export
//-
module.exports = connection;

[nirmalgoswami]

Thanks for your reply

[LM1LC3N7]

No problem, for once I can help!

[dougwilson]

Hi @bsurendrakumar , can you share the full code you are using? How often are you getting the connection from the pool? Only once, every time you make a query? Sometime in between? Also, you should be able to see the error if you attach an error event listener on the connection object you get from the pool.

[nirmalgoswami]

Hello @dougwilson thanks for focus on this issue. except this issue your work is awesome related to this plugin. I have read most of comment over internet related to this issue and i know that this error come when we are using one connection all over project. I know solutions is either use new connection before every query(but it will take lots of code change in existing stable project so i am against it ) or use @flexbrane 's approach. can you please do some comment on @flexbrane 's approach ? it will be good to choose solution to solve this issue.

[dougwilson]

Hi @nirmalgoswami, this issue has of course been discussed many times. The only thing that is just an issue is just how folks are using this module, many times it's from copy-and-paste from sources outside of this project, which is a hard thing to police, so it has dubious opinions.

The problem is that once your connection is closed, _you have to get a new connection_. There really isn't any other answer. Why is that the answer? Well, there are many operations you do in MySQL that _you need the guarantee that you are using the same connection_. For example, if you setup a session variable in one query, and use it in the next, you need to be sure you are using the same connection. Transactions are the main issue around this: if you BEGIN TRANSACTION on a connection, you are expecting that all your next queries until COMMIT or ROLLBACK are actually part of that transaction. If you are silently reconnecting, then that goes out the window, unfortunately, and you end up with really, really weird bugs.

Now, that's not to say that there are people who are just not using transactions, session state, functions like LAST_INSERT_ID(), etc., and for them, your solution works.

BUT--if someone is working fine with your solution, I suggest simply using pool.query all over your application and passing the pool object around.

[dfa1234]

It's 2018 now but we still have to do this so, I somehow fixed and reproduced a similar code behavior, but in typescript.

import * as mysql from "mysql";
import {MysqlError, Pool, PoolConnection} from "mysql";
import {dbMysqlConf} from "./config";

const MAX_ATTEMPS = 10;
let attempts = 0;

export const managePoolConnection = (app:any) => new Promise((resolve: (status:string) => void, reject: (status:string) => void) => {

    let pool: Pool = mysql.createPool(dbMysqlConf);

    pool.getConnection((err: MysqlError, connection: PoolConnection) => {
        if (err) {
            attempts++;
            if (attempts > MAX_ATTEMPS) {
                reject(`Mysql Error: More than ${MAX_ATTEMPS}, cancelling. Error ${err.errno} :  ${err.sqlMessage}`)
            } else {
                setTimeout(() => {
                    managePoolConnection(app).then(resolve, reject)
                }, 2000);
            }
            return;
        } else {
            attempts = 0;

            (app as any).poolConnection = connection;

            connection.on('error', (err: MysqlError) => {

                console.log(`Mysql Error: Cannot establish a connection with the database.  (${err.code})`);

                switch (err.code) {
                    case "PROTOCOL_CONNECTION_LOST":
                        managePoolConnection(app).then(resolve, reject);
                        break;
                    case "PROTOCOL_ENQUEUE_AFTER_QUIT":
                        managePoolConnection(app).then(resolve, reject);
                        break;
                    case "PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR":
                        managePoolConnection(app).then(resolve, reject);
                        break;
                    case "PROTOCOL_ENQUEUE_HANDSHAKE_TWICE":
                        //managePoolConnection.then(resolve, reject);
                        break;
                    default:
                        managePoolConnection(app).then(resolve, reject);
                        break;
                }
            });

            resolve(`Mysql: connection successfull`);
        }

    });

});

Then in the index.ts (app is the express server but could be something else)

managePoolConnection(app).then(
    status => console.log(status),
    err => console.log(err)
);