1

I get the following error when connecting to knex: KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

Here's my code:

Api.js

const Knex = require('knex');
const config = require('./config');
const configuration = {
user: config.config.sqlUser, // e.g. 'my-user'
password: config.config.sqlPw, // e.g. 'my-user-password'
database: config.config.sqlDbName, // e.g. 'my-database'
};
configuration.host = `${config.config.sqlConnectionName}`;
const knex = Knex({client: 'pg', connection: configuration});
knex.client.pool.max = 5;
knex.client.pool.min = 5;
knex.client.pool.createTimeoutMillis = 30000; // 30 seconds
knex.client.pool.idleTimeoutMillis = 600000; // 10 minutes
knex.client.pool.createRetryIntervalMillis = 200; // 0.2 seconds
knex.client.pool.acquireTimeoutMillis = 600000; // 10 minutes



router.get('/things', async (req, res) =>{
 
   await methods.getThings(req, res, knex);
});

methods.js:

exports.getThings = async (req, res, knex) => {
let response = {};
try{
    console.log("knex.client.pool.max");
    console.log(knex.client.pool.max);
    response = await knex.select('id', 'userUid', 'firstName', 'lastName', 'cv', 'statement', 'country', 'represented').from('things').where('approved',true)
}
catch (err){
    console.log("error: ", err);
    return res.status(500).json(err);
}
return res.status(200).json(response)
}

I'm using these: Node v14.0.0 pg "8.7.1" knex "0.95.14"

Seems like it's a problem with creating connection (30s timeout in logs) to cloud sql. How can I create the connection properly? Should I use cloud-proxy and how?

I have a startup script in VM that starts a node express server.

samuq
  • 306
  • 6
  • 16

3 Answers3

2

This error can mean many things, but that's where to start: Firstly it may also result from a typo in your database host name, So check your credentials twice! the attribute propagateCreateError should be set to false to prevent the Timeout acquiring a connection. The pool is probably full. Try to add this line to Your pool configuration. Also change the min and max e.g. 2-6 Good Luck!

knex.client.pool.propagateCreateError = false;

In addition, I found two interesting sources that you probably should read 1 2

MarioG8
  • 5,122
  • 4
  • 13
  • 29
2

If you're running this app on a VM, you'll need to also run the Cloud SQL Auth Proxy as a separate process.

You'd start the proxy with something like this:

cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432

Then your app can connect on 127.0.0.1 (instead of the sqlConnectionName).

enocom
  • 1,496
  • 1
  • 15
  • 22
  • How do I run the Cloud SQL Auth Proxy as a separate process? It's currently breaking my startup script (linux bash). – samuq Dec 15 '21 at 19:40
  • The simplest thing to do in a VM context is to just run the proxy as a background process by adding a `&` after the proxy command. This isn't production worthy though. So you might look at running the proxy and your app with systemd. See https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/4#issuecomment-768069608 for example. – enocom Dec 15 '21 at 20:01
  • I'm doing exactly as you described, but now the connection throws error: "permission denied for table 'things'". What does that mean and how to fix it? – samuq Dec 16 '21 at 07:12
  • I managed to get the connection work using private ip of the sql instance instead of 127.0.0.1. – samuq Dec 16 '21 at 08:01
  • A permission error from the database means your user doesn't have the appropriate database permissions. Try connecting with a different user or verifying your database user permissions. – enocom Dec 16 '21 at 17:24
0
"express": "^4.16.2",
"knex": "^0.14.2",
"objection": "^2.1.3",
"pg": "^8.0.3",

and npm install i fixed my problem (end of the 4 day)

zanthez
  • 45
  • 6