출처 : http://www.mysqltutorial.org/mysql-error-handling-in-stored-procedures/


Summary: in this tutorial, you will learn how to use MySQL handler to handle exceptions or errors encountered in stored procedures.

When an error occurs inside a stored procedure, it is important to handle it appropriately, such as continuing or exiting the current code block’s execution, and issuing a meaningful error message.

MySQL provides an easy way to define handlers that handle from general conditions such as warnings or exceptions to specific conditions e.g., specific error codes.

Declaring a handler

To declare a handler, you use the  DECLARE HANDLER statement as follows:

If a condition whose value matches the  condition_value , MySQL will execute the statement and continue or exit the current code block based on the action .

The action accepts one of the following values:

  • CONTINUE :  the execution of the enclosing code block ( BEGIN … END ) continues.
  • EXIT : the execution of the enclosing code block, where the handler is declared, terminates.

The  condition_value specifies a particular condition or a class of conditions that activate the handler. The  condition_value accepts one of the following values:

  • A MySQL error code.
  • A standard SQLSTATE value. Or it can be an SQLWARNING , NOTFOUND or SQLEXCEPTION condition, which is shorthand for the class of SQLSTATE values. The NOTFOUND condition is used for a cursor or  SELECT INTO variable_list statement.
  • A named condition associated with either a MySQL error code or SQLSTATE value.

The statement could be a simple statement or a compound statement enclosing by the BEGIN and ENDkeywords.

MySQL error handling examples

Let’s look into several examples of declaring handlers.

The following handler means that if an error occurs, set the value of the  has_error variable to 1 and continue the execution.

The following is another handler which means that in case an error occurs, rollback the previous operation, issue an error message, and exit the current code block. If you declare it inside the BEGIN ENDblock of a stored procedure, it will terminate stored procedure immediately.

The following handler means that if there are no more rows to fetch, in case of a cursor or SELECT INTO statement, set the value of the  no_row_found variable to 1 and continue execution.

The following handler means that if a duplicate key error occurs, MySQL error 1062 is issued. It issues an error message and continues execution.

MySQL handler example in stored procedures

First, we create a new table named  article_tags for the demonstration:

The  article_tags table stores the relationships between articles and tags. Each article may have many tags and vice versa. For the sake of simplicity, we don’t create articles and tags tables, as well as the foreign keys in the  article_tags table.

Next, we create a stored procedure that inserts article id and tag id into the article_tags table:

Then, we add tag id 1, 2 and 3 for the article 1 by calling the insert_article_tags  stored procedure as follows:

After that, we try to insert a duplicate key to check if the handler is really invoked.

We got an error message. However, because we declared the handler as a CONTINUE handler, the stored procedure continued the execution. As the result, we got the tag count for the article as well.

MySQL Error Handling Example

If we change the CONTINUE in the handler declaration to EXIT , we will get an error message only.

Finally, we can try to add a duplicate key to see the effect.

MySQL error handling - duplicate keys

MySQL handler precedence

In case there are multiple handlers that are eligible for handling an error, MySQL will call the most specific handler to handle the error first.

An error always maps to one MySQL error code because in MySQL it is the most specific. An SQLSTATEmay map to many MySQL error codes, therefore, it is less specific. An SQLEXCPETION or an SQLWARNING is the shorthand for a class of SQLSTATES values so it is the most generic.

Based on the handler precedence’s rules,  MySQL error code handler, SQLSTATE handler and SQLEXCEPTIONtakes the first, second and third precedence.

Suppose we declare three handlers in the  insert_article_tags_3 stored procedure as follows:

We try to insert a duplicate key into the article_tags table by calling the stored procedure:

As you see the MySQL error code handler is called.

MySQL handler precedence

Using a named error condition

Let’s start with an error handler declaration.

What does the number 1051 really mean? Imagine you have a big stored procedure polluted with those numbers all over places; it will become a nightmare to maintain the code.

Fortunately, MySQL provides us with the DECLARE CONDITION statement that declares a named error condition, which associates with a condition.

The syntax of the DECLARE CONDITION statement is as follows:

The condition_value  can be a MySQL error code such as 1015 or a SQLSTATE value. The condition_valueis represented by the condition_name .

After the declaration, we can refer to condition_name instead of condition_value .

So we can rewrite the code above as follows:

This code is obviously more readable than the previous one.

