Node.js app with a MySQL database deployed on heroku keeps crashing

Question

I am new to Node.js and I have an application that I have deployed to Heroku with a MySQL database.

However,I keep getting an error. The app works only for a time after I deploy on Heroku. It suddenly stops working and I get the following error:

   events.js:160
               throw er; // Unhandled 'error' event

   Error: Connection lost: The server closed the connection.
   at Protocol.end (/app/node_modules/mysql/lib/protocol/Protocol.js:113:13)
   at Socket.<anonymous> (/app/node_modules/mysql/lib/Connection.js:109:28)
   at emitNone (events.js:91:20)
   at Socket.emit (events.js:185:7)
   at endReadableNT (_stream_readable.js:974:12)
   at _combinedTickCallback (internal/process/next_tick.js:80:11)
   at process._tickCallback (internal/process/next_tick.js:104:9)
   State changed from up to crashed
   Process exited with status 1

Here is my code:

  var express = require('express');
  var app = express();
  var mysql = require('mysql')
  var bodyParser = require('body-parser');
  const sortBy = require('sort-array')


  app.set('port', (process.env.PORT || 5000));
  app.set("view engine", "ejs");
  app.use(bodyParser.urlencoded({extended: true}));
  app.use(express.static(__dirname + '/public'));



  var db_config = {
  host: 'us-cdbr-iron-east-05.cleardb.net',
  user: 'REDACTED',
  password: 'REDACTED',
  database: 'heroku_REDACTED'
  };

 var connection;
 connection = mysql.createConnection(db_config); 

 var count2 = 10;
 var count3 = 20;
 var count4 = 10;
 //SELECT COUNT(*) AS count2 FROM photos

 app.get("/", function(req, res){
 var q_users = "SELECT COUNT(*) AS count1 FROM users";

 connection.query(q_users, function(err,results, fields){
 if(err) throw err;
 var count1 = results[0].count1;
 //res.send(results);
 res.render("home", {data1: count1, data2: count2, data3: count3, 
  data4: count4});
   });
  });



  app.post('/adduser', function(req,res){
  var person = { username: req.body.username};

   connection.query('INSERT INTO users SET ?', person, function(err, 
   result){
    if(err) throw err;
    res.redirect("/");

    });
    });




    app.post('/deleteuserid', function(req,res){
     var id = req.body.userid;

     connection.query('DELETE FROM users WHERE id = ?', id, function(err, result){
      if(err) throw err;
      //res.send(result);
      res.redirect("/countpictures");

      });
    });


  app.listen(app.get('port'), function() {
  console.log('Node app is running on port', app.get('port'));
  });

Can someone please tell me how to fix this? I have a lot of SQL queries and I think some form of timeout is occuring. Please tell me what changes should I make to avoid this error, so that my app is stable and doesnt crash after every 5 minutes of deployment.

No security credentials, please. Change that password **now.** — O. Jones, Sep 30 '17 at 17:22

score 0 · Answer 1 · answered Sep 30 '17 at 17:22

Unfortunately, MySQL will eventually close the connection after it's been idle for too long...

To avoid this, you need to use a connection pool. Connection pools manage connections for you, reconnecting when necessary. For more details on connection pooling, see https://github.com/mysqljs/mysql#pooling-connections.

Hence, you can make your code more robust by replacing the connection with the following:

const connection = mysql.createPool({ connectionLimit: 5, ...db_config });

Ideally, you'd also rename the connection variable to pool as well ;)

score 0 · Answer 2 · edited Apr 22 '21 at 11:13

0

I had the same error, and I solved it like this

var mysql = require ('mysql');
var pool = mysql.createPool ({
  host: 'xxxx',
  user: 'xxxx',
  password: 'xxxx',
  database: 'xxxx'
});
module.exports = pool;

edited Apr 22 '21 at 11:13

Syscall

19,327
10
37
52

answered Apr 22 '21 at 11:08

user15737667

1

where to put the code ? in server.js ? because my config js has the pool ```production: { username: process.env.PROD_DB_USERNAME, password: process.env.PROD_DB_PASSWORD, ..... pool: { max: 5, min: 0, acquire: 50000, idle: 20000 } }``` – Yogi Arif Widodo Aug 09 '22 at 14:16

Node.js app with a MySQL database deployed on heroku keeps crashing

2 Answers2