[MySQL / Stored Procedure] 에러 핸들링 (5) SIGNAL

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

(1) DECLARE ... HANDLER

(2) Error Logging

(3) CONSTRAINT (上)

(4) CONSTRAINT (下)

(5) SIGNAL

1편 ~ 4편까지 SQLEXCEPTION 이 발생했을 때, 에러 또는 예외에 대한 SP 리턴 값을 지정하고 필요에 따라 로깅하는 방법을 소개해 드렸습니다.

이 글에서는 SQLEXCEPTION 이 발생하지 않는 예외 상황에서도, DECLARE ... HANDLER 를 사용하여 SP 리턴 값을 지정하고 로깅하는 방법을 소개합니다.

어떤 상황이 SQLEXCEPTION 이 없지만 예외로 처리해야하는 상황일까요?

대표적인 예로, 수정하거나 삭제하려는 Row 가 존재하지 않는 경우가 있습니다.

참고로 MS-SQL에서는 RAISERROR 문을 사용해 임의의 에러를 생성할 수 있습니다.

이렇게 에러를 고의로 발생시키면 TRY ... CATCH 문의 CATCH 블록으로 예외 상황을 핸들링할 수 있습니다.

마찬가지로 MySQL에서는 SIGNAL 문으로 임의의 에러를 생성하고, DECLARE ... HANDLER 문에서 핸들링 할 수 있습니다.

SIGNAL 문법

SIGNAL condition_value
    [SET signal_information_item
    [, signal_information_item] ...]
 
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
 
signal_information_item:
    condition_information_item_name = simple_value_specification
 
condition_information_item_name:
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME

문법에 나오는 모든 내용을 숙지할 필요는 없는데, MySQL Document를 보면 사용해야할 SIGNAL 문의 윤곽을 잡아볼 수 있습니다.

To signal a generic SQLSTATE value, use '45000', which means “unhandled user-defined exception.”

https://dev.mysql.com/doc/refman/5.7/en/signal.html

즉, 예외를 처리하기 위해 임의로 SIGNAL 문을 사용할 때 아래와 같이 작성하면 됩니다.

SIGNAL SQLSTATE '45000';

적용 예시

`attending` 테이블에서 Row 를 삭제하여, 수강을 취소하는 Stored Procedure 가 있습니다.

목표 : 취소하려는 수강 내역이 존재하지 않으면 10005를 리턴하고, 로그를 기록한다.

-- -----------------------------------------------------
-- PROCEDURE usp_del_attending
-- -----------------------------------------------------
 
USE exception_test;
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `usp_del_attending`$$
 
CREATE DEFINER=CURRENT_USER() PROCEDURE usp_del_attending(
      IN pi_int_student_id int  -- 학생 ID
    , IN pi_int_lecture_id int  -- 강의 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-06-27
description : 수강을 취소합니다.
parameter : 
      IN pi_int_student_id int  -- 학생 ID
    , IN pi_int_lecture_id int  -- 강의 ID
    , IN pi_dt5_now datetime(0) -- 현재 서버 시각
    , OUT po_int_return int     -- 리턴 값
result set :
    student_id / int / 학생 ID
    lecture_id / int / 강의 ID
return value :
    0 = 에러가 없습니다.
    -1 = 예상하지 않은 런 타임 오류가 발생하였습니다.
    10005 = 취소할 수강 신청 내역이 존재하지 않습니다.
usage :
    SET @pi_int_student_id = ;
    SET @pi_int_lecture_id = ;
    SET @pi_dt5_now = ;

    CALL usp_del_attending (@pi_int_student_id, @pi_int_lecture_id, @pi_dt5_now, @po_int_return);

    SELECT @po_int_return;
'
proc_body: BEGIN
    DECLARE v_vch_proc_name varchar(100) DEFAULT 'usp_del_attending';
    DECLARE v_iny_proc_step tinyint UNSIGNED DEFAULT 0;
    DECLARE v_txt_call_stack text;
    DECLARE v_bit_write_exception_log boolean DEFAULT FALSE;
    DECLARE v_vch_sql_state varchar(5);
    DECLARE v_int_error_no int;
    DECLARE v_txt_error_msg text;
 
    DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
    DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
 
    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_student_id":', IF(pi_int_student_id IS NULL, 'null', pi_int_student_id)
            , ',"pi_int_lecture_id":', IF(pi_int_lecture_id IS NULL, 'null', pi_int_lecture_id)
            , ',"pi_dt5_now":', IF(pi_dt5_now IS NULL, 'null', CONCAT('"', pi_dt5_now, '"'))
            , '}'
        );
 
        -- 런 타임 에러 중 논리 에러로 분류할 항목을 필터링
        IF v_bit_write_exception_log THEN  -- exception_log table에 기록하는 논리 에러인 경우
            INSERT exception_log (proc_name, proc_step, exception_no, call_stack, proc_call_date, log_date)
            VALUES (v_vch_proc_name, v_iny_proc_step, po_int_return, v_txt_call_stack, pi_dt5_now, NOW(0));
 
        ELSEIF po_int_return = 0 OR po_int_return IS NULL THEN  -- exception으로 분류하지 않는 그 밖의 런 타임 에러인 경우
            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 IF;
    END;
 
    SET v_txt_call_stack = CONCAT('{"pi_int_student_id":', IF(pi_int_student_id IS NULL, 'null', pi_int_student_id)
      , ',"pi_int_lecture_id":', IF(pi_int_lecture_id IS NULL, 'null', pi_int_lecture_id)
      , ',"pi_dt5_now":', IF(pi_dt5_now IS NULL, 'null', CONCAT('"', pi_dt5_now, '"'))
      , '}'
    );
 
    CALL usp_sub_add_proc_call_log(v_vch_proc_name, v_txt_call_stack, pi_dt5_now, po_int_return);
 
    SET v_iny_proc_step = 1;
 
    DELETE T
    FROM attending T FORCE INDEX FOR JOIN (PRIMARY)
    WHERE T.student_id = pi_int_student_id
        AND T.lecture_id = pi_int_lecture_id;
 
    IF ROW_COUNT() = 0 THEN
        SET v_bit_write_exception_log = TRUE;
        SET po_int_return = 10005;
 
        SIGNAL SQLSTATE '45000';
    END IF;
 
    SET po_int_return = 0;