Notice that the condition declaration must appear before handler or cursor declarations.

블로그 이미지

레몬도리 LemonDory

개발자의 이야기

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


블로그 이미지

레몬도리 LemonDory

개발자의 이야기

https://www.fun25.co.kr/blog/ubuntu-16-04-mysql-server-install/

OS: Ubuntu 16.04

# apt-get update
# apt-get install mysql-server

MySQL 서버의 root 계정의 암호를 설정한다. 이 암호는 설치 후 반드시 필요하니 꼭 메모해 두도록 한다.

enter image description here

설치시에 나오는 메시지이다.

패키지 목록을 읽는 중입니다... 완료
의존성 트리를 만드는 중입니다       
상태 정보를 읽는 중입니다... 완료
The following additional packages will be installed:
  apparmor libaio1 libapparmor-perl libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.0-5 libfcgi-perl
  libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl
  liblwp-mediatypes-perl libnuma1 libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common
  mysql-server-5.7 mysql-server-core-5.7 psmisc
제안하는 패키지:
  apparmor-profiles apparmor-profiles-extra apparmor-docs apparmor-utils libdata-dump-perl libipc-sharedcache-perl libwww-perl
  mailx tinyca
다음 새 패키지를 설치할 것입니다:
  apparmor libaio1 libapparmor-perl libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.0-5 libfcgi-perl
  libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl
  liblwp-mediatypes-perl libnuma1 libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server
  mysql-server-5.7 mysql-server-core-5.7 psmisc
