Mysql: When will the connection be closed

[TooBug]

Thanks this library.

I'm using it with express, so the script is always running.

At first, I didn't close the mysql connection manually, but the connection count of mysql keep increasing.

Then I tried to close the connection (in the callback function) after the query , but many errors occurred in the log file, the code is connection.end().

Error: Cannot enqueue Quit after invoking quit.

In the docs, I saw the connection.end() was written outside the callback function.

What's the proper way to deal with connection? Should I closed it manually? If Yes, when ?

[dougwilson]

Are you using a pool? Also, to answer your question about the error, I would need to know what your code looked like that was causing the error.

What's the proper way to deal with connection? Should I closed it manually? If Yes, when ?

If you are using a pool (createPool) then it is managed for you and you should not be trying to close it yourself. If you are using this library on a web server like express, you should use a pool with createPool and then leave it alone to manage the connections. It would be the same as using mysql in PHP with persistent connections.

but the connection count of mysql keep increasing

It will increase until the value of connectionLimit you give to the pool configuration. If your count kept going up, either you are not releasing the connections back to the pool or your server really needed that many connections.

[TooBug]

Thanks for your reply.

I'm not using a pool. My code is like this:

    var conn = mysql.createConnection(config.dbStr);
    conn.query('some sql',function(err,result){
conn.query('some sql',function(err,result){
            conn.end();
        }
    });

The conn.end() caused this error. I'm not sure if this happened every time.

Anyway, Thanks very much, I'm not very familiar with connection pool, and the frameworks I used in PHP does the detailed things.  I'll try the pool, maybe it can solve my problem. Thanks again.

[dougwilson]

The non-pool you posted above is correct, but I feel like you are just giving the simplified version. Basically the error you posted is because you called conn.end() twice on the same conn, so you are probably passing around callbacks and somewhere in your code you are calling the same callback twice.

[TooBug]

Yeah, the code is just a demo. The code in production are located several places. I'll check it carefully these days, thanks for your patiently explanation.

[TooBug]

If I use the pool, should I release it manually?

[dougwilson]

If you use the pool, there are two different things you can do:

1. If you are not using transactions and other multi statement things, you can simply use pool.query(sql, cb) and it'll manage everything for you.
2. If you need to use pool.getConnection(cb), then yes, you must be sure to call connection.release() in every code path from that point so the connection will get released back into the pool.

[TooBug]

Very clear. Maybe written in the document is better, these different usages are confused.

var conn = mysql.getConnection(options);

conn.query(sql,function(err,results){
    conn.end();
});

var conn = mysql.getConnection(options);

conn.query(sql,function(err,results){
});

conn.end();

var conn = mysql.getConnection(options);

conn.query(sql,function(err,results){
});

conn.end();

var pool = mysql.createPool(options);

pool.query(sql,function(err,results){
});

var pool = mysql.createPool(options);

pool.getConnection(function(err,conn){
    conn.query(sql,function(err,results){
        conn.release();
    });

[dougwilson]

How are they confused? You quoted a bunch of different, valid uses out of context. You should be looking in this section: https://github.com/felixge/node-mysql#pooling-connections

[TooBug]

I have read this, but until you explain whether to release the connection, I still have no idea about that. For example, What is the difference between using the pool directly and get a connection then use? When should I get a connection and release it?

The same for the none pool, what's the difference between writing the conn.end() in and out of the callback function?

These questions really make me confused. Maybe it's because I'm not very familiar with MYSQL, but if see these things in the document, I would be very happy.

Anyway, thank you very very much!

[dougwilson]

What is the difference between using the pool directly and get a connection then use? When should I get a connection and release it?

It 100% depends on your use-case. I explained the difference a bit above. You only need to get a connection and manually release it if you are using transactions or multiple statements (like LAST_INSERT_ID(). All other uses you can just use pool.query and not worry about releasing the connection. The difference between the two requires knowledge of your own MySQL queries you are running, so I cannot tell you which you need.

The same for the none pool, what's the difference between writing the conn.end() in and out of the callback function?

Nothing if you only want to do a single query. This library is the raw bare-bones MySQL prototype-level library. As such, it unfortunately requires you have knowledge on how exactly MySQL works.

[TooBug]

I see it now, after your explanation. I mean, why don't put these explanation in the document?like this

If you have to use transaction or multi statement things such as LAST_INSERT_ID(), you should get a connection first, and the release it after all the queries. If not, just use the pool directly.

Because not everyone knows the low level detail of MYSQL, especially for those who wrote PHP before, because the php extension and frameworks do all the things. At lease, in China, I have confidence to say, many developers knows little about MYSQL knowledge.

Just a suggest, you can still do your things in your own way, feel free. :)

[dougwilson]

I really appreciate the feedback. I will try to expand and add more stuff to our documentation as much as I can.

[TooBug]

I'm coming back again, I switched my app to use connection pool. When the app is just started, everything goes well, but after some time, the pool.query would fail, the err in callback is

[Error: Pool is closed.]

In my understanding, the pool should reconnect automatic, isn't it?

My code is like this:

var getDbConnection = function(){

    if(!pool){
        /*pool = mysql.createPool({
            host:'localhost',
            user:'root',
            password:'root',
            charset:'UTF8_UNICODE_CI',
            timezone:'+0800',
            database:'dbname'
        });*/
        pool = mysql.createPool(config.dbStr);  //the dbStr is similar to the code above
    }
    return pool;
};

Each query:

var pool = getDbConnection();

pool.query(function(err,result){
    // after some time, the err says [Error: Pool is closed.]
});

Did I missing something?

[dougwilson]

Did I missing something?

The error is because you are calling pool.end()--do not do that unless you're closing your server process down.

[TooBug]

Oh, Thanks for your reminder. My mistake, so sorry. :+1: :smile:

[dougwilson]

No problem :)

[TooBug]

After one day's running, no problem now, so I closed this issue, Thank you very very much.

[dougwilson]

Great to hear!