END proc_body$$
 
DELIMITER ;

95행 ~ 105행

    DELETE T
    FROM attending T FORCE INDEX FOR JOIN (PRIMARY)
    WHERE T.student_id = pi_int_student_id
        AND T.lecture_id = pi_int_lecture_id;
 
    IF ROW_COUNT() = 0 THEN
        SET v_bit_write_exception_log = TRUE;
        SET po_int_return = 10005;
 
        SIGNAL SQLSTATE '45000';
    END IF;

`attending` 테이블에서 DELETE 했을 때 affected row 의 수가 0이면, 즉 삭제된 행이 없으면...

1. SET v_bit_write_exception_log = TRUE; -- 예외를 로깅하고

2. SET po_int_return = 10005; -- 10005를 리턴하도록 지정하고

3. SIGNAL SQLSTATE '45000'; -- SQLSTATE 45000 인 SQLEXCEPTION 을 생성합니다.

57행 ~ 83행

    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_student_id":', IF(pi_int_student_id IS NULL, 'null', pi_int_student_id)
            , ',"pi_int_lecture_id":', IF(pi_int_lecture_id IS NULL, 'null', pi_int_lecture_id)
            , ',"pi_dt5_now":', IF(pi_dt5_now IS NULL, 'null', CONCAT('"', pi_dt5_now, '"'))
            , '}'
        );
 
        -- 런 타임 에러 중 논리 에러로 분류할 항목을 필터링
        IF v_bit_write_exception_log THEN  -- exception_log table에 기록하는 논리 에러인 경우
            INSERT exception_log (proc_name, proc_step, exception_no, call_stack, proc_call_date, log_date)
            VALUES (v_vch_proc_name, v_iny_proc_step, po_int_return, v_txt_call_stack, pi_dt5_now, NOW(0));
 
        ELSEIF po_int_return = 0 OR po_int_return IS NULL THEN  -- exception으로 분류하지 않는 그 밖의 런 타임 에러인 경우
            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 IF;
    END;

1. SIGNAL 문으로 SQLSTATE 45000 인 SQLEXCEPTION 을 생성했으므로, DECLARE EXIT HANDLER FOR SQLEXCEPTION 블럭이 실행됩니다.

2. 65행~69행 : Stored Procedure를 Call 했을 때 주어진 인자의 이름과 값을 json 포맷으로 만듭니다.

3. 72행 : 예외를 로깅하는 것으로 v_bit_write_exception_log 값을 설정했으므로, IF 문 분기를 탑니다.

4. 73 ~ 74행 : 발생한 예외가 `exception_log` 테이블에 기록됩니다.

맺으며

개발자 세상에는 분명 이 보다 더 나은 에러 핸들링 방법이 있을 테니, 이 방식이 최선이라고 하지는 않겠습니다.

어쨌든 중요한 것은 Stored Procedure를 사용하기로 정한 팀이라면, 팀원들이 작성하는 SP에 일정한 규칙 - 이름 규칙, 코딩 규칙, 에러 및 예외 핸들링 규칙 - 이 철저히 지켜져야 한다는 점입니다.

특히 에러와 예외를 잘 핸들링하는 것은 정말 중요한데, 종종 일정 상의 이유로.. 우선 순위 상의 이유로.. 뒤로 미루는 경우를 봅니다.
이런 규칙을 미리 정하고, 매번 SP를 작성할 때마다 적용해 보면.. 사실 어렵지도, 시간이 더 걸리지도 않는데 말입니다.

프로젝트를 하다 보면 모르고 지나치는 오류가 있을 수 있습니다.

Tip 이라면.. 요즘은 web hook을 지원하는 메신저들이 있죠?
`error_log` 테이블과 `exception_log` 테이블을 모니터링해서 새로 삽입된 Rows를 메신저로 보내보세요.

전체 일정에서 오류를 발견하고 디버깅하는 시간을 유의미한 수준으로 절약해 줄 수 있습니다. ^^