0개 업그레이드, 25개 새로 설치, 0개 제거 및 74개 업그레이드 안 함.
19.5 M바이트 아카이브를 받아야 합니다.
이 작업 후 165 M바이트의 디스크 공간을 더 사용하게 됩니다.
계속 하시겠습니까? [Y/n] 
받기:1 http://ftp.daumkakao.com/ubuntu xenial-updates/main amd64 mysql-common all 5.7.17-0ubuntu0.16.04.2 [15.7 kB]
받기:2 http://ftp.daumkakao.com/ubuntu xenial-updates/main amd64 libapparmor-perl amd64 2.10.95-0ubuntu2.6 [31.4 kB]
받기:3 http://ftp.daumkakao.com/ubuntu xenial-updates/main amd64 apparmor amd64 2.10.95-0ubuntu2.6 [450 kB]
받기:4 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libaio1 amd64 0.3.110-2 [6,356 B]
받기:5 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libnuma1 amd64 2.0.11-1ubuntu1 [21.0 kB]
받기:6 http://ftp.daumkakao.com/ubuntu xenial-updates/main amd64 mysql-client-core-5.7 amd64 5.7.17-0ubuntu0.16.04.2 [6,516 kB]
받기:7 http://ftp.daumkakao.com/ubuntu xenial-updates/main amd64 mysql-client-5.7 amd64 5.7.17-0ubuntu0.16.04.2 [1,666 kB]
받기:8 http://ftp.daumkakao.com/ubuntu xenial-updates/main amd64 mysql-server-core-5.7 amd64 5.7.17-0ubuntu0.16.04.2 [7,564 kB]
받기:9 http://ftp.daumkakao.com/ubuntu xenial/main amd64 psmisc amd64 22.21-2.1build1 [48.0 kB]
받기:10 http://ftp.daumkakao.com/ubuntu xenial-updates/main amd64 libevent-core-2.0-5 amd64 2.0.21-stable-2ubuntu0.16.04.1 [70.6 kB]
받기:11 http://ftp.daumkakao.com/ubuntu xenial-updates/main amd64 mysql-server-5.7 amd64 5.7.17-0ubuntu0.16.04.2 [2,466 kB]
받기:12 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libhtml-tagset-perl all 3.20-2 [13.5 kB]
받기:13 http://ftp.daumkakao.com/ubuntu xenial/main amd64 liburi-perl all 1.71-1 [76.9 kB]
받기:14 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libhtml-parser-perl amd64 3.72-1 [86.1 kB]
받기:15 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libcgi-pm-perl all 4.26-1 [185 kB]
받기:16 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libfcgi-perl amd64 0.77-1build1 [32.3 kB]
받기:17 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libcgi-fast-perl all 1:2.10-1 [10.2 kB]
받기:18 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libencode-locale-perl all 1.05-1 [12.3 kB]
받기:19 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libhtml-template-perl all 2.95-2 [60.4 kB]
받기:20 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libtimedate-perl all 2.3000-2 [37.5 kB]
받기:21 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libhttp-date-perl all 6.02-1 [10.4 kB]
받기:22 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libio-html-perl all 1.001-1 [14.9 kB]
받기:23 http://ftp.daumkakao.com/ubuntu xenial/main amd64 liblwp-mediatypes-perl all 6.02-1 [21.7 kB]
받기:24 http://ftp.daumkakao.com/ubuntu xenial/main amd64 libhttp-message-perl all 6.11-1 [74.3 kB]
받기:25 http://ftp.daumkakao.com/ubuntu xenial-updates/main amd64 mysql-server all 5.7.17-0ubuntu0.16.04.2 [10.8 kB]
내려받기 19.5 M바이트, 소요시간 5초 (3,442 k바이트/초)
패키지를 미리 설정하는 중입니다...
Selecting previously unselected package mysql-common.
(데이터베이스 읽는중 ...현재 14946개의 파일과 디렉터리가 설치되어 있습니다.)
Preparing to unpack .../mysql-common_5.7.17-0ubuntu0.16.04.2_all.deb ...
Unpacking mysql-common (5.7.17-0ubuntu0.16.04.2) ...
Selecting previously unselected package libapparmor-perl.
Preparing to unpack .../libapparmor-perl_2.10.95-0ubuntu2.6_amd64.deb ...
Unpacking libapparmor-perl (2.10.95-0ubuntu2.6) ...
Selecting previously unselected package apparmor.
Preparing to unpack .../apparmor_2.10.95-0ubuntu2.6_amd64.deb ...
Unpacking apparmor (2.10.95-0ubuntu2.6) ...
Selecting previously unselected package libaio1:amd64.
Preparing to unpack .../libaio1_0.3.110-2_amd64.deb ...
Unpacking libaio1:amd64 (0.3.110-2) ...
Selecting previously unselected package libnuma1:amd64.
Preparing to unpack .../libnuma1_2.0.11-1ubuntu1_amd64.deb ...
Unpacking libnuma1:amd64 (2.0.11-1ubuntu1) ...
Selecting previously unselected package mysql-client-core-5.7.
Preparing to unpack .../mysql-client-core-5.7_5.7.17-0ubuntu0.16.04.2_amd64.deb ...
Unpacking mysql-client-core-5.7 (5.7.17-0ubuntu0.16.04.2) ...
Selecting previously unselected package mysql-client-5.7.
Preparing to unpack .../mysql-client-5.7_5.7.17-0ubuntu0.16.04.2_amd64.deb ...
Unpacking mysql-client-5.7 (5.7.17-0ubuntu0.16.04.2) ...
Selecting previously unselected package mysql-server-core-5.7.
Preparing to unpack .../mysql-server-core-5.7_5.7.17-0ubuntu0.16.04.2_amd64.deb ...
Unpacking mysql-server-core-5.7 (5.7.17-0ubuntu0.16.04.2) ...
Selecting previously unselected package psmisc.
Preparing to unpack .../psmisc_22.21-2.1build1_amd64.deb ...
Unpacking psmisc (22.21-2.1build1) ...
Selecting previously unselected package libevent-core-2.0-5:amd64.
Preparing to unpack .../libevent-core-2.0-5_2.0.21-stable-2ubuntu0.16.04.1_amd64.deb ...
Unpacking libevent-core-2.0-5:amd64 (2.0.21-stable-2ubuntu0.16.04.1) ...
Processing triggers for systemd (229-4ubuntu10) ...
Processing triggers for ureadahead (0.100.0-19) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
mysql-common (5.7.17-0ubuntu0.16.04.2) 설정하는 중입니다 ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Selecting previously unselected package mysql-server-5.7.
(데이터베이스 읽는중 ...현재 15287개의 파일과 디렉터리가 설치되어 있습니다.)
Preparing to unpack .../mysql-server-5.7_5.7.17-0ubuntu0.16.04.2_amd64.deb ...
Unpacking mysql-server-5.7 (5.7.17-0ubuntu0.16.04.2) ...
Selecting previously unselected package libhtml-tagset-perl.
Preparing to unpack .../libhtml-tagset-perl_3.20-2_all.deb ...
Unpacking libhtml-tagset-perl (3.20-2) ...
Selecting previously unselected package liburi-perl.
Preparing to unpack .../liburi-perl_1.71-1_all.deb ...
Unpacking liburi-perl (1.71-1) ...
Selecting previously unselected package libhtml-parser-perl.
Preparing to unpack .../libhtml-parser-perl_3.72-1_amd64.deb ...
Unpacking libhtml-parser-perl (3.72-1) ...
Selecting previously unselected package libcgi-pm-perl.
Preparing to unpack .../libcgi-pm-perl_4.26-1_all.deb ...
Unpacking libcgi-pm-perl (4.26-1) ...
Selecting previously unselected package libfcgi-perl.
Preparing to unpack .../libfcgi-perl_0.77-1build1_amd64.deb ...
Unpacking libfcgi-perl (0.77-1build1) ...
Selecting previously unselected package libcgi-fast-perl.
Preparing to unpack .../libcgi-fast-perl_1%3a2.10-1_all.deb ...
Unpacking libcgi-fast-perl (1:2.10-1) ...
Selecting previously unselected package libencode-locale-perl.
Preparing to unpack .../libencode-locale-perl_1.05-1_all.deb ...
Unpacking libencode-locale-perl (1.05-1) ...
Selecting previously unselected package libhtml-template-perl.
Preparing to unpack .../libhtml-template-perl_2.95-2_all.deb ...
Unpacking libhtml-template-perl (2.95-2) ...
Selecting previously unselected package libtimedate-perl.
Preparing to unpack .../libtimedate-perl_2.3000-2_all.deb ...
Unpacking libtimedate-perl (2.3000-2) ...
Selecting previously unselected package libhttp-date-perl.
Preparing to unpack .../libhttp-date-perl_6.02-1_all.deb ...
Unpacking libhttp-date-perl (6.02-1) ...
Selecting previously unselected package libio-html-perl.
Preparing to unpack .../libio-html-perl_1.001-1_all.deb ...
Unpacking libio-html-perl (1.001-1) ...
Selecting previously unselected package liblwp-mediatypes-perl.
Preparing to unpack .../liblwp-mediatypes-perl_6.02-1_all.deb ...
Unpacking liblwp-mediatypes-perl (6.02-1) ...
Selecting previously unselected package libhttp-message-perl.
Preparing to unpack .../libhttp-message-perl_6.11-1_all.deb ...
Unpacking libhttp-message-perl (6.11-1) ...
Selecting previously unselected package mysql-server.
Preparing to unpack .../mysql-server_5.7.17-0ubuntu0.16.04.2_all.deb ...
Unpacking mysql-server (5.7.17-0ubuntu0.16.04.2) ...
Processing triggers for systemd (229-4ubuntu10) ...
Processing triggers for ureadahead (0.100.0-19) ...
libapparmor-perl (2.10.95-0ubuntu2.6) 설정하는 중입니다 ...
apparmor (2.10.95-0ubuntu2.6) 설정하는 중입니다 ...
update-rc.d: warning: start and stop actions are no longer supported; falling back to defaults
diff: /var/lib/apparmor/profiles/.apparmor.md5sums: No such file or directory
libaio1:amd64 (0.3.110-2) 설정하는 중입니다 ...
libnuma1:amd64 (2.0.11-1ubuntu1) 설정하는 중입니다 ...
mysql-client-core-5.7 (5.7.17-0ubuntu0.16.04.2) 설정하는 중입니다 ...
mysql-client-5.7 (5.7.17-0ubuntu0.16.04.2) 설정하는 중입니다 ...
mysql-server-core-5.7 (5.7.17-0ubuntu0.16.04.2) 설정하는 중입니다 ...
psmisc (22.21-2.1build1) 설정하는 중입니다 ...
libevent-core-2.0-5:amd64 (2.0.21-stable-2ubuntu0.16.04.1) 설정하는 중입니다 ...
mysql-server-5.7 (5.7.17-0ubuntu0.16.04.2) 설정하는 중입니다 ...
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Renaming removed key_buffer and myisam-recover options (if present)
libhtml-tagset-perl (3.20-2) 설정하는 중입니다 ...
liburi-perl (1.71-1) 설정하는 중입니다 ...
libhtml-parser-perl (3.72-1) 설정하는 중입니다 ...
libcgi-pm-perl (4.26-1) 설정하는 중입니다 ...
libfcgi-perl (0.77-1build1) 설정하는 중입니다 ...
libcgi-fast-perl (1:2.10-1) 설정하는 중입니다 ...
libencode-locale-perl (1.05-1) 설정하는 중입니다 ...
libhtml-template-perl (2.95-2) 설정하는 중입니다 ...
libtimedate-perl (2.3000-2) 설정하는 중입니다 ...
libhttp-date-perl (6.02-1) 설정하는 중입니다 ...
libio-html-perl (1.001-1) 설정하는 중입니다 ...
liblwp-mediatypes-perl (6.02-1) 설정하는 중입니다 ...
libhttp-message-perl (6.11-1) 설정하는 중입니다 ...
mysql-server (5.7.17-0ubuntu0.16.04.2) 설정하는 중입니다 ...
Processing triggers for systemd (229-4ubuntu10) ...
Processing triggers for ureadahead (0.100.0-19) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...

