Query ends, but no callback

[AmitMY]

I found a class of queries where when I run them, and they end (I'm looking in the processlist of the database) they never return any value, error, or "end" event.

This query checks if a column has all NULL values, by checking if at least one row is not NULL. If the column has all NULL values, this query never ends.

SELECT 1 FROM table WHERE column IS NOT NULL LIMIT 1

I wrapped the db query to be a promise, as well as added an "on end" callback:

  query(rawSql: string, values: any[][] = []): Promise<any[]> {
    const that = this;
    return new Promise((resolve, reject) => {
      const query = this.db.query(rawSql, values, function (err: MysqlError | null, res?: any) {
        if (err) {
            reject(err);
          } else {
            resolve(res);
          }
      });

      query.on('end', () => console.log("end"));
    });
  }

[dougwilson]

What type of object does this.db refer to in your code above? Is it a connection or a pool?

[AmitMY]

Thanks for checking, @dougwilson It is a pool:

this.db = createPool({...}

Another way to write this, which is more complete I guess:

import {createPool as mysqlCreatePool, MysqlError} from 'mysql';

const db = mysqlCreatePool({...})

const sql = "SELECT 1 FROM table WHERE column IS NOT NULL LIMIT 1";

const query = db.query(sql, undefined, function (err: MysqlError | null, res?: any) {
  console.log(err, res);
});

query.on('end', () => console.log("end"));

[dougwilson]

Thank you so much! I'm not sure what is happening, and I haven't been able to reproduce it myself, so don't have anything to debug against. I tried setting up a table to use the query you showed above, but no luck so far.

[AmitMY]

Thanks. To reproduce I tried what I said, and you are correct, it doesn't reproduce when I create a new table. The problem might not be the query, as when I make it on a table with 1 million rows, it takes a few seconds and finishes, but if it is on a table with 100 million rows it takes more than 140 seconds, and it seems like that's the point the query ends.

Running this query on phpMyAdmin I get:

MySQL returned an empty result set (i.e. zero rows). ( Query took 147.7342 sec )

(which is the correct response)

---

I wanted to give you the table to debug, so I created a new table only with 2 columns from the previous table, but in the new table (with smaller number of columns) the query succeeds (takes 5 seconds) which leads me to believe even more its a matter of time.

How would you advise me to proceed from here?

[AmitMY]

Is there a way to listen when query perhaps disconnects from the server?

[bugfixman]

Me too occurred this error. I have 3 queries. Two queries insert and update executes in the transaction. And select query will be executed after that the transaction is completed.

After has been executed about 10 queries the function query will not called the callback. No any errors. Just the callback is not executed. Only reload the nodeJs app could help fix a bug. But after will made 10 queries the callback will not called. Have any idea?

Sorry. I'm not native speaker. I hope that not made too much mistakes.

[bugfixman]

I'm getting a connection from getConnection only for transactions. For not transaction queries i'm used a connection got from createPool.

Probably connections not comes back to the pool, because a connection got from createPool without using getConnection and release.

It may be associated with this bug? Will be event thrown If all connections in the pool is busy?

[bugfixman]

@AmitMY It happened because a connection didn't come back to the pool. A connection will not released automatically without using a release method. I've added a release method to my code. Now it's works fine. My problem was solved. Probably your problem associated with not released connections. In my case the callback wasn't executed because all connections was engaged.

[AmitMY]

Thanks @bugfixman , can you please write down some example code of what you did to fix this? Like how do you catch the "not released"?

[bugfixman]

@AmitMY

If a connection was got from getConnection in this case to call release method after each query is completed. If an example was not helpful for you. I will give you code that using on my project.

I could explain is better, but i can't because my english is terrible. I hope made something helpful for you. If your problem will not fixed write here. I'll help you if i can.

[bugfixman]

Like how do you catch the "not released"?

Do you mean how i determine that a connection didn't come back to the pool? Nohow. When all connections was engaged, i didn't get nothing. No errors. No events. There is release event, but i think it's works if only to call the release method.

[travddm]

@AmitMY Have you found a fix for this issue?

I'm seeing a similar problem where the query ends, but I get no callback fired. Not even with an error. This happens inconsistently, maybe once every 1-200 tries, and it's otherwise always successful. I have only seen this happen on an Ubuntu 18.04.4 Droplet on DigitalOcean, and I have not been able to repro it on my own machine no matter how hard I try.

I'm using a simple SELECT * FROM table query on some tables with very little data in them, so I'm almost certain it's not just the query taking forever.

I have the connectTimeout on the pool set to 10000 (10s), the acquireTimeout on the pool set to 5000 (5s), and the query timeout set to 10000 (10s).

Edit: Each time I get a connection, I execute a single query on it. The very first thing that happens in the callback is the connection gets released.

[AmitMY]

@travddm I did not solve it yet

[deki23]

I seem to be running into a somewhat similar issue. I am using the createPool method for connecting, and sometimes the server just won't execute any SQL query. Any updates on this?

[roxer1993]

I think i have runned into the same issue. When ever the query returns an empty result the callback isnt runned properly. I do get the empty result forwarded but the connection is not released and no timeout seems to kill it. After a while i stop getting querys runned at all (probably cause the pool connections is all used up.) and have to restart app to get it working again. Its all released properly when the result isnt empty. So line 61-70 isnt issued when the result is emtpy.
https://pastebin.com/549kBU4L

(I have used this module for several applications without problems previously)

[roxer1993]

issue persists with latest mysql-server version and mysql npm package, would love to get an update if this is solveable or if i need to find another solution..

[jarikoff]

I have runned into the same issue as well. I use poll.getConnection for my transaction queries. If i run in parallel 10 requests with connectionLimit: 10 - all goes well. But once i try to run 20 requests in parallel - i see few of them taken and released back to pool but other part of requests are in a waiting state. No errors occurs. App just "stacked" until reboot. Here's some logs: Create conn 175666 Create conn 175669 Connection 175669 released Connection 175666 released Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Create conn 175680 Waiting for available connection slot Create conn 175681 Create conn 175682 Waiting for available connection slot Waiting for available connection slot Create conn 175683 Waiting for available connection slot Create conn 175684 Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Create conn 175686 Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Waiting for available connection slot Create conn 175687 Create conn 175688 Waiting for available connection slot Waiting for available connection slot <-- The last one and no more errors/logs

[jarikoff]

@dougwilson Small update: once i set queueLimit: conncetionLimit - 1 to force error if queue > than possible connections - everything works good except error Queue limit reached. Expected behavior - if trying to get connections more than available - wait for released one instead of locking for no reason. I hope it will help you to make it work :)