[MySQL / Stored Procedure] 에러 핸들링 Error Logging

출처: http://purumae.tistory.com/199

[MySQL / Stored Procedure] 에러 핸들링 시리즈

아래와 같은 테이블을 만들고, Stored Procedure 실행 중에 SQL Exception 이 발생했을 때, 디버깅에 필요한 단서를 저장합니다.

CREATE TABLE `error_log` (
    `error_log_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '에러 로그 ID',
    `proc_name` VARCHAR(100) NOT NULL COMMENT '프로시저 이름',
    `proc_step` TINYINT(3) UNSIGNED NOT NULL COMMENT '프로시저 내에서 에러가 발생한 스텝 번호',
    `sql_state` VARCHAR(5) NOT NULL COMMENT 'SQLSTATE',
    `error_no` INT(11) NOT NULL COMMENT '에러 번호',
    `error_msg` TEXT NOT NULL COMMENT '에러 메세지',
    `call_stack` TEXT NULL COMMENT '프로시저 호출 파라미터',
    `proc_call_date` DATETIME(0) NOT NULL COMMENT '프로시저 호출 일자',
    `log_date` DATETIME(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '로그 적재 일자',
    PRIMARY KEY (`error_log_id`))
ENGINE = InnoDB
COMMENT = 'DB 런타임 에러 로그';

샘플로 사용할 Stored Procedure의 일부

CREATE DEFINER=CURRENT_USER() PROCEDURE `usp_add_terms_agreement_for_campaign`(
      IN pi_int_campaign_id int UNSIGNED    -- 캠페인 ID
    , IN pi_int_social_user_id int UNSIGNED -- 소셜 유저 ID
    , IN pi_dt5_now datetime(0)             -- 현재 서버 시각
    , OUT po_int_return int                 -- 리턴 값
)
DETERMINISTIC
SQL SECURITY DEFINER
CONTAINS SQL
COMMENT '
author : doeyull.kim
e-mail : purumae@gmail.com
created date : 2018-05-03
description : 캠페인 약관에 동의합니다.
parameter : 
      IN pi_int_campaign_id int UNSIGNED    -- 캠페인 ID
    , IN pi_int_social_user_id int UNSIGNED -- 소셜 유저 ID
    , IN pi_dt5_now datetime(0)             -- 현재 서버 시각
    , OUT po_int_return int                 -- 리턴 값
return value :
    0 = 에러가 없습니다.
    -1 = 예상하지 않은 런 타임 오류가 발생하였습니다.
'
proc_body: BEGIN
    DECLARE v_vch_proc_name varchar(100) DEFAULT 'usp_add_terms_agreement_for_campaign';
    DECLARE v_iny_proc_step tinyint UNSIGNED DEFAULT 0;
    DECLARE v_txt_call_stack text;
    DECLARE v_vch_sql_state varchar(5);
    DECLARE v_int_error_no int;
    DECLARE v_txt_error_msg text;
 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 v_vch_sql_state = RETURNED_SQLSTATE
            , v_int_error_no = MYSQL_ERRNO
            , v_txt_error_msg = MESSAGE_TEXT;
 
        ROLLBACK;
 
        SET v_txt_call_stack = CONCAT('{"pi_int_campaign_id":', IF(pi_int_campaign_id IS NULL, 'null', pi_int_campaign_id)
            , ',"pi_int_social_user_id":', IF(pi_int_social_user_id IS NULL, 'null', pi_int_social_user_id)
            , ',"pi_dt5_now":', IF(pi_dt5_now IS NULL, 'null', CONCAT('"', pi_dt5_now, '"'))
            , '}'
        );
 
        INSERT error_log (proc_name, proc_step, sql_state, error_no, error_msg, call_stack, proc_call_date, log_date)
        VALUES (v_vch_proc_name, v_iny_proc_step, v_vch_sql_state, v_int_error_no, v_txt_error_msg, v_txt_call_stack, pi_dt5_now, NOW(0));
 
        SET po_int_return = -1;
 
        RESIGNAL;
    END;

1. 변수 선언

    DECLARE v_vch_proc_name varchar(100) DEFAULT 'usp_add_terms_agreement_for_campaign';
    DECLARE v_iny_proc_step tinyint UNSIGNED DEFAULT 0;
    DECLARE v_txt_call_stack text;
    DECLARE v_vch_sql_state varchar(5);
    DECLARE v_int_error_no int;
    DECLARE v_txt_error_msg text;

- v_vch_proc_name : 현재 Stored Procedure의 이름

- v_vch_proc_step : SQL Exception이 발생한 구문위 위치를 찾기 위해 사용합니다. 번거롭고 원시적이지만 긴 Stored Procedure를 디버깅해야할 때 유용합니다.

- v_txt_call_stack : input parameter 값을 json 형태로 변환하여 담습니다. (5.6과의 호환을 위해 json 유형으로 선언하지 않습니다.)

- v_vch_sql_state / v_int_error_no / v_txt_error_msg : GET DIAGNOSTICS 구문으로 얻는 SQL STATE, Error Number, Error Message 를 담을 변수

2. DECLARE ... HANDLER 구문

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
 
 
    END;

SQLEXCEPTION condition 이 되었을 때, BEGIN ~ END 블럭의 내용을 실행하고 EXIT 즉, Stored Procedure 실행을 강제로 종료합니다.

3. GET DIAGNOSTICS 구문

        GET DIAGNOSTICS CONDITION 1 v_vch_sql_state = RETURNED_SQLSTATE
            , v_int_error_no = MYSQL_ERRNO
            , v_txt_error_msg = MESSAGE_TEXT;

GET DIAGNOSTICS 구문으로 디버깅에 필요한 다음 세 가지 condition information 을 가져옵니다.

- RETURNED_SQLSTATE
- MYSQL_ERRNO
- MESSAGE_TEXT

        SET v_txt_call_stack = CONCAT('{"pi_int_campaign_id":', IF(pi_int_campaign_id IS NULL, 'null', pi_int_campaign_id)
            , ',"pi_int_social_user_id":', IF(pi_int_social_user_id IS NULL, 'null', pi_int_social_user_id)
            , ',"pi_dt5_now":', IF(pi_dt5_now IS NULL, 'null', CONCAT('"', pi_dt5_now, '"'))
            , '}'
        );

Stored Procedure의 input parameter 값을 JSON 문자열로 만들어 v_txt_call_stack 변수에 담습니다.

TIP!!!
개발자가 매번 이 부분을 손으로 코딩하는 것은 바람직하지 않습니다. (단순 반복 작업 & 실수하기 쉬움) -> 자동화 하세요~

5. INSERT `error_log` ...

        INSERT error_log (proc_name, proc_step, sql_state, error_no, error_msg, call_stack, proc_call_date, log_date)
        VALUES (v_vch_proc_name, v_iny_proc_step, v_vch_sql_state, v_int_error_no, v_txt_error_msg, v_txt_call_stack, pi_dt5_now, NOW(0));

6. 마무리

        SET po_int_return = -1;
 
        RESIGNAL;

output parameter po_int_return

- Stored Procedure 가 정상적으로 실행 : SET po_int_return = 0;
- SQL Exception 이 발생하여 `error_log` 테이블에 로깅 : SET po_int_return = -1;

RESIGNAL

- error condition 정보를 Stored Procedure를 호출한 클라이언트에게 전합니다.
- RESIGNAL 하지 않으면 Stored Procedure를 호출한 클라이언트는 SQL Exception 이 발생했다는 사실을 감지하지 못하기 때문에, RESIGNAL 이 필요합니다.