Sporadically, only the last query returns a result when using multiple queries?

[rabbitfufu]

I am using Node-Mysql in combination with Generic-Pool to manage connection pooling.

I am using multiple queries.

I am running into a strange problem where once in awhile, without any pattern I can identify, only the last query is returning a result.

My code looks like this...

var sql = " SELECT SQL_CALC_FOUND_ROWS * FROM stuff; SELECT FOUND_ROWS(); ";

pool.acquire(function( err, db ){
  if (err) {
    throw new Error( "Could not acquire connection." );
  } else {
    db.query( sql, function( err, result ) {
      if ( err ) {
        throw new Error( "Could not do query." );
      } else {
        console.log( result[1][0] ); // 14
        console.log( result[0] ); // empty array []
      }
      pool.release( db );
   });
  }
});

...As you can see result[0] should have 14 rows of data in it - and most of the time it does. But once in awhile, it comes back as an empty array.

I am hoping someone here may be able to advise.

Thanks (in advance) for your help.

[dresende]

Are you sure it's not a generic pool problem? I can execute that kind of queries with no problems.

[rabbitfufu]

I have done quite a bit more testing. The problem is not what I thought it was originally, but I still believe it to be in node-mysql.

The problem only occurs when the following 2 conditions are met:

1. Only under race conditions, when I am firing queries to the mysql server rapidly. (For example, an auto-fill text-field)

2. Only when I escape query values using the ? character syntax.

For example, if I have have an sql query like so...

var sql = "SELECT * FROM stuff WHERE ID = ?"
db.query( sql, value, function() {...} );

...and then I call it under race conditions, before too long it returns an empty array, which is incorrect.

If I change my escape method to the following...

value = value.toInt();
var sql = "SELECT * FROM stuff WHERE ID = " + value
db.query( sql, function() { ... });

...it works perfectly.

I am now working around this problem in my code by manually escaping the values. However I would really prefer to use the parameterized query syntax.

I hope this is helpful, please let me know if you have other questions

[dougwilson]

A lot has changed since this time, but it sounds like your issue is hat whatever you are passing as value is being shared between your queries instead of it being unique. Since the placeholders are interpolated at at later time and not synchronously with the db.query call, that may be your issue. Please feel free to re-open with more details, like a PR that fixes your issue or some code we can run to reproduce the issue.