설치된 MySQL 버전을 확인해 볼려면 아래와 같이 dkpg 명령어를 이용한다. MySQL 5.7 버전으로 설치가 되었다.

# dpkg --list | grep mysql
ii  mysql-client-5.7           5.7.17-0ubuntu0.16.04.2         amd64        MySQL database client binaries
ii  mysql-client-core-5.7      5.7.17-0ubuntu0.16.04.2         amd64        MySQL database core client binaries
ii  mysql-common               5.7.17-0ubuntu0.16.04.2         all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server               5.7.17-0ubuntu0.16.04.2         all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.7           5.7.17-0ubuntu0.16.04.2         amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.7      5.7.17-0ubuntu0.16.04.2         amd64        MySQL database server binaries

MySQL 서버 시작

# service mysql start

MySQL 서버 종료

# service mysql stop

설정 파일 위치는 /etc/mysql/my.cnf 이다. 아래와 같이 클라이언트와 서버쪽 설정 파일을 include 하도록 되어 있다.

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

서버쪽 설정을 변경할려면 /etc/mysql/mysql/conf.d/mysqld.cnf 를 수정하면 된다. MySQL 서버는 초기 설치시에 바인딩 어드레스가 127.0.0.1 로 되어 있기 때문에 리모트에서 접속이 불가하다. 리모트에서 접속을 할려면 아래와 같이 bind-address 를 0.0.0.0 으로 한 후 MySQL 서버를 재시작한다.

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

