Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

Question

I am using the following code to make a knex connection, but frequently the error occurred

Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

Can anyone please suggest the solution for this issue?

var knexConn = reqKnex({
        client: pClient,
        native: false,
        connection: pConn,
        searchPath: pSearchPath,
        pool: {
            max: 7,
            min: 3,
            acquireTimeout: 60 * 1000
        }
    });


function getTransactionScope(pKnex, callback) {
    try {
        pKnex.transaction(function(trx) {
            return callback(trx);
        });
    } catch (error) {
        console.log(error);
    }
}

function ExecuteSQLQuery(pTranDB, pTrx, pQuery, pCallback) {
    try {
        var query = pTranDB.raw(pQuery);

        if (pTrx) {
            query = query.transacting(pTrx);
        }
        query.then(function(res, error) {
            try {
                if (error) {
                    console.log(error);
                } else {
                    return pCallback(res, error);
                }
            } catch (error) {
                console.log(error);
            }
        }).catch(function(error) {
            return pCallback(null, error);
        });
    } catch (error) {
        console.log(error);
    }
}

function Commit(pTrx, pIsCommit) {
    try {
        if (pIsCommit) {
            pTrx.commit();
        } else {
            pTrx.rollback();
        }
    } catch (error) {
        console.log(error);
    }
}

Answer 1

I solved this problem with these versions:

"knex": "^0.21.1",
"objection": "^2.1.3",
"pg": "^8.0.3"

Answer 2

I had this issue recently, and I had just updated to Node v14.2.0

This version seems to have a breaking change to knex. Luckily I have NVM so I switch to another version (v12.16.3) and this solved the issue.

Good luck!

Answer 3

I also experienced this problem when I upgraded to node 14.0.0. I reverted my node version and this problem went away.

Answer 4

I've made some test. Here is the result.

"knex": "^0.20.8",
"objection": "^2.1.2",
"pg": "^7.14.0"

Using these versions my app works well on node 12.22.7 but I've got the timeout issue on 14.18.1.

Updating pg to version 8.0.2:

• 12.22.7 -> it works correctly
• 14.18.1 -> knex timeout error

Updating pg to version 8.0.3:

• 12.22.7 -> it works correctly
• 14.18.1 -> it works correctly

The issue goes away on node 14 when pg version is 8.0.3.

Answer 5

The attribute propagateCreateError should be set to false to prevent the Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? error.

Example pool configuration:

"pool": {
  "min": 2,
  "max": 6,
  "createTimeoutMillis": 3000,
  "acquireTimeoutMillis": 30000,
  "idleTimeoutMillis": 30000,
  "reapIntervalMillis": 1000,
  "createRetryIntervalMillis": 100,
  "propagateCreateError": false // <- default is true, set to false
},

Explanation: The propagateCreateError is set to true by default in Knex and throws a TimeoutError if the first create connection to the database fails, preventing tarn (the connection pool manager) from re-connecting automatically.

The solution is to set propagateCreateError to false thus enabling knex to automatically reconnect on create connection failure instead of throwing the error.

AuroraDB: In case you are connecting to an AuroraDB instance, currently it has a very long startup time, causing the TimeoutError on every new cold-start, to fix this, set AWS Console -> RDS -> AuroraDB Instance -> Pause compute capacity after consecutive minutes of inactivity: 1440 hours to prevent the database from going to sleep completely.

For Detail explanation please see https://github.com/knex/knex/issues/2820

Answer 6

I had the same issue when deploying a strapi app to heroku. In my package.json I had the following versions:

• "knex": "<0.20.0"
• "pg": "^7.18.2"

I also had the following node engine configuration:

"engines": {
  "node": ">=10.0.0",
  "npm": ">=6.0.0"
},

