When to close MySQL connection using node-mysql?

Question

Currently using: https://github.com/felixge/node-mysql

I have the following code:

var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'me',
    password : 'secret',
    database : 'Database1'
});
app.put('/api/upload', function(req, res, next)
{
    connection.connect();
    doMultipleQueries(function(err)
    {
        connection.end();
    });          
};

The put request works perfectly fine, but calling it the second time, I get the following error

events.js:68
        throw arguments[1]; // Unhandled 'error' event
                       ^
Error: Cannot enqueue Handshake after invoking quit.
    at Protocol._validateEnqueue (/Users/anon/Desktop/project Web/node_modules/mysql/lib/protocol/Protocol.js:110:16)

Am I supposed to leave the connection open until the server dies?

UPDATE: When I move the mysql.createConnection into the put request function like so:

var connection = null; 
app.put('/api/upload', function(req, res, next)
{
    connection = mysql.createConnection({
        host     : 'localhost',
        user     : 'me',
        password : 'secret',
        database : 'Database1'
    });
    connection.connect();
    doMultipleQueries(function(err)
    {
        connection.end();
    });          
};

It works fine. Does this mean connection.end() closes what mysql.createConnection created and cannot be reconnected?

x-yuri · Accepted Answer · 2020-12-18 13:00:30Z

37

connection.end() does it job regardless to fatal error. If a fatal error occurs before the COM_QUIT packet can be sent, an err argument will be provided to the callback, but the connection will be terminated regardless of that.

Also check destroy() method. This will cause an immediate termination of the underlying socket.

You can add error handler.

https://github.com/felixge/node-mysql/blob/master/Readme.md#error-handling

// I am Chuck Norris:
connection.on('error', function() {});

Once terminated, an existing connection object cannot be re-connected by design.

Check here. It's showing connecting back after disconnect.

https://github.com/felixge/node-mysql/blob/master/Readme.md#server-disconnects

edited Dec 18, 2020 at 13:00

x-yuri

19.4k16 gold badges133 silver badges185 bronze badges

answered Nov 7, 2012 at 9:02

metalfight - user868766

2,7501 gold badge17 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ninjaneer Over a year ago

Thanks for answering, but it doesn't help me much. I actually did have code for connection.on error which displayed the error shown above. The end() properly closes the connection, but when I try to reconnect the connection, it doesn't connect.

joaopintocruz Over a year ago

I'm not a GitHub user so maybe you (someone) could easily tell those guys just to add a right curly brace at the end of the function passed in "connection.on". Thanks

Chris · Accepted Answer · 2013-12-26 00:48:15Z

4

I believe the proper way is to just get a connection for your app at startup, and end() it when your app closes.

edited Dec 26, 2013 at 0:48

user2555451

answered Dec 26, 2013 at 0:30

Chris

1,61112 silver badges11 bronze badges

7 Comments

ninjaneer Over a year ago

Probably not efficient since one connection might stall during a complex query.

Chris Over a year ago

If you expect long queries, use a connection pool (that is the idea behind a pool, that there is already a connection, ready for use), or make sure your query is optimized properly. node-mysql can also let you deal with disconnects and other errors.

G4BB3R Over a year ago

What do you mean by long queries ? More than 10 joins ?

JakubKnejzlik Over a year ago

@G4BB3R long query => long time consuming (for example taking >100ms)

David S Over a year ago

No no no. This is correct using NoSQL (e.g. Mongo), but sql connections have a limit. If your limit is 15 (10 or 15 is the default), that means you can only have 15 people on your site at any one time. You should be closing the connections after you're done doing what you need to do.

|

Collectives™ on Stack Overflow

When to close MySQL connection using node-mysql?

2 Answers 2

2 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related