Mysql: {"code":"PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR","fatal":false}

[BangaruBabu]

Hello node-mysql Team,

We are struggling with the connection pooling timeout issue. Currently we are using node-mysql npm to get data from Remote MySQL and expose same data as JSON format . First 2 minutes we are getting the expected data from API ,after that we are receiving error as below.

{"code":"PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR","fatal":false}

In our case our application instance is in once server and MySQL DB is in another server

Here is the code snippet that we have

/*-------------------------------------Rest.js------------------------------------*/
 router.get("/articles",function(req,res){
        //getArticleTitles()
        var query = "select vid as articleid ,title as articletitle from node where type='article' and status=1 order by created desc";
        connection.connect();
        connection.query(query,function(err,rows){

            if(err) {

                console.log(JSON.stringify(err));

                res.json({"Error" : 1, "Message" : "Error while getting the data from Remote DataBase motherofall.org"});

            } else {
                res.json({"Error" : 0, "Message" : "Success", "Articles" : rows});
            }
        });
        connection.release();
    });

/*-------------------------------------Server.js------------------------------------*/
REST.prototype.connectMysql = function() {
    var self = this;
    var pool      =    mysql.createPool({
        connectionLimit : 100,
        waitForConnections : true,
        queueLimit :0,
        host     : 'myremotehost',
        user     : '',
        password : '',
        database : 'mother51_moastage',
        debug    :  true,
        wait_timeout : 28800,
        connect_timeout :10
    });
    pool.getConnection(function(err,connection){
        if(err) {
          self.stop(err);
        } else {
          self.configureExpress(connection);
        }
    });
}

Hope , someone already faced it , can any one Help on this . Thanks In Advance

Cheers
BangaruBabu

[BangaruBabu]

Find error screen

[dougwilson]

Hi! The issue is that you are only using a single connection for the entire life of your application, and once the connection has a fatal error (like the connection was closed by your MySQL server due to idling), the connection will be destroyed.

Calling connection.connect(); does _not_ re-establish a killed connection, only connect a never-connected connection.

Due to incomplete provided code, I cannot show the full refactoring your need to do to get your app to properly use the pool, but I hope just altering your provided code helps:

/*-------------------------------------Server.js------------------------------------*/
REST.prototype.connectMysql = function() {
    var self = this;
    var pool      =    mysql.createPool({
        connectionLimit : 100,
        waitForConnections : true,
        queueLimit :0,
        host     : 'myremotehost',
        user     : '',
        password : '',
        database : 'mother51_moastage',
        debug    :  true,
        wait_timeout : 28800,
        connect_timeout :10
    });
    self.configureExpress(pool);
}

/*-------------------------------------Rest.js------------------------------------*/
 router.get("/articles",function(req,res){
        //getArticleTitles()
        var query = "select vid as articleid ,title as articletitle from node where type='article' and status=1 order by created desc";
        pool.query(query,function(err,rows){

            if(err) {

                console.log(JSON.stringify(err));

                res.json({"Error" : 1, "Message" : "Error while getting the data from Remote DataBase motherofall.org"});

            } else {
                res.json({"Error" : 0, "Message" : "Success", "Articles" : rows});
            }
        });
    });

[luochen1990]

@dougwilson Hi, I used the configuration you provided, but still gotting the PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR error accidentally, what should I do to keep my service running without this fatal ?

DB = {
    host: '10.120.xxx.xxx'
    port: 3306
    user: 'xxx'
    password: 'xxx'
    timezone: '+0800'
    connectionLimit: 10 # connection number at a same time
    connectTimeout: 10000
    waitForConnections: true # enqueue query when no connection available
    queueLimit: 0 # unlimit queue size
}

mysql.createPool(DB).getConnection (err, db) ->
    if err?
        log.err err
        return
    else
        log "DB Connected"

    db.query "XXX" # using "db"

[dougwilson]

Hi @luochen1990, that error is because you have issues within your own code, and you are making an invalid call to our library. The only real way I can help you at this point is that you need to distill the issue down such that I have the complete code of your entire application. This error cannot be solved if I'm just getting small pieces of your application little by little, I would have to see the entire application, provide me with the instructions to get it up and running, provide the instructions on how to encounter the error and then I can attach a debugger to your app to determine where in your code the mistake is.

The mistake boils down to the following:

1. Your MySQL connection is encountering a fatal error.
2. Your code is ignoring this error and trying to make another query on the same connection.

[luochen1990]

@dougwilson Thank you for your reply! As you mentioned above, It is possible for my code that the connection is used to send query after it is fatal, since I wrote my code as connect-once-use-many-times style:

mysql.createPool(DB).getConnection (err, db) ->
    if err?
        log.err err
        return
    else
        log "DB Connected"

    db.config.queryFormat = (query, values) ->
        return query if not values?
        return query.replace /\:(\w+)/g, (txt, key) =>
            if values.hasOwnProperty(key)
                return this.escape(values[key])
            return txt

    db.query 'use `youdata_log`', (err) -> log.err err if err?

    amqp.connect(MQ.addr).then (mq) ->
        mq.createChannel().then (ch) ->
            ch.assertQueue(MQ.name, MQ.assertion)

            log "MQ Connected"

            ch.consume MQ.name, (mq_message) ->
                if mq_message != null
                    rawMsg = mq_message.content.toString()

                    msg = parseMsg(rawMsg)
                    db.query "something about msg"

As you can see, I connect the db once, and every time I got a message from the MQ, I use the connection I got before to execute a query. If there is an connection error during the time between I get the connection and I execute a query, then the error may occur.

So, what should I write to make the auto-reconnect possible?

[dougwilson]

So, what should I write to make the auto-reconnect possible?

Yea, that code would definitely exhibit this issue, since you don't stop using the same db connection even after a fatal connection occurs. Perhaps a better pattern for your use here is to use a connection pool (even if you want to have the connection pool set to a maximum of one connection). Doing this will mean that each time a message comes in, you're asking the pool for a valid connection and it will make sure you get one, otherwise you have to re-implement much of what the pool code is doing in your code.

A simple example (I don't write CoffeeScript, so please forgive any syntax errors I made when trying to alter your example above):

var pool = mysql.createPool(DB)

pool.on 'connection', (db) ->
    db.config.queryFormat = (query, values) ->
        return query if not values?
        return query.replace /\:(\w+)/g, (txt, key) =>
            if values.hasOwnProperty(key)
                return this.escape(values[key])
            return txt

    db.query 'use `youdata_log`', (err) -> log.err err if err?

amqp.connect(MQ.addr).then (mq) ->
    mq.createChannel().then (ch) ->
        ch.assertQueue(MQ.name, MQ.assertion)

        log "MQ Connected"

        ch.consume MQ.name, (mq_message) ->
            if mq_message != null
                rawMsg = mq_message.content.toString()

                msg = parseMsg(rawMsg)
                pool.query "something about msg"

[luochen1990]

@dougwilson thank you very much! I tried this and it works well.

[dougwilson]

@luochen1990, glad to hear it! Please let me know if you have other questions, are still having problems, etc.

[trkaplan]

I was having the same issue, as suggested here, passing the connection pool rather than the connection to self.configureExpress() fixed the pool timeout problem.