当Node.js第一次连接失败时，如何重试数据库连接？

Question

我正在使用Node.js与SQL Server。如果第一次连接失败，Node.js不会重新尝试连接。我使用setTimeout()定期重试直到连接成功。

const poolPromise = new sql.ConnectionPool(config.db);
poolPromise
  .connect()
  .then(pool => {
    console.log('Connected to MSSQL');
    return pool;
  })
  .catch(err => {
    if (err && err.message.match(/Failed to connect to /)) {
      console.log(new Date(), String(err));

      // Wait for a bit, then try to connect again
      setTimeout(function() {
        console.log('Retrying first connect...');
        poolPromise.connect().catch(() => {});
      }, 5000);
    } else {
      console.error(new Date(), String(err.message));
    }
  });

上述代码尝试连接，失败后第二次尝试，但不会继续进行第三、第四等尝试。

- Maihan Nijat

---

为什么你想尝试重新连接而不是检查配置是否有误呢？重新连接能解决什么问题？ - DaCurse

@DaCurse 当服务器重新启动时，首先运行节点实例，但它尝试失败，因为 SQL Server 尚未准备好。几秒钟后，SQL Server 准备好了。 - Maihan Nijat

请参见 https://dev59.com/IF8d5IYBdhLWcg3wkSwV。 - danh

使用async/await和循环。 - Ry-

是的，如果你是通过docker-compose运行的话，这是最理想的。 - John

Answer 1

我写了这个小片段，它可以工作。我将连接部分封装成一个函数，并使用递归函数调用它。

在这个例子中，您将看到一个无穷大的数字。

function sql() {
    this.connect = function() {
        return new Promise((resolve, reject) => reject("error connecting"));
    }
}


function connect() {
    return new Promise((resolve, reject) => {
        // const poolPromise = new sql.ConnectionPool("config.db");
        const poolPromise = new sql();
        poolPromise
            .connect()
            .then(pool => {
                console.log("connected");
                resolve(pool);
            })
            .catch(err => {
                console.error(err);
                reject(err);
            });
    });
}

function establishConnection() {
     var a = connect();
     a.then(a => console.log("success"))
    .catch(err => {
        console.error("Retrying");
        // I suggest using some variable to avoid the infinite loop.
        setTimeout(establishConnection, 2000);
    });
};

establishConnection();

- Marcelo Vismari

Answer 2

在查看了这里的答案后，我同意回调函数是正确的方法。我编写了以下脚本来尝试连接到MySQL，直到连接建立，然后偶尔检查连接是否仍然有效，如果无效，则再次尝试连接。我在几个地方放置了console.log，以便在运行时您可以看到和理解发生了什么。

var mysql = require('mysql');
var env = require('dotenv').config()

// ENVIRONMENT LOADS
var env = process.env.NODE_ENV.trim();
var host = process.env.MYSQL_HOST.trim();
var user = process.env.MYSQL_USER.trim();
var password = process.env.MYSQL_ROOT_PASSWORD.trim();
var database = process.env.MYSQL_DB.trim();
var port = process.env.MYSQL_PORT.trim();

console.log('\n\n********\n\nMySQL Credentials\n\n********\n\n');
if (env != 'production') {
  console.log("Host: ", host, ":", port);
  console.log("User: ", user);
  console.log("Database: ", database);
  console.log("Password: ", password);
}else{
  console.log('Using Production Credentials');  
}
console.log('\n\n************************\n\n');

let mysqlDB = null; // db handler
let connected = null; // default null / boolean
let connectFreq = 1000; // When database is disconnected, how often to attempt reconnect? Miliseconds
let testFreq = 5000; // After database is connected, how often to test connection is still good? Miliseconds

function attemptMySQLConnection(callback) {
  console.log('attemptMySQLConnection')
  if (host && user && database) {

    mysqlDB = mysql.createPool({
      host: host,
      port: port, // Modified for Dev env
      user: user,
      password: password,
      database: database,
      connectionLimit: 300,
      waitForConnections: true, // Default value.
      queueLimit: 300, // Unlimited
      acquireTimeout: 60000,
      timeout: 60000,
      debug: false
    });

    testConnection((result) => {
      callback(result)
    })

  } else {
    console.error('Check env variables: MYSQL_HOST, MYSQL_USER & MYSQL_DB')
    callback(false)
  }
}

function testConnection(cb) {
  console.log('testConnection')
  mysqlDB.query('SELECT 1 + 1 AS solution', (error, results, fields) => {
    try {
      if (error) {
        throw new Error('No DB Connection');
      } else {
        if (results[0].solution) {
          cb(true)
        } else {
          cb(false)
        }
      }
    } catch (e) {
      // console.error(e.name + ': ' + e.message);
      cb(false)
    }
  });
}

function callbackCheckLogic(res) {
  if (res) {
    console.log('Connect was good. Scheduling next test for ', testFreq, 'ms')
    setTimeout(testConnectionCB, testFreq);
  } else {
    console.log('Connection was bad. Scheduling connection attempt for ', connectFreq, 'ms')
    setTimeout(connectMySQL, connectFreq);
  }
}

function testConnectionCB() {
  testConnection((result) => {
    callbackCheckLogic(result);
  })
}

function connectMySQL() {
  attemptMySQLConnection(result => {
    callbackCheckLogic(result);
  });
}

connectMySQL(); // Start the process by calling this once

module.exports = mysqlDB;

- John