在Compute Engine的Node.js应用中用Knex连接Cloud SQL超时问题

在Compute Engine上运行Node.js应用时，无论是否搭配Cloud SQL Auth Proxy（已在启动脚本中安装并启动），使用Knex连接Cloud SQL都会触发KnexTimeoutError：

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

以下是相关代码文件：

启动脚本 #

set -v

# Talk to the metadata server to get the project id
PROJECTID=$(curl -s "http://metadata.google.internal/computeMetadata/v1/project/project-id" -H "Metadata-Flavor: Google")
REPOSITORY="..."
# CMS="..."

# Install logging monitor. The monitor will automatically pick up logs sent to
# syslog.
curl -s "https://storage.googleapis.com/signals-agents/logging/google-fluentd-install.sh" | bash
service google-fluentd restart &

# Install dependencies from apt
apt-get update
apt-get install build-essential

apt-get install -yq ca-certificates git supervisor postgresql-client
apt-get install --yes liblcms2-2 liblcms2-dev liblcms2-utils

# Install nodejs
rm -r /opt/nodejs
mkdir /opt/nodejs
curl https://nodejs.org/dist/v14.0.0/node-v14.0.0-linux-x64.tar.gz | tar xvzf - -C /opt/nodejs --strip-components=1
ln -s -f /opt/nodejs/bin/node /usr/bin/node
ln -s -f /opt/nodejs/bin/npm /usr/bin/npm
# Get the application source code from the Google Cloud Repository.
# git requires $HOME and it's not set during the startup script.
export HOME=/root
rm -r /opt/app
# rm -r /cms
git config --global credential.helper gcloud.sh
git clone https://source.developers.google.com/p/${PROJECTID}/r/${REPOSITORY} /opt/app


# Install app dependencies
cd /opt/app
git checkout development

npm install
npm install bunyan @google-cloud/logging-bunyan 
npm install sharp@0.29.0




# Create a nodeapp user. The application will run as this user.
cd /
cd /home/app
userdel -f app
cd /
rm -r /home/app
cd /opt/app
useradd -m -d /home/app app
chown -R app:app /opt/app
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.3.0/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy
export GOOGLE_APPLICATION_CREDENTIALS=/service-account-key/key.json
export INSTANCE_HOST='<public-ip>'
export DB_PORT='5432'
export DB_USER=’...’
export DB_PASS=’...’
export DB_NAME=’...’
./cloud_sql_proxy -instances=dreamlouvre:europe-west1:dream-louvre-sql=tcp:5432 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS &
# Configure supervisor to run the node app.
rm /etc/supervisor/conf.d/node-app.conf
cat >/etc/supervisor/conf.d/node-app.conf << EOF
[program:app]
directory=/opt/app
command=npm start
autostart=true
autorestart=true
user=app
environment=HOME="/home/app",USER="app",NODE_ENV="development",SQL_CONNECTION_NAME="...",SQL_DB_NAME="...",SQL_USER="...",SQL_PW="...",
stdout_logfile=syslog
stderr_logfile=syslog
EOF
supervisorctl reread
supervisorctl update
# Application should now be running under supervisor

Knex实例配置脚本 #

exports.initKnex = () => {

    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 = 1;
    knex.client.pool.min = 1;
    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
    return knex;
} 

触发错误的接口代码 #

exports.getCurrency = async (req, res) => {

    let response = {};
    try{
        response = await knex('exchangeRates').where({'countryCode': req.params.cc}).select('currencyCode', 'rate', 'digits');
        //console.log("RESPONSE: ", response);
      }
    catch (err){
        console.log("error: ", err);
        return res.status(500).json(err);
    }
    return res.status(200).json({'data':response});

}

1. 修正Knex连接地址配置 #

当前Knex把Cloud SQL实例连接名直接设为host是错误的，需根据连接方式调整：

• 使用Cloud SQL Auth Proxy时，代理监听本地127.0.0.1:5432，host应设为127.0.0.1
• 直接用公网IP连接时，host应设为Cloud SQL的公网IP（即启动脚本中的<public-ip>）

同时把连接池配置整合到Knex初始化参数中，避免事后修改可能的失效：

exports.initKnex = () => {
    const configuration = {
        user: config.config.sqlUser,
        password: config.config.sqlPw,
        database: config.config.sqlDbName,
        port: 5432 // 显式指定端口
    };
    // 二选一：使用代理则用127.0.0.1，直接连公网则替换为Cloud SQL公网IP
    configuration.host = '127.0.0.1';
    // configuration.host = '<your-cloud-sql-public-ip>';
    
    const knex = Knex({
        client: 'pg',
        connection: configuration,
        pool: {
            max: 5, // 调高连接池上限，1个连接极易被占满
            min: 2,
            createTimeoutMillis: 30000,
            idleTimeoutMillis: 600000,
            createRetryIntervalMillis: 200,
            acquireTimeoutMillis: 600000
        }
    });
    return knex;
} 

2. 检查Cloud SQL Auth Proxy状态与权限 #

• 执行ps aux | grep cloud-sql-proxy确认代理是否正常运行，若未启动，检查服务账号密钥文件/service-account-key/key.json是否存在、权限是否正确（该账号需拥有Cloud SQL Client角色）
• 核对代理启动命令中的实例连接名格式：项目ID:地区:实例名，必须与实际一致

3. 验证网络连通性 #

• 使用代理时，在GCE实例上执行psql -h 127.0.0.1 -U <DB_USER> -d <DB_NAME>测试本地连接是否正常
• 直接用公网IP时，确保Cloud SQL授权网络已添加GCE实例的公网IP，再执行psql -h <public-ip> -U <DB_USER> -d <DB_NAME>测试连通性

4. 修复Supervisor环境变量配置 #

启动脚本中Supervisor的environment末尾多了一个逗号，会导致环境变量加载异常，需删除：

# 错误配置
environment=HOME="/home/app",USER="app",NODE_ENV="development",SQL_CONNECTION_NAME="...",SQL_DB_NAME="...",SQL_USER="...",SQL_PW="...",

# 修正后
environment=HOME="/home/app",USER="app",NODE_ENV="development",SQL_CONNECTION_NAME="...",SQL_DB_NAME="...",SQL_USER="...",SQL_PW="..."

内容的提问来源于stack exchange，提问作者samuq