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

Question

我正在使用SQL服务器与Node.js.当连接首次尝试失败时，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));
    }
  });

上面的代码尝试连接，失败并尝试第二次，但不会继续第三次、第四次等。

Answer 1

在检查了这里的答案后，我同意回电是一种方式。我编写了以下脚本，试图连接到MySQL，直到连接建立，然后偶尔检查连接是否仍然有效，如果无效，请再次尝试连接。我放置了控制台。日志在一些地方，这样当事情运行时，你可以看到并理解正在发生的事情。

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;

Answer 2

我写了这个有效的小片段。我将连接部分包装到一个函数中，然后使用递归函数调用它。

在这个例子中，你会看到一个无穷大。

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