I am trying to write a retry logic to connect to AWS Aurora db for mysql. Aurora db can sleep if it is left idle for a specified amount of time and performs a cold restart upon receiving a request. The restart can take 30-50sec and to establish a connection, I am doing three reattempts in the code below.

To test the wait times on my local system, I am trying to simulate the sleep scenario by setting wrong port number when connection pool is being created. And the code is throwing an error as expected.

Error: connect ECONNREFUSED 127.0.0.1:3305 (3306 is the correct one.)

{
  errno: -4078,
  code: 'ECONNREFUSED',
  syscall: 'connect',
  address: '127.0.0.1',
  port: 3305,
  fatal: true
}

I invoke initConnection() to create the pool and then use it to query the db. The code should throw the exception but it should throw it after 3 reattempts. But this is not happening. The code is throwing error when the code written for testing the connection is invoked. ('SHOW DATABASES;'). The code is not trying to reattempt the connection.

Can someone help point out the issue in this code and how it can be corrected?

const mysql = require("mysql");

export class DatabaseConnectionFactory {
    private cPool: any;
    private cPoolInit: boolean = false;

    public async initConnection() {
        try {
            await this.createPool();
        } catch (err) {
            console.log(err);
        }
    }

    private async createPool(attempt: number = 1) {
        return new Promise(async (resolve, reject) => {
            try {
                if(!this.cPoolInit){
                    this.cPool = mysql.createPool({
                        connectionLimit: 500,
                        host:"mysqlHost",
                        port: "mysqlPort",
                        user: "mysqlUser",
                        password: "mysqlPassword"
                    });
                    
                    // Test Connection
                    this.cPool.query('SHOW DATABASES;', null, (err, rows) => {
                        if (err){
                            throw err; // APP THROWS ERROR AT THIS LINE AND EXITS
                        }
                        console.log('Test Connection, Successful.');
                    });
                    this.cPoolInit = true;
                }
                resolve('Created Connection Pool.');
            } catch(err) {
                console.log(err);
                console.log("Reattempting connection.");
                await this.reattemptConnection(attempt);
                reject('Unable to Create Connection Pool.');
            }
        });
    }

    private wait(seconds: number) {
        const ms = 1000 * seconds;
        return new Promise(resolve => setTimeout(resolve, ms));
    }

    private async reattemptConnection(reattempt: number) {
        switch(reattempt) {
            case 1: {
                console.log('Reattempt 1');
                await this.wait(30);
                await this.createPool(reattempt   1);
                break;
            }
            case 2: {
                console.log('Reattempt 2');
                await this.wait(20);
                await this.createPool(reattempt   1);
                break;
            }
            case 3: {
                console.log('Reattempt 3');
                await this.wait(10);
                await this.createPool(reattempt   1);
                break;
            }
            default:{
                break;
            }
        }
    }
}

CodePudding user response：

Not able to correctly test but something like this:

// const mysql = require("mysql");
import * as mysql from "mysql";

export class DatabaseConnectionFactory {
  private cPool: any;
  private cPoolInit: boolean = false;

  private testConnection(): Promise<void> {
    return new Promise<void>((resolve, reject) => {
      this.cPool.query("SHOW DATABASES;", null, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          console.log("Test Connection, Successful.");
          resolve();
        }
      });
    });
  }

  public async createPool(attempt: number = 0): Promise<void> {
    if (this.cPoolInit) return;

    try {
      this.cPool = mysql.createPool({
        connectionLimit: 500,
        host: "mysqlHost",
        port: 3305,
        user: "mysqlUser",
        password: "mysqlPassword"
      });
      await this.testConnection();
      this.cPoolInit = true;
      console.log("Created Connection Pool.");
    } catch (err) {
      console.log(err);
      console.log("Reattempting connection.");
      try {
        await this.reattemptConnection(attempt);
      } catch (e) {
        throw new Error("Unable to Create Connection Pool.");
      }
    }
  }

  private wait(delayMs: number): Promise<void> {
    return new Promise((resolve) => setTimeout(resolve, delayMs));
  }

  private async reattemptConnection(reattempt: number) {
    const delaysMs = [30 * 1000, 20 * 1000, 10 * 1000];
    if (reattempt < 0 || reattempt >= delaysMs.length) {
      throw new Error("Out of attempts");
    }

    console.log("Reattempt: "   reattempt.toString());
    await this.wait(delaysMs[reattempt]);
    await this.createPool(reattempt   1);
  }
}

All you had to do:

1. Promisify the .query callback
2. (simplify/clear/fix code a bit)
3. Add a try-catch to your reattempt method call due to in your case if connection is established and nothing is thrown - you will still get reject('Unable to Create Connection Pool.');