New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error: Connection lost: The server closed the connection. #528

Closed
apparatusdeus opened this Issue Jul 2, 2013 · 12 comments

Comments

Projects
None yet
6 participants
@apparatusdeus

apparatusdeus commented Jul 2, 2013

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

This comment has been minimized.

Show comment
Hide comment
@ifsnow

ifsnow Jul 2, 2013

Contributor

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.

Contributor

ifsnow commented Jul 2, 2013

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

This comment has been minimized.

Show comment
Hide comment
@apparatusdeus

apparatusdeus Jul 2, 2013

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?

apparatusdeus commented Jul 2, 2013

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

This comment has been minimized.

Show comment
Hide comment
@pranavs80

pranavs80 Jul 8, 2013

@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

pranavs80 commented Jul 8, 2013

@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

This comment has been minimized.

Show comment
Hide comment
@apparatusdeus

apparatusdeus Jul 8, 2013

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.

apparatusdeus commented Jul 8, 2013

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

This comment has been minimized.

Show comment
Hide comment
@pranavs80

pranavs80 Jul 8, 2013

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

pranavs80 commented Jul 8, 2013

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

@pranavs80

This comment has been minimized.

Show comment
Hide comment
@pranavs80

pranavs80 Jul 8, 2013

@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)

pranavs80 commented Jul 8, 2013

@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)

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Aug 30, 2013

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 */
    });
});

ghost commented Aug 30, 2013

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

This comment has been minimized.

Show comment
Hide comment
@apparatusdeus

apparatusdeus Aug 30, 2013

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 commented Aug 30, 2013

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.

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Aug 30, 2013

@apparatusdeus Thank you! That's really useful!

ghost commented Aug 30, 2013

@apparatusdeus Thank you! That's really useful!

@pranavs80

This comment has been minimized.

Show comment
Hide comment
@pranavs80

pranavs80 Aug 30, 2013

@chimerhapsody @apparatusdeus - Thank you..

pranavs80 commented Aug 30, 2013

@chimerhapsody @apparatusdeus - Thank you..

@kai-koch

This comment has been minimized.

Show comment
Hide comment
@kai-koch

kai-koch Oct 27, 2013

Collaborator

Feel free to reopen, if your problem still exists.

Collaborator

kai-koch commented Oct 27, 2013

Feel free to reopen, if your problem still exists.

@kai-koch kai-koch closed this Oct 27, 2013

@SanjayKewat

This comment has been minimized.

Show comment
Hide comment

SanjayKewat commented Aug 4, 2014

@dougwilson dougwilson added the question label Aug 4, 2014

@mysqljs mysqljs locked and limited conversation to collaborators Aug 4, 2014

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.