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

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);
    }
}

node.js knex.js

Solution 1:^[1]

I solved this problem with these versions:

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

Solution 2:^[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!

Solution 3:^[3]

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

Solution 4:^[4]

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.

Solution 5:^[5]

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.

Solution 6:^[6]

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

Solution 7:^[7]

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

Solution 8:^[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

Solution 9:^[9]

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

Solution 10:^[10]

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 });

Solution 11:^[11]

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.

Solution 12:^[12]

"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)

Solution 13:^[13]

if 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.

If 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

Solution 14:^[14]

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.

Solution 15:^[15]

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);
  }
}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Bratislav Damnjanovic
Solution 2	wiredmartian
Solution 3
Solution 4	alcaprar
Solution 5	foxbit
Solution 6	Joel H
Solution 7	Ali Raza
Solution 8
Solution 9	Juraj Bublinec
Solution 10	Bohdan Yurchuk
Solution 11	zhyp
Solution 12	zanthez
Solution 13
Solution 14	Roman Tselenko
Solution 15	bguiz

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

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]

Solution 14:[14]

Solution 15:[15]