MySQL 서버로 접속해 본다.

root@api_mysql_dev:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-0ubuntu0.16.04.2 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

외부 접속 설정(https://zetawiki.com/wiki/MySQL_%EC%9B%90%EA%B2%A9_%EC%A0%91%EC%86%8D_%ED%97%88%EC%9A%A9)

모든 IP 허용
INSERT INTO mysql.user (host,user,authentication_string,ssl_cipher, x509_issuer, x509_subject) VALUES ('%','root',password('패스워드'),'','','');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;
IP 대역 허용 ( 예: 111.222.xxx.xxx )
  • 다음과 같이 설정하면 111.222로 시작하는 모든 IP가 허용된다.
INSERT INTO mysql.user (host,user,authentication_string,ssl_cipher, x509_issuer, x509_subject) VALUES ('111.222.%','root',password('패스워드'),'','','');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.%';
FLUSH PRIVILEGES;
특정 IP 1개 허용 ( 예: 111.222.33.44 )
INSERT INTO mysql.user (host,user,authentication_string,ssl_cipher, x509_issuer, x509_subject) VALUES ('111.222.33.44','root',password('패스워드'),'','','');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.33.44';
FLUSH PRIVILEGES;
  • 모든 IP를 허용한 경우 다음과 같이 원래 상태로 복구할 수 있다.
DELETE FROM mysql.user WHERE Host='%' AND User='root';
FLUSH PRIVILEGES;


블로그 이미지

레몬도리 LemonDory

개발자의 이야기

출처 : http://www.gpgstudy.com/forum/viewtopic.php?p=83676

1. 게임에 관련된 DB를 설계할 때 부하에 관련되어 고려해야 할 사항들이 어떤게 있나요? 

모든 성능 이슈가 그러하지만 DB에게 있어서는 Trade-Off 가 대단히 중요합니다. 우선 내가 구현하고자 하는 DB가 어떤 목적의 DB인지 파악하는 것이 무엇보다도 중요하죠, 일반적으로 게임은 OLTP 입니다. 즉 Insert / Update / Delete 가 빈번히 발생하는 구조로 되어 있는 것이 일반적인 형태입니다. 

즉 OLTP 환경에서는 단위 트랜잭션이 최대한 짧게 유지 되도록 하는 것이 대단히 중요합니다. 이 경우 Lock 조정을 위해서 ISOLATION LEVEL 을 조정하여 여러 트랜잭션이 블로킹을 발생 시키지 않도록 해주어야 합니다. 그러나 이 모든 것 보다 우선 하는 게 있으니 바로 모델링입니다. 우선 모델링을 잘 잡아 두면 후에 튜닝 하기가 상당히 수월 하기 때문에 모델링을 중점적으로 공부 하는 것이 중요 합니다. 

보통 게임 DB는 구조는 매우 단순하다고 생각 할 수 있는데, 생각하기 나름입니다. 복잡하다고 치면 한없이 복잡한 게 게임 DB입니다. 그래서 스팩의 변화나 기획의 변화에 유연하게 대응 할 수 있는 구조로 설계를 하는게 중요 합니다. 튜닝은 그 다음이죠 



2 . 게임에서 DB에 부하를 주는 부분이 어떤 부분이 있나요? 

부하를 주는 부분은 정말 다양 하기 때문에 자신이 만들고자 하는 게임이나 데이터를 이용하는 스타일에 따라서 완전히 달라 질 수 있습니다. 



3 . 테이블에 값들을 insert 문이나 delect 처럼 생성하고 제거하는 것보다 update문처럼 정보를 변경하는 것이 더 부하가 적게 걸린다고 하던데 왜 그런 것인지 그리고 실제 어느정도 차이가 나는 것인지요? 

Insert & Delete VS Update 에 대한 이야기가 많은데, 이 문제는 테이블의 단편화 현상과 관련된 문제입니다. 먼저 DB의 성능은 일반적으로 면적을 컨셉으로 하여 이애 하는 것이 편리 합니다. 즉 면적이 넓어지면(데이터가 커지면) 속도가 그만큼 느려지게 됩니다. 

일반적으로 OTLP 의 경우에 Key 가 되는 데이터에 인덱스를 걸어 두게 됩니다. 즉 데이터를 하나 넣으면 데이터 + 인덱스가 되는 형태이고 읽기 성능의 최대화를 위해서 물리적으로 데이터가 elevated sort 된 형태로 입력을 하는 경우가 많이 있습니다. 

만일 새로운 데이터가 들어가면 데이터 페이지에 데이터를 넣어 두고 추가적으로 인덱스 정보를 기록 합니다. 또한 DB에서 잡아둔 하나의 Page 단위를 넘어서게 되면 새로운 페이지를 만드는 구조로 데이터저장 공간을 확장 하게 됩니다. 

데이터를 직접적으로 지우면 인덱스 정보와 데이터 정보를 함께 지워야 하기 때문에 지울 때 시간이 오래 걸립니다. 또한 일반적인 DBMS 의 경우 데이터 A, B, C, D, E, F 데이터를 순서대로 넣고 그 중에서 C를 지우게 되면 D, E, F 을 위로 올리는 게 아니라 빈칸으로 두고 데이터를 검색 하게 됩니다. 이유는 모든 데이터를 앞으로 올리는 경우 Delete 시 부하가 높아 지기 때문에 비워두게 됩니다. 이를 Disk fragmentation 혹은 Data fragmentation 이라 하는데 fragmentation 이 심해지는 경우 데이터를 검색하기 위해서 검색을 해야 하는 면적이 넓어 지기 때문에 효율이 떨어지게 됩니다. 

그렇기 때문에 데이터를 fragmentation 을 줄이고 Delete 시의 부하를 조금이라도 줄이기 위해서 데이터를 지웠다고 마 킹만 해두게 됩니다. 이 데이터는 정기 점검이나 패치 작업등 에 일괄적으로 삭제 해두고 Index 을 Rebuild 해주게 되면 단편화 현상을 해결 할 수 있습니다. 



4. 제약이나 참조 키 , CASCASDE 같은 것을 사용하면 게임에 부하가 심해지나요? 
편의성을 위해서 사용할려고 하는데 DB에 부하를 주는 부분이라면 사용을 피할려고 합니다. ( 근데 그렇게까지 부하를 주지 않을꺼 같은데... ) 

5. Primary key 와 foreign key를 사용해서 관리를 할 때 장점과 단점에 대해 알고 싶습니다. 
특히 DB 부하에 관련되서 알고 싶습니다. 


제약이나 참조 키의 경우에 데이터 입력 시에 PK 관의 무결 성을 검사하게 됩니다. 그래서 무결성에 위배가 되면 데이터를 입력하제 않게 되죠, 또한 Update 시에는 CASECADE 가 설정 되어 있는 경우 PK 을 수정 하면 FK 의 데이터를 수정 하도록 되어 부하가 높아 집니다. 이러한 이유로 3의 방법을 주로 이용 하는 것입니다. 

일부 설계자나 개발자는 실 서비스의 FK을 모두 지우고 서비스를 하는 경우가 있는데 제 개인적인 생각으로는 올바르지 못하다고 생각 합니다. 후에 전문 DBA가 튜닝 시에 상당한 애를 먹게 되고 만일 논리적인 규약이 맞지 않는 경우 데이터의 무결 성 위배로 인한 중복 데이터의 삽입이 발생 할 수 있고 이를 개발자가 가드를 하지 못하는 경우 중복 또는 불필요한 데이터로 인해서 면적(DB Size) 가 커지게 되면 결국 DB는 느려지게 때문에 정규화를 최대한 지킬 필요가 있습니다. 

실제로 게임 DB 튜닝을 나가면 이러한 경우가 대단히 많습니다. Table 정보를 Reverse Engineering 했을 때 ER-Win 등의 Case Tool 에서 테이블 정보가 평행선으로 쭉 나올 때의 당황스러움이란;; 

개인적으로는 최대한 정규화를 하고 쪼갤 수 있는 만큼 쪼개고 그러다 안되면 비정규화를 해야 하는데 정규화 = 느리다 라는 선입견을 가진 분이 많아서 저 역시 이를 설득 하는데 제법 애를 먹습니다. 

DB 에서 정답은 없습니다. 특히나 성능을 관점으로 바라보게 되는 경우 더 다양한 변수가 존재 하게 되고요, 개발자에 따라서 요구하는 데이터가 다르고 데이터를 수정 하거나 입력 하는 스타일이 모두 다릅니다. 개발자가 올바른 DB를 이해 하기 위해서는 기존의 절차적인 사고를 버리고 집합적인 사고로의 전환과, 꾸준한 모델링 그리고 서비스 되고 있는 DB에 대한 주기적인 관찰과 모니터링이 중요 하다고 생각 됩니다.

블로그 이미지

레몬도리 LemonDory

개발자의 이야기

상품명을 정렬하려고 했는데

업체에서 자기회사에서 주력으로 밀고 싶은 상품들이 있다며 그 상품들을 상위로 노출하고 싶다는 이야기를 해서 테스트를 해봤다.

일반 문자 '가', '나', '다'나 'a', 'b', 'c' 등 같이 순서를 알지만 특수문자들은 어떤게 먼제 오는지 기억하기가 쉽지 않아 테스트를 했다.

결과는 아래와 같다

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      1
32      2
33      3
34      ㄱ
35      가
36      ㄴ
37      나
38      ㄷ
39      다
40      a
41      A
42      B
43      b
44      c
45      C

이런 걸 외워야하나? ㅎㅎㅎ

블로그 이미지

레몬도리 LemonDory

개발자의 이야기

status 필드가 있는데
where status in ('active', 'approved', 'rejected', 'submitted') 와 같이 넣었을때
기본키 등의 order by 에 영향을 받은 sorting 이 되게 된다.

결과를 기본 sorting 영향이 아닌
자신만의 순서로 받고 싶은경우
아래와 같이 Order by Case 문으로 해결 가능하다.



http://stackoverflow.com/questions/3892406

When 조건 Then 순서번호

Use a CASE expression (SQL Server 2005+):

ORDER BY CASE status
           WHEN 'active' THEN 1
           WHEN 'approved' THEN 2
           WHEN 'rejected' THEN 3
           WHEN 'submitted' THEN 4
           ELSE 5
         END

You can use this syntax for more complex evaluation (including combinations, or if you need to use LIKE)

ORDER BY CASE 
           WHEN status LIKE 'active' THEN 1
           WHEN status LIKE 'approved' THEN 2
           WHEN status LIKE 'rejected' THEN 3
           WHEN status LIKE 'submitted' THEN 4
           ELSE 5
         END

MySQL에서는 FIELD() 함수로 간단하게 해결 가능!

출처 : http://itzone.tistory.com/386 

블로그 이미지

레몬도리 LemonDory

개발자의 이야기

CHARINDEX 함수를 이용하여, 문자열을 쪼개어 테이블에 넣어 리턴하는 테이블리턴함수 입니다.

여러 번 작성했었는데, 간만에 그냥 테스트 해 보며 머릿속에서 나오는 대로 코딩해 보았습니다.

넘겨지는 원문은 긴 데이터도 받아들일 수 있도록 NTEXT를 받을 수 있게 구현 되어 있습니다.

해당 테이블 리턴 함수를 이용하여 alias를 주고, 다른 테이블과 JOIN 하며 처리하면 활용도가 높겠죠. ㅎ


NTEXT 데이터 타입도,

SUBSTRING  CHARINDEX 라는 훌륭한 문자열 함수를 적용시킬 수 있다는 좋은 예제라고 생각 됩니다.


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
CREATE FUNCTION TF_SPLIT
(
    @ORG_STR  NTEXT,        -- 쪼갤 원문
    @SPLITTER NVARCHAR(1)   -- 쪼갤 문자열
)
RETURNS @TBL_SPLIT TABLE
(
    VAL NVARCHAR(4000)      -- 쪼갠 데이터가 들어갈 테이블(길이 확인)
)
AS
BEGIN
 
    -- 원문이 빈 문자열이면 그대로 리턴
    IF ISNULL(DATALENGTH(@ORG_STR), 0) = 0
        GOTO FUNC_RET;
 
    -- 변수 선언
    DECLARE @START INT,
            @FOUND INT;
 
    -- 변수 초기화  
    SET @START = 1;
    SET @FOUND = CHARINDEX(@SPLITTER, @ORG_STR, @START + 1);
 
    -- @SPLITTER 가 존재하는 경우에만 LOOP
    WHILE @FOUND > @START
    BEGIN
        -- @SPLITTER 로 쪼갠 문자열을 리턴 테이블에 INSERT
        INSERT INTO @TBL_SPLIT ( VAL )
        VALUES ( SUBSTRING(@ORG_STR, @START, @FOUND - @START) );
 
        -- 다음 @SPLITTER 찾음
        SET @START = @FOUND + 1;
        SET @FOUND = CHARINDEX(@SPLITTER, @ORG_STR, @START);
    END;
 
FUNC_RET:   
    RETURN;
END
GO
 
-- 테스트
SELECT * FROM TF_SPLIT ( N'김구라&윤종신&김국진&규현&', N'&' );
GO

결과

 

 

 

 

 

출처 : http://withsoju.tistory.com/599

블로그 이미지

레몬도리 LemonDory

개발자의 이야기


--tables
 
select

 'EXEC sp_dropextendedproperty

@name = ''MS_Description''

,@level0type = ''schema''

,@level0name = '

 + object_schema_name(extended_properties.major_id) + '
 
,@level1type = ''table''
 
,@level1name = '

 + object_name(extended_properties.major_id)

from

 sys.extended_properties
 
where

 extended_properties.class_desc = 'OBJECT_OR_COLUMN'
 

and extended_properties.minor_id = 0
 

and extended_properties.name = 'MS_Description'
 


--columns
 
select

 'EXEC sp_dropextendedproperty

@name = ''MS_Description''

,@level0type = ''schema''

,@level0name = '

 + object_schema_name(extended_properties.major_id) + '
 
,@level1type = ''table''
 
,@level1name = '

 + object_name(extended_properties.major_id) + '
 
,@level2type = ''column''
 
,@level2name = '

 + columns.name
 
from

 sys.extended_properties
 

join sys.columns
 

on columns.object_id = extended_properties.major_id
 

and columns.column_id = extended_properties.minor_id
 
where

 extended_properties.class_desc = 'OBJECT_OR_COLUMN'
 

and extended_properties.minor_id > 0
 

and extended_properties.name = 'MS_Description'

 


출처 : http://www.windows-tech.info/15/48982fa580841a79.php
 http://www.windows-tech.info

 

블로그 이미지

레몬도리 LemonDory

개발자의 이야기