'MySQL/Stored Procedure'에 해당되는 글 8건

  1. 2018.06.27 [MySQL / Stored Procedure] 에러 핸들링 (5) SIGNAL
2018. 6. 27. 14:39



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


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


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


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


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

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


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


SIGNAL 문법

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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
cs

문법에 나오는 모든 내용을 숙지할 필요는 없는데, 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 문을 사용할 때 아래와 같이 작성하면 됩니다.

1
SIGNAL SQLSTATE '45000';
cs


적용 예시

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
-- -----------------------------------------------------
-- 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(100DEFAULT '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 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 = 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() = 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 ;
cs


95행 ~ 105행

95
96
97
98
99
100
101
102
103
104
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() = THEN
        SET v_bit_write_exception_log = TRUE;
        SET po_int_return = 10005;
 
        SIGNAL SQLSTATE '45000';
    END IF;
cs

`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행

57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 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 = 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;
cs

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를 메신저로 보내보세요.

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

Posted by 르매

댓글을 달아 주세요