Custom Format causing errors time from time

[knpaing]

Hi,

I am using "custom format" mentioned in documentation but I am facing some strange problems. I'd get following error message at times. Then I just click "OK" on my error message box and submit the form again and it works without any problem. Please advise. Thanks.

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':id, :txn_date, :staff_id, :project_id, :quantity, :unit_of_measurement, :unit_p' at line 1"

[dougwilson]

If I were to take a guess, maybe under some conditions you are not passing in a values array on accident? Otherwise I don't know what else would cause it. If that was not the answer, then perhaps a reproduction is necessary for us to investigate.

[knpaing]

I've traced almost everywhere I can of in the codes on both sides, API and frontend I didn't not find any such problem where the values were missed out. But I am using this

pool.query = util.promisify(pool.query);

to use the query with async/await feature. I will test it without using promisify and get back to you. Thank you.

[knpaing]

I have tried several ways and I believe adding the queryFormat in constructor fixed the problem. So far I am not getting any problem at all. May be adding queryFormat in "getConnection" method might go out of sync sometimes may be.

[dougwilson]

Hm, I'm not sure what you mean by adding it in the getConnection method. If you can provide code that reproduces the issue, even if it is only sometimes that would help a lot for us to investigate.

[knpaing]

In documentation, it shows like this:

connection.config.queryFormat = function (query, values) {
  if (!values) return query;
  return query.replace(/\:(\w+)/g, function (txt, key) {
    if (values.hasOwnProperty(key)) {
      return this.escape(values[key]);
    }
    return txt;
  }.bind(this));
};

But instead I added queryFormat in constructor like this:

const pool = mySql.createPool({
  host: 'localhost',
  user: 'user',
  database: 'test',
  password: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  queryFormat: (query, values) => {
    if (!values) return query;
    return query.replace(
      /\:(\w+)/g,
      function(txt, key) {
        if (values.hasOwnProperty(key)) {
          return mySql.escape(values[key]);
        }
        return txt;
      }.bind(this)
    );
  }
});

To reproduce the issue is to use the first method and use NodeJS util to use async/await.

pool.query = util.promisify(pool.query);

1 or 2 out of 10 we can see that error. If you need more complete code, I will create a small project and share it with you. But it require MySql server tho.

[dougwilson]

I mean, the example is showing it being added just before the query call. Is that what you are doing or something else? Maybe that small example project would help understand what the code look like.

[knpaing]

I will provide you with a small project. Please give me a couple of days. Thanks.