await and async don't work in the recursive function

Question

I'm trying to connect the code to MySQL. When the connection fails in first attempt, I want it to reattempt to connect. So, This code is used recursive function.

This code contains DB connect process.

const mysql = require('mysql');
let connection;
let cnt = 0;


export const cm_dbconnect =  async() => {

    let result = false;

  
    var dbInfo = {
      host     : '127.0.0.1',
      user     : '****',
      password : '**********'
    }

    let db =  mysql.createConnection(dbInfo);
    
    const attemptConnection = async() => {
    console.log('Attempting to connect to db')
   
    
    
    db.connect(async function (err) {
      if (err) {
        
        cnt += 1;
        console.log('Error connecting to database, try again in 1 sec...')
        db.destroy() 
        if (cnt<5) {
        console.log(cnt);
        
        function settimeoutpromise() {
            return new Promise((resolve, reject) => {
                setTimeout(() => {
                cm_dbconnect();
                resolve("end")
            }, 1000);
        })}
        await settimeoutpromise();
    
        } 
      } 
    })
    
    
  }
  await attemptConnection()

            
  
  module.exports  = db;
  result = true
  return  {result: result, errCode: "", errDetail: ""};


}

And this code contains controller.

export const Controller = async () => {
   

    let connect = await cm_dbconnect();
          
    let db = require('');
    let sql = 'select *** from ***;';
    db.query(sql, function (error, results, fields) {
    console.log(results);
      
    })
    
    console.log("aaaa");
    console.log(connect);


       }
let test = Controller();

However, the result is returned before the retry is finished. I think this is why await/async doesn't work.

Attempting to connect to db
aaaa
{ result: true, errCode: '', errDetail: '' }
Error connecting to database, try again in 1 sec...
1
undefined
Attempting to connect to db
Error connecting to database, try again in 1 sec...
2
Attempting to connect to db
Error connecting to database, try again in 1 sec...
3
Attempting to connect to db
Error connecting to database, try again in 1 sec...
4
Attempting to connect to db
Error connecting to database, try again in 1 sec...

I want to get return value after retrying if the 5 times connection fails. please tell me solution for this.

Answer 1

You can change the way your are creating the connection :

const mysql = require('mysql');

const cm_dbconnect =  async() => {
  
    var dbInfo = {
      host     : '127.0.0.1',
      user     : '****',
      password : '**********'
    }

    let db =  mysql.createConnection(dbInfo);
  
    try
    {
              await new Promise((resolve, reject) => {
            db.connect(err => {
                return err ? reject(err) : resolve({status: 'OK', message: 'Connected to DB'})
              })
           })
           
          
    }
    catch(err)
    {
        ...handle errors...
    }

  module.exports  = cm_dbconnect;

In your controller:

const cm_dbconnect = require('path')
export const Controller = async () => {
   
    let connect = await cm_dbconnect();
    let count;
// already called once 
    while( connect.err && count < 4) {
        count++;
        connect = await cm_dbconnect();
    }
    
   if( connect.err && count >=4) return 'DB not connected at the moment'
  
    console.log(`Connected with DB after this many tries`);
          
    let db = require('');
    let sql = 'select *** from ***;';
    db.query(sql, function (error, results, fields) {
    console.log(results);
      
    })
    
    console.log("aaaa");
    console.log(connect);


       }
let test = Controller();

I have changed the DB connection to get promise based on connection, in the controller we are checking what is being returned.

You can also check this thread, it has good way to solve the problem too.