Error: Connection lost: The server closed the connection.

[apparatusdeus]

I keep leaving node running over night and when I come back in the morning the instance has thrown an exception and ended.

Error: Connection lost: The server closed the connection.
    at Protocol.end (/node/node_modules/mysql/lib/protocol/Protocol.js:72:13)
    at Socket.onend (_stream_readable.js:483:10)
    at Socket.g (events.js:175:14)
    at Socket.EventEmitter.emit (events.js:117:20)
    at _stream_readable.js:910:16
    at process._tickCallback (node.js:415:13)

I am unsure why this is occurring. I created a connection pool under the impression that unused connections that terminate would be handled and reconnected when needed again. Is this untrue or am I not closing my connection correctly? Do I have to explicitly handle the disconnection even on a connection pool?

Here is a portion of my code:

var mysqlPool = mysql.createPool({
    host     : '*HOST*',
    user     : '*USERNAME*',
    password : '*PASSWORD*',
    database : '*DATABASE*'
});

var statues = {};
mysqlPool.getConnection(function(err, connection) {
    if(err) throw err;

    connection.query('SELECT * FROM table', function(err, rows) {
        if(err) {
            console.error(err);
            process.exit();
            return;
        }
        for(row in rows) {
            statues[rows[row]['id']] = rows[row]['value'];
        }

        /* Do other stuff - No other queries */
        connection.end(); // Didn't have this originally. Is it required?
    });

    connection.end();
});

If I called another query within the callback of the first query would I also need to call connection end in its callback or is it only required in the getConnection callback.

The instance very rarely hits the database as it only needs it for specific tasks so I'm guessing the connection is being terminated because its its not in use.

Sorry if this all seems a little obvious to anyone I'm still getting use to the whole Asynchronous coding style (I use PHP normally).

[ifsnow]

Here is a correct example of connection.end

mysqlPool.getConnection(function(err, connection) {
  if(err) throw err;

  connection.query('SELECT * FROM table', function(err, rows) {
    ....
    ....
    connection.end();
  });

  // Wrong use.
  // connection.end();
});

The order of the function call in your code. (general case)

(1) mysqlPool.getConnection()
(2) connection.query()
(3) connection.end() <= Wrong use
(4) callback function of connection.query()
(5) connection.end() of callback function

connection.end() must be called within the callback function.
connection.end() releases the connection. The connection can be used in the other getConnection callback.

[apparatusdeus]

Based on your example would this also be correct:

mysqlPool.getConnection(function(err, connection) {
    if(err) throw err;
    connection.query('SELECT 1 AS a', function(err, rows) {
        if(err) {
            connection.end();
            console.error(err);
            return;
        }
        connection.query('SELECT 2 AS b', function(err, rows) {
            if(err) {
                connection.end();
                console.error(err);
                return;
            }
            connection.query('SELECT 3 AS c', function(err, rows) {
                if(err) {
                    connection.end();
                    console.error(err);
                    return;
                }
                ....
                ....
                connection.end();
            });
        });
    });
});

1. Does the connection need to be ended if an error occurs?
2. Should connection be ended only after all sub-queries and there callbacks have been completed?

[pranavs80]

@apparatusdeus can u please help me out regarding the pooling problem.
the example given above, i am having same way my query getting executed in nested form.

can u please let me know about closing the db connection. #516

[apparatusdeus]

In regard to the pooling problem. I managed to 'fix' the issue by updating both my version of node and node-mysql to the latest version although I am unsure which update resulted in the fix.

I am however still unsure weather or not I am required to end a connection when an error has occured:

if(err) {
    connection.end();
    console.error(err);
    return;
}

although I believe it likely it would be nice for someone to confirm this.

[pranavs80]

@apparatusdeus thanks. i updated my node and node mysql both, but still same issue.

[pranavs80]

@apparatusdeus - i am getting error message. when i try to connect DB after some request.

TypeError: Cannot call method 'query' of undefined
at /home/ubuntu/nodejs/smaato-mobidealer-8003.js:210:15
at Handshake._callback (/home/ubuntu/node_modules/mysql/lib/Pool.js:35:9)
at Handshake.end (/home/ubuntu/node_modules/mysql/lib/protocol/sequences/Sequence.js:66:24)
at Handshake.ErrorPacket (/home/ubuntu/node_modules/mysql/lib/protocol/sequences/Handshake.js:64:8)
at Protocol._parsePacket (/home/ubuntu/node_modules/mysql/lib/protocol/Protocol.js:169:23)
at Parser._onPacket (native)
at Parser.write (/home/ubuntu/node_modules/mysql/lib/protocol/Parser.js:62:12)
at Protocol.write (/home/ubuntu/node_modules/mysql/lib/protocol/Protocol.js:36:16)
at Socket.ondata (stream.js:38:26)
at Socket.emit (events.js:67:17)

Hello, @pranavs80 @apparatusdeus

I had same issue before, till I destroy the connection in the last block of query.
I think it's caused by the value of mysql max_connection be reached.

When you are done with a connection, just call connection.end() and the connection will return to the pool, ready to be used again by someone else.
If you would like to close the connection and remove it from the pool, use connection.destroy() instead.

So I just destroy the connection in the last block of query to prevent the max_connection from being reached.

Here is my example (based on @apparatusdeus ):

mysqlPool.getConnection(function(err, connection) {
    if(err) throw err;
    connection.query('SELECT 1 AS a', function(err, rows) {
        if(err) {
            connection.end();
            console.error(err);
            return;
        }
        connection.end();
    });
    connection.query('SELECT 2 AS b', function(err, rows) {
        if(err) {
            connection.end();
            console.error(err);
            return;
        }
        connection.end();
    });
    connection.query('SELECT 3 AS c', function(err, rows) {
        if(err) {
            connection.end();
            console.error(err);
            return;
        }
        connection.destroy(); /* Destroy the connection in the last block of query */
    });
});

[apparatusdeus]

Hi @chimerhapsody, you don't need to destroy() the connection just call .release() on it to have it sent back to the pool which will hold it until getConnection is called again.

@apparatusdeus Thank you! That's really useful!

[pranavs80]

@chimerhapsody @apparatusdeus - Thank you..

[kai-koch]

Feel free to reopen, if your problem still exists.