Changing the versions to <0.21.1 and ^8.0.3 (as suggested here: https://stackoverflow.com/a/61482183/4696783 ) and changing node engine to 12.16.x (as suggested here: https://stackoverflow.com/a/61942001/4696783) solved the issue.

Answer 7

For anyone else coming across this, it may also result from a typo in your database host name (as was the case for me).

Answer 8

I had to patch knex npm package to retry acquiring pool connection atleast once before throwing error incase of knex timeout issue. Open file node_modules/knex/lib/client.js. You will see this method:

  async acquireConnection() {
    if (!this.pool) {
      throw new Error('Unable to acquire a connection');
    }
    try {
      const connection = await this.pool.acquire().promise;
      debug('acquired connection from pool: %s', connection.__knexUid);
      return connection;
    } catch (error) {
      let convertedError = error;
      if (error instanceof TimeoutError) {
        convertedError = new KnexTimeoutError(
          'Knex: Timeout acquiring a connection. The pool is probably full. ' +
            'Are you missing a .transacting(trx) call?'
        );
      }
      throw convertedError;
    }
  },

Replace with:

async acquireConnection() {
    if (!this.pool) {
      throw new Error('Unable to acquire a connection');
    }
    try {
      const connection = await this.pool.acquire().promise;
      debug('acquired connection from pool: %s', connection.__knexUid);
      return connection;
    } catch (error) {
      this.logger.warn('Acquire connection error, retrying once');
      try {
        if (!this.pool) {
          this.logger.warn('Pool has been destroyed, initializing again');
          this.initializePool();
        }
        const connection = await this.pool.acquire().promise;
        debug('acquired connection from pool: %s', connection.__knexUid);
        return connection;
      } catch (error) {
        let convertedError = error;
        if (error instanceof TimeoutError) {
          convertedError = new KnexTimeoutError(
            'Knex: Timeout acquiring a connection. The pool is probably full. ' +
              'Are you missing a .transacting(trx) call?'
          );
        }
        throw convertedError;
      }
    }
  },

then patch the package

yarn add -D patch-package
yarn patch-package knex

then create a postinstall npm script so that it runs automatically after each install:

"postinstall": "patch-package -p1 -i patches/<name of your patch file>"

PS: using node v14.17.6

Answer 9

Using NodeJS 14.7 LTS in a nginx server that runs pm2 to manage projects, all I needed to do was run pm2 reload {projectID} to "fix" it, if anyone faces it with the same conditions as me, try it first before changing packages, and downgrading your NodeJS.

Answer 10

Also check that your IP is among trusted sources on your DB. That was the issue for me:

Answer 11

If you have not changed anything and you've got this error, this maybe been caused of an external db connection. My case: customer didn't payed for the service.

Answer 12

Some points can generate this error, among them are these:

• Case you work behind a proxy this error can happen because you proxy in command line not configured fine. verify the envs http_proxy, https_proxy case your SO is linux.

• Case your connection is with ORACLE Database can be necessary configrure environments viriables to instantClient.

  Exemple:

     export PATH=/opt/oracle/instantclient_21_1:$PATH
     export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_1
  

• Case your conection is with oracle database and your application work with rabbitMQ (PREFETCH) or any service than use many work threads, this link https://oracle.github.io/node-oracledb/doc/api.html#-162-connections-threads-and-parallelism can help you. By default node use thread pool size 4, increasing the number of worker threads may improve throughput and prevent deadlocks

16.2 Connections, Threads, and Parallelism

If you open more than four connections, such as via increasing poolMax, you should increase the number of worker threads available to node-oracledb. A thread pool that is too small can cause connection requests to fail with the error NJS-040: connection request timeout or NJS-076: connection request rejected.

The thread pool size should be equal to, or greater than, the maximum number of connections. If the application does database and non-database work concurrently, then additional threads could also be required for optimal throughput.

Increase the thread pool size by setting the environment variable UV_THREADPOOL_SIZE before starting Node.js.

For example, on Linux your package.json may have a script like:

"scripts": {
    "start": "export UV_THREADPOOL_SIZE=10 && node index.js"   }, 
. . . 

Or, on Windows:

"scripts": {
    "start": "SET UV_THREADPOOL_SIZE=10 && node index.js"   }, 
. . . 

Answer 13

I had same issue when migrating from node v12.16.1 to v14.18.1 (latest 14 for now). Steps to fix the issue:

• bump knex version =0.20.1 --> 0.95.11
• bump pg version 7.12.1 --> 8.7.1
• remove knex-migrate module and do migrations via raw knex

       const [totalMigrations, executedMigrations] = await knex.migrate.up({
           directory: require('path').resolve(__dirname, 'migrations'),
           disableMigrationsListValidation: true, 
       });
       logger.info('Migration success', { totalMigrations, executedMigrations });

Answer 14

"express": "^4.16.2",
"knex": "^0.14.2",
"objection": "^2.1.3",
"pg": "^8.0.3",

i fixed my problem (end of the 4 day)

Answer 15

Just putting this here for others who might come in, my client is mssql I moved to

1. latest lts/gallium (v16.5.1)
2. latest knex 2.1.0
3. latest tedious 14.5.0

This worked for me.

Answer 16

I struggled with this issue for a long time, but then I realized that the config.connection configuration was connecting to a "secrets" service to get the database password (i.e. this was a secrets manager provided by GCP) and this service would sometimes take longer than expected (the Knex config.connection block can be async).

Long story short, the "secrets" service would flake and timeout unexpectedly (I don't think it was scaled to handle secrets like this), but then Knex would either cast the thrown timeout error as its own or Knex's timeout would fire first. The end result was a cryptic timeout error thrown by Knex.

This was really hard to pin down since it was random. So long as the secrets server was fast to respond the error was nowhere to be found. Thank god I decided to refactor the secrets to be loaded as ENV variables. Once I did that then I realized what was going on.

So the lesson here is to always load secrets data as ENV variables!

Answer 17

In my application I was facing (sometime in between)

TimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

My config of knex is

const config = client: process.env.CLIENT,
  connection: {
    host: process.env.DBHOST,
    user: process.env.DBUSER,
    password: process.env.DBPASS,
    database: process.env.DATABASE
  },
  pool: { min: 0, max: 30, acquireTimeoutMillis: 60 * 1000 },
  seeds: {
    directory: './db/sds'
  },
  migrations: {
    directory: './db/mg'
  }
}

import knexLib from 'knex';
export const con = knexLib(config);

and I'm using it something like

import {con} from './con';
import {FormatError} from '../err'

const handler = (req)=>{
  const trx = con.transaction();
  try{
    const result = await con('insert-table').transacting(trx).insert(req.list).returning('*');
    const resultOfLog = await Promise.all(
      result.map((o) => {
        return con('log-table')
          .insert({event_id: 1, resource: o.id});
        })
    );
    return result;
  } catch(error){
    return new FormatError(error);
  }
}

Answer 18

I had the same issue, taking into consideration: this post.

The attribute propagateCreateError should be set to false to prevent the Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? error.

Example pool configuration:

"pool": { "min": 2, "max": 6, "createTimeoutMillis": 3000, "acquireTimeoutMillis": 30000, "idleTimeoutMillis": 30000, "reapIntervalMillis": 1000, "createRetryIntervalMillis": 100, "propagateCreateError": false // <- default is true, set to false }, Explanation:

The propagateCreateError is set to true by default in Knex and throws a TimeoutError if the first create connection to the database fails, preventing tarn (the connection pool manager) from re-connecting automatically.

The solution is to set propagateCreateError to false thus enabling knex to automatically reconnect on create connection failure instead of throwing the error.

and this post:

I have made the following configuration

module.exports = {
    client: 'pg',
    connection: {
        host: config.testDB.host,
        user: config.testDB.userName,
        port: config.testDB.port,
        password: config.testDB.password,
        database: 'testdb',
        charset: 'utf8'
    },
    pool: {
        max: 50,
        min: 2,
        // acquireTimeout: 60 * 1000,
        // createTimeoutMillis: 30000,
        // acquireTimeoutMillis: 30000,
        // idleTimeoutMillis: 30000,
        // reapIntervalMillis: 1000,
        // createRetryIntervalMillis: 100,
        propagateCreateError: false // <- default is true, set to false
    },
    migrations: {
        tableName: 'knex_migrations'
    }
}

If it doesn't work for you, try setting propagateError to true and uncomment

 "idleTimeoutMillis": 30000,
 "createTimeoutMillis": 30000,
 "acquireTimeoutMillis": 30000