허당 레몬도리

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


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


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

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


샘플로 사용할 Stored Procedure의 일부

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
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(100DEFAULT '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 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;
cs


1. 변수 선언

25
26
27
28
29
30
    DECLARE v_vch_proc_name varchar(100DEFAULT '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;
cs

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 구문

32
33
.
.
52
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
 
 
    END;
cs

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


3. GET DIAGNOSTICS 구문

34
35
36
        GET DIAGNOSTICS CONDITION v_vch_sql_state = RETURNED_SQLSTATE
            , v_int_error_no = MYSQL_ERRNO
            , v_txt_error_msg = MESSAGE_TEXT;
cs

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

RETURNED_SQLSTATE
MYSQL_ERRNO
MESSAGE_TEXT


4. Call Stack 추출

40
41
42
43
44
        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, '"'))
            , '}'
        );
cs

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

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


5. INSERT `error_log` ...

46
47
        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));
cs


6. 마무리

49
50
51
        SET po_int_return = -1;
 
        RESIGNAL;
cs

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 이 필요합니다.


profile

허당 레몬도리

@LemonDory

포스팅이 좋았다면 "좋아요❤️" 또는 "구독👍🏻" 해주세요!