[Node.js] MySql PROTOCOL_CONNECTION_LOST 해결

MySql을 통해 DB로부터 데이터를 가져오는 API를 개발했다.

앱과 연동 후 하루정도 이후에 다시 앱에서 호출을 해봤는데

서버에 응답이 없는 문제가 발생했었다.

 

당장 서버 측 콘솔을 확인해보니 PROTOCOL_CONNECTION_LOST라는 메시지와 함께

MySql의 연결이 죽어있던 것이다.

 

원인은 MySql 자체에서 기본 타임아웃 시간이 28800으로 인해 발생한 것이었다.

약 8시간 정도 아무런 요청이 없다면 모든 연결을 종료하는 것 같았다.

 

이전에 닷넷 개발을 통해 MySql을 사용했을 때는

쿼리 요청을 사용할 때만 Connection을 연결하고 끊고, 연결하고 끊고를 반복하다 보니 이러한 문제는 없었다. 하지만 NodeJs에서 구현한 방법은 js파일이 require 될 때 Connection 연결을 시작하고, 그 상태를 유지하여

이후에 쿼리 요청이 발생하면 기존에 선언된 connection 객체를 통해 쿼리 요청을 하는 것 이기 때문에 28800초(8시간) 동안 아무런 요청이 없다면 연결을 잃고 오류가 발생하는 것이다.

실제로 서버 timeout시간을 조절하여 실험을 진행해 본 후 깨달은 결과다.

아래는 mysql에서 사용한 timeout 시간 조절 쿼리문이다.

set global wait_timeout = 10; /* API,RDBMS 등에서 연결을 유지하는 seconds 를 결정 */

show variables like 'wait_timeout'; /* 설정된 seconds값 확인*/

참고 문서: https://kimdubi.github.io/mysql/timeout/

 

 

이 문제를 해결하기 위해 나는 3가지 방안을 생각을 했다.

첫 번째는 서버 측에서 커넥션을 계속 유지하기 위해 더미 쿼리를 1시간 간격으로 보내주도록 하는 방법.

두 번째는 닷넷 개발 때와 비슷하게 컨트롤러에서 요청이 있을 때마다 connection객체를 생성하는 방법.

세 번째는 연결이 해제되는 경우 알아서 다시 연결을 시도하는 방법.

 

첫 번째 방법은 서버에 불필요한 작업을 주는 것 같아서 보류하였고

두 번째 방법은 비동기의 제국인 nodejs에서는 그다지 좋은 사용법은 아닌 것 같았다. (또한 매번 쿼리를 요청, 종료를 반복하는 것도 서버 부하에 영향을 끼칠 것 같기도 했다)

그래서 세 번째 방법을 선택하게 되었다.

 

세 번째 방법을 구현하기 위해 아래와 같이 코드를 사용했다.

 

const mysql = require('mysql');
const fs = require('fs');

const data = fs.readFileSync('./db_options.json');
const conf = JSON.parse(data);

const db_config =  {  //db에 대한 config 정의
    host : conf.host,
    user : conf.user,
    password : conf.password,
    port : conf.port,
    database : conf.database,
    timezone : "Asia/Seoul",
}

var connection;

function handleDisconnect() { //함수 정의
  connection = mysql.createConnection(db_config);
                                            

  connection.connect(function(err) {
    console.log('DBConnected!');
    if(err) {
      console.log('error when connecting to db:', err);
      setTimeout(handleDisconnect, 2000); //연결 실패시 2초 후 다시 연결
    }
  });

  connection.on('error', function(err) {
    if(err.code === 'PROTOCOL_CONNECTION_LOST') {
      console.log('MySql_DBError) PROTOCOL_CONNECTION_LOST');
      handleDisconnect(); //연결 오류시 호출하는 재귀함수
    } else {
      console.log('MySql_DBError)', err);
      throw err;
    }
  });
}

handleDisconnect(); //require과 동시에 실행됨


//connection 현재상태의 connection 객체를 반환하는 함수를 export함.
module.exports.getConnection = function(){
  return connection;
};

 

미들웨어 쪽 사용법은 다음과 같다.

 

const db = require('../service/db_service');


const userController = {
    getUser: async (req, res, next) => {
        const {userNo} = req.params;
        const sql = `SELECT * FROM exam WHERE refUgNo = ?`;
        const params = [ugNo];
    
    	//getUser라는 함수가 호출될때마다 최신상태의 connection 객체를 받아오기때문에
        //연결이 끊겼던 이전 connection은 참조하지 않게 된다.
        db.getConnection().query(sql, params, (error, rows) => {
            if (error) next(error);
            res.send(rows);
        })
    },
}

module.exports =  userController;