Mysql: The difference between release and destroy (for connection)?

[macias]

Sorry for bringing up such issue, but I really have difficulties seeing when one should call destroy and release on connection. I know theory, but in practice release is not terminating command, so in the effect program hangs. It seems its practical usage is very limited. See my SO question:

http://stackoverflow.com/questions/38490440/when-you-call-release-instead-destroy-on-connection

(I still didn't do the tests mentioned in the answer).

Today I tried to understand the management of the connections in the pool, i.e. how safe is to use pool.query directly. There is an old topic: https://github.com/mysqljs/mysql/issues/857#issuecomment-47382419

So I dig up a bit and found out, that pool.query uses release on close (not destroy). I run very simple test case:

db.pool.query("Select COUNT(*) AS count from word", function(err,rows) {
  if (err)
    return next(err);

  console.log(rows[0].count);
});

In my case I have table word, feel free to put any table you have, with or without data. The point is such simplistic code leads to non-terminating program.

So either I don't understand something or there is some flaw with release. For the record, the issue with "pool.query vs. pool.getConnection" I linked above has the code with conn.release() so I believe it also translates to non-terminating program.

[sidorares]

your concern about "non-terminating program" suggest that you are writing a script that supposed to run some queries + do some calculations and exit. In that case you probably don't need pool.

Connections are relatively expensive (time: tcp connection + 3 roundtrips until first response to query; CPU, server side: mysql creates full thread per each new connection, and usually max number of allowed connections is very limited). For that reason it makes sense to allow to say "I'm done querying this connection, but if it's required later - don't close it now and mark as available". This is what conn.release() for

The point is such simplistic code leads to non-terminating program.

You need to tell that your program is ready to terminate: close your servers, your open tcp connections etc. With pool there is pool.end() for this - https://github.com/mysqljs/mysql#closing-all-the-connections-in-a-pool

[macias]

Thank you very much, so non-terminating state is intentional and has its purpose.

I am closing this topic then, because as for me, it is clear now. Once again -- thank you!

[sidorares]

Yes, this is the main use case for (any) pool - re-use things later (because they are relatively expensive to create and not so much expensive to keep ) = memory allocation pools, thread pools, process workers, tcp connections and many other things can be pooled