Node.js mysql query returns empty array

Question

I wrote node.js script and it works not as expected. Here is my js code, that describes mysql connection;

var mysql = require('mysql');
var createConnectionMYSQL;
var connectCount=0;
(createConnectionMYSQL = function () {
    con = mysql.createPool({
      connectionLimit:10,
      host: "*****",
      user: "*****",
      password: "********",
      database: "dbname"
});
      console.log(++connectCount);
})();
con.query("SET SESSION wait_timeout = 120");
con.query('set names utf8');
con.on('error', function (err) {
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {  
        createConnectionMYSQL();                          
    } else {                                       
        throw err;                                  
    }
});

Main function returns promise that I handle.

function getRecipes(str, page) {
    return new Promise(function (resolve, reject) {
        //unimportant code
        var sql = 'SELECT ID, Recept, MATCH (Recept) AGAINST ("+' + ingredients[0] + '*" IN BOOLEAN MODE) as REL FROM recipes WHERE (MATCH (Recept) AGAINST ("+' + ingredients[0] + '*" IN BOOLEAN MODE))>0 ORDER BY REL';
        if (page != 0) sql += ' LIMIT ' + (page * 12) + ' ,12';
        con.query(sql, function (err, result, fields) {
            if (err) return reject(err);
         //   console.log(result + ' ' + sql);
            resolve(result);
        });
    });

}

But I get strange behavior of mysql module. In getRecipes(par1,0).then(function(results){}) I get first empty array and one minute later I get normal array with results as if resolve() worked twice. But it's Lucky case. Sometimes I got more empty arrays and then expected array with results.

@RyanWu, I just checked, ane yes, `resolve` triggered more than one time... But why? I have correct mysql query. — Vladislav Vazhenin, Oct 25 '17 at 14:25

score 0 · Answer 1 · answered Oct 25 '17 at 14:46

I think the return in 'return reject(err)' is unnecessary, but that shouldn't explain this behaviour.

Do you have the connection part in the same promise loop that handles the query? So that first you check with promise that connection is ok, and after that, you make the query? If not, then that might cause some problems.

Have you checked from the database, that how many requests are coming?

Node.js mysql query returns empty array

1 Answers1