node-mysql connection end or destroy not working

Question

I don't know why mysql.end() or mysql.destroy() are not working as i expect. This is my code.

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'db',
    password: ''
});
connection.connect();
connection.query('SELECT * FROM ofertas ', function (err, rows, fields)
{
    if (err) console.log(err);
    else
    {
        console.log(rows);
    }

});
connection.destroy(function (err)
{
    if (err) throw err;
});

I execute this code and then i go to mysql command line and i type this :

show status like 'Conn%';

Before node code

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 3     |
+---------------+-------+

After node code

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 4     |
+---------------+-------+

connection.state = 'disconnected'

Thanks

score 1 · Answer 1 · answered May 20 '15 at 06:13

1

You need to wait until the query is finished before you can destroy the connection. Try

connection.query('SELECT * FROM ofertas ', function (err, rows, fields)
{
    if (err) console.log(err);
    else
    {
        console.log(rows);
    }
    connection.destroy();
});

answered May 20 '15 at 06:13

Peter Smartt

358
3
11

I think there might be a problem with node-mysql - I am getting a problem where the connection gets destroyed, but that another one gets created straight away (that's why I ended up on this thread). Try adding `console.log(connection.threadId);` just before you destroy the connection, and then check in mysql or with ps aux if the thread id has incremented by 1. None-the-less you still need to wait for the query to finish before you destroy the connection. – Peter Smartt May 21 '15 at 00:59
when using connection.end() all the queries callbacks are executed and when they finish then a finish paquet is sent to the mysql server. Using connection.destroy() inside the callback function isnt the same? By the way, i think i missunderstood the difference between mysql connections and threads. Connections are all the past connections and threads are current connections? – Cesar Zubillaga May 26 '15 at 10:14
2

Don't use connection.end(); I think it's obsolete. If you are using a connection pool, connection.release() returns it back to the pool so that another query can re-use it, or if not it will just sleep until the timeout cleans it up (run MySQL SHOW PROCESSLIST). This may not be the behaviour you want. Connection.destroy() gets rid of the connection completely. BTW the problem I was having was due to a completely separate part of the code making a query and not destroying it. I tracked it down with console.logs in the mysql code. Connection.threadId refers to the Linux process. – Peter Smartt May 26 '15 at 11:23

score 0 · Answer 2 · answered Mar 01 '22 at 23:45

You can use the below code, for cleaner termination .

connection.execute({
      sqlText: 'select * from <TABLENAME>',
      complete: function(err, stmt, rows) {
        if (err) {
          console.error('Failed to execute statement due to the following error: ' + err.message);
        } else {
          numRows =rows.length;
          console.log('Number of rows produced: ' + numRows);
        }
        connection.destroy(function(err, conn) {
          if (err) {
            console.error('Unable to disconnect: ' + err.message);
          } else {
            console.log('Disconnected connection with id: ' + connection.getId());
          }
        });

      }
    });

node-mysql connection end or destroy not working

2 Answers2