Mysql: on result event not fired when 1 row only exists

[HassanAzzam]

The callback is working fine as expected, but I noticed that query.on('result') event doesn't fire when there is only one row is being retrieved. Is anyone experienced this issue before?

This issue is not that big deal for creating apps, I'm just working on something related to events.. that's why I'm asking about it.

[dougwilson]

Interesting, I haven't heard of that before, and especially as far as I know this module never emits a result event on a connection object. Can you provide either a pull request with a fix or code and instructions on how to reproduce the issue?

[HassanAzzam]

In this example, in on.('end') callback it's expected for rows to be [{1: 1}], but it logs [] in console.

var rows   = [];
var fields = [];
connection.query('SELECT 1', function(){})
  .on('error', function(err) {
    assert.ifError(err);
  })
  .on('fields', function(_fields) {
    fields = _fields;
  })
  .on('result', function(_rows) {
    rows.push(_rows);
  })
  .on('end', function() {
    // rows here is []
    assert.deepEqual(rows, [{1: 1}]);
    assert.equal(fields.length, 1);
    assert.equal(fields[0].name, '1');
    server.destroy();
  });

In this example, in on.('end') throws Error: Connection lost: The server closed the connection. error to console.

var rows   = [];
var fields = [];
connection.query('SELECT 1')
  .on('error', function(err) {
    assert.ifError(err);
  })
  .on('fields', function(_fields) {
    fields = _fields;
  })
  .on('result', function(_rows) {
    rows.push(_rows);
  })
  .on('end', function() {
    assert.deepEqual(rows, [{1: 1}]);
    assert.equal(fields.length, 1);
    assert.equal(fields[0].name, '1');
    server.destroy();
  });

  // Throws connection lost error

[dougwilson]

Thanks for the code examples. Can you add some more explanation to each so I understand which one does what and what you then expect them to do?

[dougwilson]

One difference between the two is that your first example is incorrect for events. From the documentation at https://github.com/mysqljs/mysql/blob/master/Readme.md#streaming-query-rows

You MUST NOT provide a callback to the query() method when streaming rows.

[HassanAzzam]

Expectations are added to the previous comment

[HassanAzzam]

Is the reason for the second example is missing pause()?

[dougwilson]

The second example dies because you are calling .destroy() within the event. If you want to end the connection gracefully you should call .end()

That's still a bug at some level, but you probably don't want to be calling .destroy() in the first place. Any reason you're calling .destroy() vs .end() there?

[HassanAzzam]

The server here wasn't the connection object, I used connection.end() and it worked like a charm.
Thanks 😄

[dougwilson]

Oh, oops, i thought it was connection.destroy. what does server refer to? I don't think you should get that error in the second example, but not 100% sure how to reproduce. I tried just pasting it and running filling i missing vars like connection, but idk what to put in for server var.

[HassanAzzam]

It's from query test cases:

var assert     = require('assert');
var common     = require('../../common');
var connection = common.createConnection({port: common.fakeServerPort});

var server = common.createFakeServer();

server.listen(common.fakeServerPort, function (err) { 
... 
})

[dougwilson]

oooo, that makes sense. Since the MySQL server was destroyed, the connection you had opened emitted the connect lost error.