Azure WebApp中MySQL连接池报ETIMEDOUT但普通连接正常问题排查

将代码部署在Azure WebApp上时，普通连接（每次请求创建并关闭）可正常连接数据库，但使用连接池时出现超时错误：

--inside authoriseUser--
Error occurred while querying database: Error: connect ETIMEDOUT [DB IP address]:3306
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1555:16) {
  errno: -4039,
  code: 'ETIMEDOUT',
  syscall: 'connect',
  address: 'DB IP address',
  port: 3306,
  fatal: true
}

旧代码运行正常但耗时较高，想改用连接池优化性能，以下是相关代码：

旧代码（正常运行） #

async authoriseUser(memberEmail, context) {
    console.log("--inside authoriseUser--");
    return new Promise(async (resolve, reject) => {
        try {
            var authUserStart = performance.now();
            const config = {
                user: '<username>',
                password: '<password>',
                server: '<servername>',
                database: '<databasename>',
                options: {
                    encrypt: true,
                    enableArithAbort: true
                }
            };
            await sql.connect(config);
            const result = await sql.query`SELECT TOP 1 col1 FROM tablename WHERE UserID = ${memberEmail}`;
            var authUserEnd = performance.now();
            if (result.recordset.length > 0) {
                console.log("User found in database");
                var authUserTime = (authUserEnd - authUserStart) / 1000;
                console.log("authUserEnd TIme:", authUserTime)
                resolve(memberEmail);
            } else {
                console.log("User not found in database");
                resolve(false);
            }
        } catch (error) {
            console.error("Error occurred while querying database:", error);
            reject(new Error('DatabaseQueryError'));
        } finally {
            await sql.close();
        }
    });
}

连接池实现代码（报错） #

const mysql = require('mysql2/promise');
const pool = require('./db');

//rest of code

async authoriseUser(memberEmail, context) {
    console.log("--inside authoriseUser--");
    return new Promise(async (resolve, reject) => {
        let connection;
        try {
            var authUserStart = performance.now();
            connection = await pool.getConnection();
            const [rows] = await connection.query('SELECT TOP 1 col1 Table WHERE UserID = ?', [memberEmail]);
            var authUserEnd = performance.now();

            if (rows.length > 0) {
                console.log("User found in database");
                var authUserTime = (authUserEnd - authUserStart) / 1000;
                console.log("authUserEnd Time:", authUserTime);
                resolve(memberEmail);
            } else {
                console.log("User not found in database");
                resolve(false);
            }
        } catch (error) {
            console.error("Error occurred while querying database:", error);
            reject(new Error('DatabaseQueryError'));
        } finally {
            if (connection) connection.release();
        }
    });
}

db.js配置 #

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: '<hostname>',
    user: '<username>',  
    password: '<password>',       
    database: '<databasename>',   
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    connectTimeout: 30000,
  //  ssl: {
  //      rejectUnauthorized: true
  //  }
});

module.exports = pool;

疑问：为何普通连接正常但连接池不行？哪里操作有误？

1. 核心错误：数据库驱动不匹配 #

旧代码用的是mssql驱动（从sql.connect、encrypt: true配置及SQL Server特有的TOP 1语法可判断），但连接池代码却改用了mysql2驱动——这是两个完全不同的驱动，分别对应SQL Server和MySQL数据库。你实际连接的是Azure SQL Server，用mysql2驱动自然连不上，触发超时。

修复：改用mssql库实现连接池，而非mysql2。

2. 连接池缺少加密配置 #

Azure SQL Server强制要求加密连接，旧代码中明确设置了encrypt: true，但你的db.js注释掉了SSL配置，导致连接池无法建立合法加密连接，引发超时。

如果用mssql连接池，需在配置中保留encrypt: true；如果确实是MySQL数据库，需取消注释SSL配置并匹配Azure MySQL的SSL规则。

3. SQL语句语法错误 #

连接池代码中的SQL语句存在语法问题：

SELECT TOP 1 col1 Table WHERE UserID = ?

缺少FROM关键字，正确写法应为：

SELECT TOP 1 col1 FROM Table WHERE UserID = ?

（注：如果是MySQL，需用LIMIT 1替代TOP 1，这也进一步验证了驱动不匹配的问题）

4. 冗余的Promise包装 #

authoriseUser本身已是async函数，无需手动包裹new Promise，代码可简化为：

async authoriseUser(memberEmail, context) {
    console.log("--inside authoriseUser--");
    let connection;
    try {
        const authUserStart = performance.now();
        connection = await pool.getConnection();
        const [rows] = await connection.query('SELECT TOP 1 col1 FROM tablename WHERE UserID = ?', [memberEmail]);
        const authUserEnd = performance.now();

        if (rows.length > 0) {
            console.log("User found in database");
            const authUserTime = (authUserEnd - authUserStart) / 1000;
            console.log("authUserEnd Time:", authUserTime);
            return memberEmail;
        } else {
            console.log("User not found in database");
            return false;
        }
    } catch (error) {
        console.error("Error occurred while querying database:", error);
        throw new Error('DatabaseQueryError');
    } finally {
        if (connection) connection.release();
    }
}

正确的mssql连接池示例 #

针对Azure SQL Server，推荐使用mssql官方连接池：

// db.js
const sql = require('mssql');

const pool = new sql.ConnectionPool({
    user: '<username>',
    password: '<password>',
    server: '<servername>',
    database: '<databasename>',
    options: {
        encrypt: true,
        enableArithAbort: true
    },
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    }
});

// 初始化连接池
pool.connect().catch(err => console.error('连接池初始化失败:', err));

module.exports = pool;

业务代码中使用：

const pool = require('./db');

async authoriseUser(memberEmail, context) {
    console.log("--inside authoriseUser--");
    try {
        const authUserStart = performance.now();
        const result = await pool.query`SELECT TOP 1 col1 FROM tablename WHERE UserID = ${memberEmail}`;
        const authUserEnd = performance.now();

        if (result.recordset.length > 0) {
            console.log("User found in database");
            const authUserTime = (authUserEnd - authUserStart) / 1000;
            console.log("authUserEnd Time:", authUserTime);
            return memberEmail;
        } else {
            console.log("User not found in database");
            return false;
        }
    } catch (error) {
        console.error("Error occurred while querying database:", error);
        throw new Error('DatabaseQueryError');
    }
}

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