Check connection = mysql nodejs

I use node js with mysql and want to avoid that the app crash on connection errors.At the moment i use this :

function mysql_handleDisconnect() {
  mysql_connection = mysql.createConnection(mysql_config_obj); // Recreate the connection, since
                                                  // the old one cannot be reused.

  mysql_connection.connect(function(err) {              // The server is either down
    if(err) {                                     // or restarting (takes a while sometimes).
      console.log('error when connecting to db:', err);
      mysql_handleDisconnect(); // We introduce a delay before attempting to reconnect,
    }                                     // to avoid a hot loop, and to allow our node script to
  });                                     // process asynchronous requests in the meantime.
                                          // If you're also serving http, display a 503 error.
  mysql_connection.on('error', function(err) {
    console.log('db error', err);
    if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
      mysql_handleDisconnect();                         // lost due to either server restart, or a
    } else {                                      // connnection idle timeout (the wait_timeout
      throw err;                                  // server variable configures this)
    }
  });
}

 mysql_handleDisconnect(mysql_connection);

so this is blocking because it leads to a hot loop if the connection is closed.my problem is, if i add a setTimeout to reestablish connection just every 2 seconds i could get an fatal error when i do a query with "mysql_connection.query('SELECT ...')".in this case the app crashes.

So my question is,if there's a possibility to check the connection before i do a query?

asked Mar 26, 2015 at 11:13

3

Try using below code in every microservice before doing anything:

 if(connection.state === 'disconnected'){
     return respond(null, { status: 'fail', message: 'server down'});
   }

State of connection to DB could fall in 2 states:

  1. disconnected (when due to DB server down or wrong config use for DB connection is wrong)
  2. authenticated (when DB connection is successfully created to DB server).

So either check state == 'disconnected' or state == 'authenticated'

Check connection = mysql nodejs

herve

3,6262 gold badges16 silver badges27 bronze badges

answered Mar 13, 2017 at 11:09

Pranay SahaPranay Saha

2983 silver badges12 bronze badges

1

I know this is an old question but I have found connection.ping( (err) => {...}) to be very useful for health-checks made from load balancers and whatnot.

answered Mar 26, 2019 at 13:30

MrfksIVMrfksIV

8506 silver badges16 bronze badges

Every time, while I'm pushing my code in production, the mysql connection is lost. It is a very common problem in production, or local.
My solution is that At every query established the db connection and remove connection after completing the db query. My solution is to establish the db connection before every query, and then remove the connection after completing the db query.

Step1: Here is the code for dbConnection.js

//this code is for conenct to db
const mysql = require('mysql2');
require('dotenv').config();
module.exports.stablishedConnection = ()=>{
return new Promise((resolve,reject)=>{
  const con = mysql.createConnection( {
    host: process.env.DB_HOST||localhost,
    user: process.env.DB_USER_NAME||myUserName ,
    password: process.env.DB_PASSWORD||mypassword,
    database: process.env.DB_NAME||mydb
  });
  con.connect((err) => {
    if(err){
      reject(err);
    }
    resolve(con);
  });
  
})
}
module.exports.closeDbConnection =(con)=> {
  con.destroy();
}

Step2: For Router.js I am import the db connection and handle the promise

const router = require('express').Router();
const {stablishedConnection,closeDbConnection}  =require('../db/dbConnection');

router.get('/user/:sId/:userId',function(req,res){
  stablishedConnection()
  .then((db)=>{
    console.log("Db connection stablished");
    db.query(`select * from user WHERE  sent_id=${req.params.sId} AND user_id=${req.params.userId}`, null, function (err,data) { 
      if (!data) {
        res.status(200).json({sucess:false,err});
      }else{ 
        res.status(200).json({sucess:true,data});
        closeDbConnection(db);
         console.log("Db Connection close Successfully");
      }
  })                         
}).catch((error)=>{
  console.log("Db not connected successfully",error);
}); 
  
 
});
router.get('/sen/:userId',function(req,res){
  stablishedConnection()
  .then((db)=>{
    console.log("Db connection stablished");
    db.query(`select * from sen WHERE  user_id=${req.params.userId}`, null, function (err,data) { 
      if (!data) {
        res.status(200).json({sucess:false,err});
      }else{
        res.status(200).json({sucess:true,data});
        closeDbConnection(db);
        console.log("Db Connection close Successfully");
      }
  })                         
}).catch((error)=>{
  console.log("Db not connected successfully",error);
});   
});
router.get('/language',(req,res)=>{
  stablishedConnection()
  .then((db)=>{
    console.log("Db connection stablished");
    db.query("select * from language", null, function (err,data) { 
      if (!data) {
        res.status(200).json({sucess:false,err});
      }else{
        res.status(200).json({sucess:true,data});
        closeDbConnection(db);
         console.log("Db Connection close Successfully")
      }
  })                         
}).catch((error)=>{
  console.log("Db not connected successfully",error);
}); 
})

module.exports = router;

This is perfectly run If you want to create and close connection at every query ..

answered May 2, 2020 at 1:55

Check connection = mysql nodejs

I solved this problem like this:

let connection = mysql.createConnection(DB_CONFIG);
function runDBQuery() {
  const disconnected = await new Promise(resolve => {
    connection.ping(err => {
      resolve(err);
    });
  });
  if (disconnected) {
    connection = mysql.createConnection(DB_CONFIG);
  }
  ... use actual connection
}

answered Sep 30, 2021 at 20:41

kolserdavkolserdav

1341 silver badge9 bronze badges

How do you check database is connected or not in node JS?

We will see how to check if the server where our MySQL Database is Hosted is Active or Not. Syntax: database_connection. ping(callback);

How does node js handle MySQL connection?

To download and install the "mysql" module, open the Command Terminal and execute the following:.
C:\Users\Your Name>npm install mysql..
var mysql = require('mysql');.
Run "demo_db_connection.js" C:\Users\Your Name>node demo_db_connection.js..
Connected!.
con. connect(function(err) { if (err) throw err; console..

How do you check is MySQL connected or not?

To check the database connectivity, you must know the database hostname, normally it is “localhost” then database-name, username and password. If the DB is hosted on a remote server [remote DB server], change the host to that server's hostname. Also the connectivity from this machine to DB machine should be enabled.

CAN node JS connect to MySQL?

If you want to connect to a MySQL database using JavaScript, you can use Node. js and a library called mysql.