Errors: Could not acquire a connection - Pool is closed

[vishalbiradar]

Node version: 10.13.0
Sails version (sails): 1.2.2
ORM hook version (sails-hook-orm): 2.1.1
Sockets hook version (sails-hook-sockets): 2.0.0
Grunt hook version (sails-hook-grunt): 4.0.0
DB adapter & version (e.g. [email protected]): [email protected]

---

I am trying to execute the 1000 API calls concurrently (using Artillery)but only 90-98 are successful and the remaining are failing. Also, the error is causing the server to stuck for some time.

I am getting below error:

getConnection failed ("failed"). Could not acquire a connection to the database using the specified manager

Then I tried with pool options:

pool: true,
connectionLimit: 100000,
connectTimeout: 60 * 60 * 1000,
acquireTimeout: 60 * 60 * 1000,
timeout: 60 * 60 * 1000,
waitForConnections: true

I am getting below error:

Error: Pool is closed.

Some times I am getting an error:

packets out of order. Got: 1 Expected: 0

[sailsbot]

@vishalbiradar Thanks for posting! We'll take a look as soon as possible.

In the mean time, there are a few ways you can help speed things along:

• look for a workaround. (Even if it's just temporary, sharing your solution can save someone else a lot of time and effort.)
• tell us why this issue is important to you and your team. What are you trying to accomplish? (Submissions with a little bit of human context tend to be easier to understand and faster to resolve.)
• make sure you've provided clear instructions on how to reproduce the bug from a clean install.
• double-check that you've provided all of the requested version and dependency information. (Some of this info might seem irrelevant at first, like which database adapter you're using, but we ask that you include it anyway. Oftentimes an issue is caused by a confluence of unexpected factors, and it can save everybody a ton of time to know all the details up front.)
• read the code of conduct.
• if appropriate, ask your business to sponsor your issue. (Open source is our passion, and our core maintainers volunteer many of their nights and weekends working on Sails. But you only get so many nights and weekends in life, and stuff gets done a lot faster when you can work on it during normal daylight hours.)
• let us know if you are using a 3rd party plugin; whether that's a database adapter, a non-standard view engine, or any other dependency maintained by someone other than our core team. (Besides the name of the 3rd party package, it helps to include the exact version you're using. If you're unsure, check out this list of all the core packages we maintain.)

---

Please remember: never post in a public forum if you believe you've found a genuine security vulnerability. Instead, disclose it responsibly.

For help with questions about Sails, click here.

[whichking]

Hi, @vishalbiradar!

I wonder if the database isn't configured to support that number of connections. If you reconfigure the database, we recommend reverting back to the default pool settings when testing it out.

If that doesn't seem to fix the problem, would you provide a minimal repo that reproduces the behavior you're getting? Thanks!

[vishalbiradar]

@rachaelshaw @MadisonHicks
I have tried with and without the pool configurations, but still facing the same "connection poll closed issue".
I will send you the sample of my project which you can use to validate.

[vishalbiradar]

@rachaelshaw @MadisonHicks @rachaelshaw

My questions are:

1. How can we enable a connection pool?
2. Hows the auto-increment of connections from the pool works?
3. How to set the default connections to be in the pool at any point in time?

Below is the code snippet from config/datastores.js file

    const host = process.env.MYSQL_SERVER || 'localhost';
    const user = process.env.MYSQL_USER || 'ddn_api';
    const password = process.env.MYSQL_PASSWORD || 'password';
    const database = process.env.MYSQL_DATABASE|| 'ddn_db_dev';
    const port = process.env.MYSQL_PORT || 3306;
    module.exports.datastores = {
        default: {
            adapter: require('sails-mysql'),
            url: `mysql://${user}:${password}@${host}:${port}/${database}`
        }
    };

[eashaw]

Hi @vishalbiradar,
Your app should be using the pool automatically with mysql. Is your app configured to run in production mode? https://sailsjs.com/documentation/concepts/deployment/scaling
Is your app configured to use transactions and leasing connections? If so there a chance that the application is leaking connections.

[anudeepchandrat]

Hi @eashaw Yes the app is configured to run in production mode. We are using transactions and tried matching the default connectionLimit from the application to be compatible with maximum connections allowed to the database. We are observing that application gets stuck waiting forever for connections to be returned to the pool once the connection limit is hit.
This behaviour was commented in one of the existing threads https://stackoverflow.com/questions/18496540/node-js-mysql-connection-pooling/18496936 as well.
Sample code how we are using the transactions:

export async function getDataSource(reqObj: any, populate: any) {
return await sails.getDatastore().transaction(async(db, proceed)=> {
try {
let responseData;
if (populate) {
responseData = await sails.models[samplemodelName].find(reqObj, true).usingConnection(db);
} else if (Object.keys(reqObj).length === 0) {
responseData = await sails.models[samplemodelName].find().usingConnection(db);
} else {
responseData = await sails.models[samplemodelName].find(reqObj).usingConnection(db);
}
return proceed(false, responseData);
} catch(err) {
return proceed(err);
}
});
}

But the above thread says we need to release connection explicitly if we are using getConnection(). We are using sails mysql-adapter which we hope handles opening the database connection and releases the connection back to the pool once the operation is completed (We hope that is not happening in our case at least). Could you please confirm this and help us resolving the issue to release the connections back to the pool?

Not sure if this is similar to #6908 for machinepack-mysql?

[eashaw]

Hi @anudeepchandrat, the default settings work for most deployments of mysql. People using custom connections to their database might have to change their database's settings or change the number of connections in their Sails configuration.

[anudeepchandrat]

@eashaw Thank you for the information. We tried changing the number of connections in their Sails configuration using connectionLimit to be compatible with number of connections allowed from mysql database. Once the connectionLimit is reached from Sails we could see the requests are added to the queue and waiting forever for the free connections to be available. Also the Sails application is not able to serve any requests further until the server is restarted. Could you please let us know your thoughts on this?

[eashaw]

@anudeepchandrat, The try catch that you shared in the code above is already being handled by Sails, have you looked for any connection leaks in the transaction?

[anudeepchandrat]

@eashaw As mentioned above we have tried increasing the connectionLimit from Sails for now to handle increase in number of clients. Could you please let us know how to check for connection leaks in transactions? It would be great if you can share some snippet for handling transactions that would work for any number of calls rather than increasing the connectionLimit.

[eashaw]

Hey @anudeepchandrat, We suggest looking anywhere where you use .transaction() and making sure .usingConnection(db) chained on.

[anudeepchandrat]

@eashaw Thank you for the suggestions. We were able to resolve the issue following the references and approached mentioned at https://dev.paygoenergy.co/blog/2019-06-20-sailsjs-transactions-and-exits/ (Rule: never reference exits from within transaction).