Server NodeJs crashes in idle time and how do I fix it?

Question

My server api is on alwayse alwaysdata. After x time the server crash.

events.js:183
  throw er;
 // Unhandled 'error' eventError: Connection lost: The server closed the connection.
at Protocol.end (/home/ec2-user/node_modules/mysql/lib/protocol/Protocol.js:112:13)
at Socket.<anonymous> (/home/ec2-user/node_modules/mysql/lib/Connection.js:97:28)
at Socket.<anonymous> (/home/ec2-user/node_modules/mysql/lib/Connection.js:502:10)
at emitNone (events.js:111:20)
at Socket.emit (events.js:208:7)
at endReadableNT (_stream_readable.js:1064:12)
at _combinedTickCallback (internal/process/next_tick.js:139:11)
at process._tickCallback (internal/process/next_tick.js:181:9)

I'm looking at whether this could not be related to a mysql error. but pre-existing posts do not help me. I think the server mysql cut the connection I do not know why. here I establish the connection:

let con = mysql.createConnection({
            host: "alwaysdata.net",
            user: "user",
            password: "",
            database: "database"
        });

        try {
            con.query(check, (err, customer) => {
            if (err){
                console.log("%s Error on check query",Date());
                throw err;
            }

Did you try to simulate the scenario by terminating or closing the mysql server? — oetoni, Oct 31 '18 at 09:09
Consider posting the last 400 lines of your MySQL error log and your my.cnf OR my.ini — Wilson Hauck, Nov 01 '18 at 22:28

score 2 · Answer 1 · answered Oct 31 '18 at 11:18

try connection pool:

const mysql = require('mysql');
let pool  = mysql.createPool(
    {
        connectionLimit : 100,
        host : '172.17.0.1',
        port : 3306,
        user : 'test',
        password : 'test',
        database : 'test',
        multipleStatements: true
    }
);

...
pool.query(sql, params, function(err, rows) {
...

it works stably on my versions of mysql 5.7 and 8

oetoni · Answer 2 · 2018-10-31T09:14:12.897

I believe there are two ways you can handle this.

1) Force MySQL to keep the connection alive (not official, but I believe will do the trick).
2) Handle the mysql server disconnect from the Node's point of view.

For both there is an excellent example here.

Server disconnects

You may lose the connection to a MySQL server due to network problems, the server timing you out, the server being restarted, or crashing. All of these events are considered fatal errors, and will have the err.code = 'PROTOCOL_CONNECTION_LOST'. See the Error Handling section for more information.

Re-connecting a connection is done by establishing a new connection. Once terminated, an existing connection object cannot be re-connected by design.

With Pool, disconnected connections will be removed from the pool freeing up space for a new connection to be created on the next getConnection call.

manish kumar · Answer 3 · 2018-11-04T05:05:41.400

let connection=null;
function handleDisconnect() {
    connection = mysql.createConnection({
        host: "alwaysdata.net",
        user: "user",
        password: "",
        database: "database"
    }); // Recreate the connection, since
    // the old one cannot be reused.

    connection.connect(function (err) { // The server is either down
        if (err) { // or restarting (takes a while sometimes).
            console.log('error when connecting to db:', err);
            setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect,
        } // to avoid a hot loop, and to allow our node script to
    }); // process asynchronous requests in the meantime.
    // If you're also serving http, display a 503 error.
    connection.on('error', function (err) {
        console.log('db error', err);
        if (err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
            handleDisconnect(); // lost due to either server restart, or a
        } else { // connnection idle timeout (the wait_timeout
            throw err; // server variable configures this)
        }
    });
}
handleDisconnect();
setInterval(function () {
    connection.query('SELECT 1');
}, 5000);

module.exports = connection;

you export the connection object and use this for the other connection queries.

I have called a Query every 5sec to keep the connection alive, i have tried all other approaches and this works like a charm.

here, at each call. he will create a loop. Forcibly I will find myself with ping all the time. my function is a post — Benjamin Rocher, Nov 03 '18 at 18:02
you export the connection object and reuse the same. In that way only one object will be made and once the loop will be there. — manish kumar, Nov 04 '18 at 05:06

score 0 · Answer 4 · answered May 30 '21 at 19:50

Manish's answer worked for me!

I've been struggling with this for the past two days. I had a nodejs server with mysql db running on my localhost and after migrating to heroku with cleardb addon I came across several issues. I had this code in a config file:

const mysql = require('mysql');

const db = mysql.createConnection({
    host: 'host',
    database: 'database',
    user: 'user',
    password: 'password', });
module.exports = db;

I changed it to what Manish mentioned to handle the disconnect.

Server NodeJs crashes in idle time and how do I fix it?

4 Answers4