Error: Cannot enqueue Query after fatal error. Expressjs. Mysql

Question

I have my Express js connect to multiple dbs. Which works everytime I startup my app. But As soon as my connection to my database goes stale... the connection returns an error code of PROTOCOL_CONNECTION_LOST. Which is normal for a mysql when a connection is idle. My mysql server is deployed in AWS RDS which also works just fine.

The problem is, everytime my express app encounters the PROTOCOL_CONNECTION_LOST error, it should reconnect to the database, which in fact also works. BUUT when I try to make queries to my MYSQL db. It returns a Error: Cannot enqueue Query after fatal error. error. I've been dealing with this for a while, and my workaround is to restart the express app everytime. hope someone else has encountered this and could give an advice.

Here is my sample code for connecting to db:

var mysql = require('mysql');
var mysqlConn
// mysqlConn.connect();

function handleDisconnect() {
    mysqlConn = mysql.createConnection({
        host: 'aws_instance***',
        user: '******',
        password: '*****',
        database: 'my_db',
        multipleStatements: true
    });

    mysqlConn.connect(function (err) {
        if (err) {
            console.log('ERROR CONNECT admin:', err.code + '--' + err.address);
            setTimeout(handleDisconnect, 2000);
        } else {
            console.log('Connected to DB')
        }
    });

    mysqlConn.on('error', function (err) {
        console.log('ERROR admin', err.code + '--' + err.address);
        if (err.code === 'PROTOCOL_CONNECTION_LOST') {   // Connection to the MySQL server is usually
            console.log("Connection to db lost!")
            handleDisconnect();                         // lost due to either server restart, or a
        } else {    
            console.log(err)                                  // connnection idle timeout (the wait_timeout
            throw err;                                  // server variable configures this)
        }
    });

}

handleDisconnect();
module.exports = {
    mysqlConn: mysqlConn,
};

Then here is my output logs as shown in my server logs.

ERROR db PROTOCOL_CONNECTION_LOST--undefined
Connection to db lost!
Connected to db
OPTIONS /verify-token/ 200 0.285 ms - 4
Error: Cannot enqueue Query after fatal error.
POST /verify-token/ 500 1.332 ms - 95
OPTIONS /auth/login 200 0.793 ms - 4
Error: Cannot enqueue Query after fatal error.
POST /login 500 1.564 ms - 58
OPTIONS /login 200 0.687 ms - 4
Error: Cannot enqueue Query after fatal error.
POST /login 500 1.467 ms - 58

kungphu · Accepted Answer · 2018-10-15 04:17:24Z

6

+50

While there are workarounds, they apparently don't work for everyone. The suggestion in the documentation is to use connection pooling instead of manually managing individual connections.

answered Oct 15, 2018 at 4:17

kungphu

4,8513 gold badges30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vincent Pakson Over a year ago

I might use connection pooling instead. I haven't tried it yet, cause it takes a day for me to test this, cause i have to wait for PROTOCOL_CONNECTION_LOST to take effect and I don't want to modify my db knowing that other functionalities of the website we're developing is using it. Thanks though.

kungphu Over a year ago

Assuming you have or can set up a dev environment, I'd try setting up a temporary endpoint that triggers this error, possibly by messing with the connection object (just closing it might work unless that results in a different error). Really, though, connection pooling is probably a better option than this anyway. If there's no dev environment... farewell and adieu to you fair Spanish ladies...

Fattie Over a year ago

on ubuntu, to simulate a disconnect, sudo service mysql restart

Collectives™ on Stack Overflow

Error: Cannot enqueue Query after fatal error. Expressjs. Mysql

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related