Can't add new command when connection is in closed state

Question

I am using the Amazon EC2 with Ubuntu.

I am running the server using the PM2, as

pm2 start bin/www --log-date-format "YYYY-MM-DD HH:mm" --watch

It works fine but when I am trying to access API after 1 or 2 days. I will get the error:

0|www      | 2017-11-29 08:30: name error  { Error: Can't add new command when connection is in closed state
0|www      |     at Connection._addCommandClosedState (/var/www/html/perb_nodejs/node_modules/mysql2/lib/connection.js:158:13)
0|www      |     at Connection.query (/var/www/html/perb_nodejs/node_modules/mysql2/lib/connection.js:621:15)
0|www      |     at Object.loginTrainer (/var/www/html/perb_nodejs/models/trainer.js:49:29)
0|www      |     at /var/www/html/perb_nodejs/routes/v1/trainer.js:50:16
0|www      |     at Layer.handle [as handle_request] (/var/www/html/perb_nodejs/node_modules/express/lib/router/layer.js:95:5)
0|www      |     at next (/var/www/html/perb_nodejs/node_modules/express/lib/router/route.js:137:13)
0|www      |     at Route.dispatch (/var/www/html/perb_nodejs/node_modules/express/lib/router/route.js:112:3)
0|www      |     at Layer.handle [as handle_request] (/var/www/html/perb_nodejs/node_modules/express/lib/router/layer.js:95:5)
0|www      |     at /var/www/html/perb_nodejs/node_modules/express/lib/router/index.js:281:22
0|www      |     at Function.process_params (/var/www/html/perb_nodejs/node_modules/express/lib/router/index.js:335:12) fatal: true }

And then I have to restart the PM2 instance.

I understand that because of closed connection, I am facing this issue. But why this is happening when I am using PM2. I think PM2 is supposed to do the same job.

package.json

{
  "name": "",
  "version": "0.0.0",
  "private": true,
  "scripts": {
    "start": "node ./bin/www"
  },
  "dependencies": {
    "body-parser": "~1.18.2",
    "cookie-parser": "~1.4.3",
    "cors": "2.8.4",
    "debug": "~2.6.9",
    "express": "~4.15.5",
    "jade": "~1.11.0",
    "morgan": "~1.9.0",
    "multer": "1.3.0",
    "mysql2": "1.5.0",
    "path": "0.12.7",
    "request": "2.83.0",
    "serve-favicon": "~2.4.5"
  }
}

Edit: I think it is not because of the server but DB connection is closed.

Any help will be appreciated.

Answer 1

I had the same problem. While investigating the source code I realized that in some places we were holding on to the pool database connection. So I change the code so that we always release the connection after each call, instead of passing the connection from one function to an other.

let connection = await pool.getConnection();
try {
    // Run one query 
} finally {
    connection.release();
}

In this way the pool will always return a valid opened connection.

Of course, this will work only for requests. You will still need to hold on to the connection when executing a transaction, but do so only in those cases.

Answer 2

I had the same issue with pooled connections and 8.0.15 server and to make things worse I had also long running connections. For now I have a cautious optimism that the issue is resolved. Some changes must be done in your logic:

1. If you need long running connections then first check MySQL server parameter wait_timeout - it is in seconds and it will kill silent connections after the period of time, probably it will be sufficient for you just to increase this value
2. If wait_timeout is not sufficient you can use connection.ping method with some interval - it will keep the connection alive
3. Do analyse error object after operations: if it has fatal = true then the connection became useless (probably it was closed somehow or connection was lost) - you must call connection.destroy() - it will remove this poisoned connection from the pool otherwise the pool can provide this connection again – probably some change should be implemented in the pool logic.

Answer 3

config add { "keepAliveInitialDelay": 10000, "enableKeepAlive": true, }

Answer 4

You need to listen for the error event on the connection and then open a new connection after it occurs. Here's how the overall program might look:

for (;;) {
  try {
    const conn = await pool.getConnection();
    conn.on('error', err => {
      console.log(`Error on connection: ${err.message}`);
      // stop doing stuff with conn
    });
    try {
      // do stuff with conn
    } catch (err) {
      console.log(`Error doing stuff: ${err.message}`);
    } finally {
      conn.destroy();
    }
  } catch (err) {
    console.log(`Unable to acquire connection: ${err.message}`);
  }
  // delay before trying to reacquire connection
}

Answer 5

I solved my problem by just closing the connection after 10 seconds of inactivity, then creating a new connection. The real problem is that there is no way of knowing wether or not the connection is open.

Answer 6

What I ended up having to do was put the connection request in it's own .js file - connectionRequest.js

module.exports = function () {

    let mysql = require('mysql2')
    let connCreds = require('./connectionsConfig.json');

    //Establish Connection to the DB
    let connection = mysql.createConnection({
        host: connCreds["host"],
        user: connCreds['username'],
        password: connCreds['password'],
        database: connCreds['database'],
        port: 3306
    });

    //Instantiate the connection
    connection.connect(function (err) {
        if (err) {
            console.log(`connectionRequest Failed ${err.stack}`)
        } else {
            console.log(`DB connectionRequest Successful ${connection.threadId}`)
        }
    });

    //return connection object
    return connection
}

Once I did that I was able to import it into my query on the controller file like so

let connectionRequest = require('../config/connectionRequest')

controllerMethod: (req, res, next) => {
    //Establish the connection on this request
    connection = connectionRequest()

    //Run the query
    connection.query("SELECT * FROM table", function (err, result, fields) {
        if (err) {
            // If an error occurred, send a generic server failure
            console.log(`not successful! ${err}`)
            connection.destroy();

        } else {
            //If successful, inform as such
            console.log(`Query was successful, ${result}`)

            //send json file to end user if using an API
            res.json(result)

            //destroy the connection thread
            connection.destroy();
        }
    });
},

Answer 7

I was having this issue using TypeORM because I was using connection.getRepository() directly inside of a transaction that was being managed by a query runner. It didn't seem to like that, queryRunner.manager.getRepository() works for me and is what I was intending by doing it as a transaction in the first place.

Didn't work:

const queryRunner = connection.createQueryRunner();
await queryRunner.connect();
const bundleRepository = connection.getRepository(BundleEntity);

Did work:

const queryRunner = connection.createQueryRunner();
await queryRunner.connect();
const bundleRepository = queryRunner.manager.getRepository(BundleEntity);

Answer 8

In my experience I noticed this issue occuring in certain node versions. In my case my dev was 14.17 while the prod was 16.x

the prod logs showed that error...once I changed the prod ver it was fixed.