Heroku and NodeJs - MySql Connection lost: The server closed the connection

Question

I have a very simple nodeJS app connection to a MySql ClearDB. The first time running it has no problem connection to the DB and executing a simple SELECT but after some minutes the server crash it with a DB fail connection. Any idea why what is wrong?

ERROR: Connection lost: The server closed the connection.

here is my code:

web.js

var express = require("express");
var mysql      = require('mysql');
var app = express();
app.use(express.logger());

var connection = mysql.createConnection({
  host     : 'us-cdbr-east-04.cleardb.com',
  user     : 'b6#####',
  password : 'b3f####',
  database : 'heroku_1d########5291'
});

connection.connect();

app.get('/', function(request, response) {
  connection.query('SELECT * from t_users', function(err, rows, fields) {
      if (err) {
        console.log('error: ', err);
        throw err;
      }
      response.send(['Hello World!!!! HOLA MUNDO!!!!', rows]);
    });
});

var port = process.env.PORT || 5000;
app.listen(port, function() {
  console.log("Listening on " + port);
});

Procfile

web: node web.js

package.json

{
  "name": "node-example",
  "version": "0.0.1",
  "dependencies": {
    "express": "3.1.x"
    , "mysql": ""
  },
  "engines": {
    "node": "0.10.x",
    "npm": "1.2.x"
  }
}

LOG

Aug 25 12:31:30 polar-beach-8042 heroku/slug-compiler:  Slug compilation finished 
Aug 25 12:31:30 polar-beach-8042 heroku/web.1:  Stopping all processes with SIGTERM 
Aug 25 12:31:33 polar-beach-8042 heroku/web.1:  Process exited with status 143 
Aug 25 12:31:51 polar-beach-8042 app/web.1:  - - - [Sun, 25 Aug 2013 19:31:51 GMT] "GET / HTTP/1.1" 200 131 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/28.0.1500.95 Safari/537.36" 
Aug 25 12:31:51 polar-beach-8042 app/web.1:  - - - [Sun, 25 Aug 2013 19:31:51 GMT] "GET /favicon.ico HTTP/1.1" 404 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/28.0.1500.95 Safari/537.36" 
Aug 25 12:31:51 polar-beach-8042 heroku/router:  at=info method=GET path=/favicon.ico host=polar-beach-8042.herokuapp.com fwd="67.86.230.11" dyno=web.1 connect=2ms service=5ms status=404 bytes=34 
Aug 25 12:31:52 polar-beach-8042 heroku/router:  at=info method=GET path=/ host=polar-beach-8042.herokuapp.com fwd="67.86.230.11" dyno=web.1 connect=2ms service=7ms status=200 bytes=131 
Aug 25 12:32:51 polar-beach-8042 app/web.1:  Error: Connection lost: The server closed the connection. 
Aug 25 12:32:51 polar-beach-8042 app/web.1:      at Protocol.end (/app/node_modules/mysql/lib/protocol/Protocol.js:73:13) 
Aug 25 12:32:51 polar-beach-8042 app/web.1:      at Socket.onend (stream.js:79:10) 
Aug 25 12:32:51 polar-beach-8042 app/web.1:      at Socket.EventEmitter.emit (events.js:117:20) 
Aug 25 12:32:51 polar-beach-8042 app/web.1:      at _stream_readable.js:910:16 
Aug 25 12:32:51 polar-beach-8042 app/web.1:      at process._tickCallback (node.js:415:13) 
Aug 25 12:32:52 polar-beach-8042 heroku/web.1:  Process exited with status 8 
Aug 25 12:32:52 polar-beach-8042 heroku/web.1:  State changed from up to crashed 

THANKS!!!!!!!!

Answer 1

This is because the MySQL server on ClearDB closed the connection. You can detect the disconnect event, then recreate the connection.

More info here: https://bezkoder.com/deploy-node-js-app-heroku-cleardb-mysql/

const mysql = require("mysql");    
const dbConfig = require("../config/db.config.js");
    
var connection = mysql.createPool({
  host: dbConfig.HOST,
  user: dbConfig.USER,
  password: dbConfig.PASSWORD,
  database: dbConfig.DB
});

module.exports = connection;

Answer 2

You already seem to know what is happening; the MySQL server is closing the connection. Many database services, including ClearDB, to do this (ie, close inactive connections). You're going to have to detect the disconnect event, and recreate the connection.

There are instructions in the node-mysql documentation for doing this.