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.

Solution 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.

Solution 2

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
}

Solution 3

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.