into MySQL: StoredRoutine

레이블이 StoredRoutine인 게시물을 표시합니다. 모든 게시물 표시

2010년 12월 29일 수요일

Stored function의 NOT DETERMINISTIC 옵션은 무엇이고 쿼리에 어떤 영향을 미칠까?

Procedure나 Function의 생성시에 사용되는 키워드 중에서 DETERMINISTIC 또는 NOT DETERMINISTIC이라는 키워드를 본 적이 있을 것이다.
여기서 DETERMINISTIC이 의미하는 것이 무엇일까 ?. 그리고 이 옵션으로 인해서 어떤 차이가 생기는 것일까 ?.

이 글에서는 DETERMINITIC하고 그러지 않은 함수의 차이를 알아보고자 한다.
우선, 아래와 같은 예제 함수를 하나 만들었다고 가정해보자.

CREATE FUNCTION
  getKeyValue() RETURNS BIGINT
  NOT DETERMINISTIC
BEGIN
  return 99999999;
END;;

이 함수는 NOT DETERMINISTIC 으로 정의가 되었다는 것을 기억하고, 아래 쿼리를 한번 보자.

SELECT COUNT(*) FROM tb_test WHERE fdpk > getKeyValue();

tb_test 테이블에는 대략 1억건 정도의 레코드가 저장되어 있고, fdpk는 tb_test 테이블의 Primary key 컬럼이며,
tb_test 의 fdpk 값은 1~1억까지 값을 가지고 있다고 가정해보자.

이 쿼리는 최종적으로 값 1을 리턴하는 쿼리인데, 이 쿼리가 실행되는데, 시간이 얼마나 걸릴까 ?
직접 한번 테스트해보길 바라며, 정답은 테스트를 해보진 않아서 모르겠지만, 아마 기대 했던 1초 미만은 아닐 것이다.

왜 이런 결과가 나온 것일까 ?
이 질문의 정답은 이 게시물의 제목에서 말하듯이 "NOT DETERMINISTIC" 옵션 때문이다.
MySQL의 Stored procedure나 Function이 NOT DETERMINISTIC으로 정의되면,
MySQL은 이 Stored routine의 결과값이 시시각각 달라진다고 가정하고,
비교가 실행되는 레코드마다 이 Stored routine을 매번 새로 호출해서 비교를 실행하게 된다.
즉, 함수 호출의 결과값이 Cache되지 않고, 비교되는 레코드 건수만큼 함수 호출이 발생하는 것이다.

그래서 위 예제 쿼리의 경우, 이 쿼리문이 완료되기 위해서는 getKeyValue() 함수가 1억번 호출이 되어야 되며,
그와 동시에 fdpk 컬럼에 생성되어 있는 인덱스까지도 무용지물로 만드는 것이다.

만약, getKeyValue() 함수가 DETERMINISTIC으로 정의되었다면 우리가 기대하는 시간안에 처리를 완료할 것이다.
이 때에는 MySQL이 이 함수가 DETERMINISTIC 옵션으로 입력값이 동일하면 출력값은 항상 동일하다는 것을 인지하고
단 1번만 이 함수를 호출해서 결과값으로 Primary key를 검색하게 될 것이기 때문이다.

별것 아닌것으로 보이는 이 옵션으로 엄청난 성능 차이를 낼 수 있는 것이므로,
함수를 이와 같은 용도로 사용할 경우에는 이 옵션에 주의하자.

2010년 12월 28일 화요일

트리거(Trigger)와 이벤트(Event)의 생성 및 실행 권한

이번에는 Trigger와 Event의 권한 설정에 대해서 알아보도록 하겠다.

MySQL 5.0 버전에서는 Trigger를 위한 권한이 조금 부족한 상황이다.

그래서 MySQL 5.0에서는 일반 사용자가 Trigger를 생성 및 호출하기 위해서는 ROOT 유저 (그 외, SUPER 권한을 가진 사용자)의 도움이 필요하다.

-- // 트리거 (Trigger) 생성 문장

CREATE

DEFINER = { user | CURRENT_USER }

TRIGGER backup_record AFTER UPDATE

ON member FOR EACH ROW

BEGIN

...

END;

위의 기본 Trigger의 구조에서 보이는 것처럼 Trigger는 SQL SECURITY를 명시하는 구문이 없다.

Trigger와 Event는 특정 이벤트가 발생하면 자동으로 실행되기 때문에 INVOKER라는 개념이 있을 수 없다.

또한 내부적으로 Trigger와 Event는 SQL SECURITY가 DEFINER인 방식으로 처리된다. 또한 MySQL 5.0에서는 Trigger를 위한 권한 (Privileges)가 없기 때문에 Trigger를 실행하기 위해서는 SUPER 권한(Privileges)을 반드시 가지고 있어야 한다.

그래서, 서비스용으로 Trigger를 사용하고자 하는 경우에는 반드시 아래 두 가지 중에서 한 방법을 사용해야 한다.

l 서비스용 MySQL 계정이 SUPER 권한을 가지고 있거나

l Trigger를 DBA (ROOT 또는 SUPER 권한을 가지고 있는 계정)계정으로 생성하고, DEFINER를 자기 자신으로 설정하는 방법

이 두 가지 방법의 차이는

첫 번째 방법은 Trigger를 서비스 계정의 컨텍스트 (Context)로 실행하게 되며,

두 번째 방법은 Trigger를 DEFINER로 명시된 DBA 계정의 권한 컨텍스트(Context)로 실행하게 된다.

일반적으로 서비스용 계정에 SUPER 권한(Privileges)를 부여하는 것은 조금 위험할 수 있으므로 두 번째 방법이 적합할 것으로 생각된다.

MySQL 5.1 이상부터는 TRIGGER라는 권한 (Privileges)가 새로이 도입되어서, Trigger의 생성 및 호출을 SUPER 권한과 분리해서 부여할 수 있게 되었다.

또한 MySQL 5.1 부터는 EVENT라는 권한(Privileges)가 새로이 도입되어서, Event의 생성 및 호출 권한을 서비스용 계정에 SUPER 권한 없이 부여할 수 있게 되었다. (Event라는 기능이 MySQL 5.1부터 추가되었으므로 Event에 대한 권한 문제는 고려하지 않아도 된다)

Stored procedure 와 Stored function 그리고 View 의 권한 처리

MySQL에서 Procedure나 Function 및 Trigger를 정의하고 호출하는 권한 방식은 조금 익숙하지 않은 방식으로 처리된다.

여기에서는 Procedure 와 Function의 권한 처리에 대해서 알아보고, Trigger는 다음 기회에 더 알아보도록 하겠다.

(Trigger의 권한 방식은 Procedure나 Function과는 조금 다르기 때문에...)

View의 생성 및 사용 권한은 Procedure나 Function과 거의 비슷하기 때문에 여기서 같이 언급하고자 한다.
(여기 내용은 주로 Routine에 기준해서 작성되었기 때문에 호출 방식이나 사용법은 View에 적용되지 않을 수 있다.)

-- // 프로시져 (Stored procedure) 생성 문장

CREATE

DEFINER = { user | CURRENT_USER }

PROCEDURE sp_process_ranking()

SQL SECURITY { DEFINER | INVOKER }

BEGIN

...

END;

-- // 함수 (Stored function) 생성 문장

CREATE

DEFINER = { user | CURRENT_USER }

FUNCTION fn_get_usercount() RETURN INT UNSIGNED

SQL SECURITY { DEFINER | INVOKER }

BEGIN

...

END;

CREATE or REPLACE

DEFINER = { user | CURRENT_USER }

SQL SECURITY { DEFINER | INVOKER }

VIEW view_name

AS SELECT ...

MySQL에서는Stored procedure, Stored function, Trigger를 모두 모아서 Stored routine (Stored program)이라고 부른다.

(이하, 간략히 줄여서 Routine으로 명명함)

DEFINERDEFINER란 해당 Routine을 정의한 사용자를 의미하며, 기본적으로 DEFINER는 해당 Routine에 대한 Ownership을 가지게 된다. 3가지 Routine 모두 DEFINER라는 구문을 명시할 수 있는데, 만약 DEFINER 구문을 명시하지 않으면 현재 Routine을 생성하는 사용자가 DEFINER로 정의된다. 만약 별도로 DEFINER를 명시하고자 하는 경우 MySQL 표준 계정 표기법대로 아래와 같이 정의해야 한다. DEFINER = 'account'@'host_name or ip_address or domain_name' SUPER 권한을 가지고 있는 유저만 자기 자신 이외의 계정을 명시할 수 있다.
SQL SECURITYRoutine 중에서 Procedure와 Function 만 SQL SECURITY 구문을 명시할 수 있는데,
SQL SECURITY 구문은 해당 Routine의 실행 권한을 명시할 때 사용한다.
SQL SECURITY는 DEFINER 또는 INVOKER 둘 중에서 하나를 명시할 수 있으며,
SQL SECURITY를 명시하지 않으면 SQL SECURITY='DEFINER' 로 기본적으로 설정된다.

Procedure와 Function의 경우에는 DEFINER와 SQL SECURITY에 의해서 Routine의 실행 권한을 결정하게 되는데,

SQL SECURITY 가 DEFINER로 명시된 경우

이 경우, 어느 계정으로 실행하느냐에 관계없이 그 Routine의 DEFINER의 계정으로 Routine을 실행하게 된다. 마치 UNIX의 SetUID와 비슷한 개념으로 이해하면 될 것 같다. (실제 목적도 거의 비슷하다.) 예로, A 유저가 생성하고 DEFINER를 A 유저로 설정해 둔 Routine이 있다고 가정 해보자. 이 Routine을 B 유저가 실행한다 하더라도, 이 Routine이 실행되는 동안은 A 유저의 권한 컨텍스트(Context)에서 실행된다.

SQL SECURITY가 INVOKER로 명시된 경우
이 경우, Routine의 생성한 사용자나 DEFINER에 관계없이 현재 해당 Routine을 호출하는 사용자의 권한으로 실행하게 된다. 위 예제를 이 케이스에 맞춰 본다면, 그 Routine은 B 유저의 권한 컨텍스트(Context)로 실행된다.

어떤 케이스이든지, 해당 Routine을 실행하기 위해서는, Routine을 실행하는 계정(A유저가 될지 B유저가 될지는 DEFINER와 SQL SECURITY에 의해서 가변적임)의 사용자가 Routine이 소속된 데이터베이스의 EXECUTE 권한을 가지고 있어야 하며, Routine내의 모든 작업들에 대해서 적절한 권한(예를 들어서 테이블 SELECT 또는 INSERT 등등)들을 가지고 있어야 한다.

SQL SECURITY를 DEFINER로 설정해야 하는 경우

중요한 데이터베이스의 테이블에 접근해서 데이터를 조작하는 특수한 작업만 일반 사용자(B)에게 제공해야 하는 경우에는 DEFINER를 DBA 계정으로 설정하고, SQL SECURITY를 DEFINER로 지정하면, 일반 사용자(B)는 중요한 테이블에 대해서 Routine에서 제공하는 제한된 작업만 할 수 있게 된다.

그 이외의 경우에는 SQL SECURITY를 INVOKER를 설정하는 것이 보안적으로 안전하다.

2010년 12월 27일 월요일

Stored routine (Procedure, Function, Trigger)에서의 예외 처리 방법

Stored routine (Procedure, Function, Trigger)는 다른 절차적인 프로그램 언어와 같이
여러가지 에러 상황에 대한 Exception handling이 필수적이다.
여기에서는 MySQL Stored routine에서의 예외 처리를 한번 알아보고자 한다.

우선 MySQL의 예외처리는 DECLARE ... HANDLER 구문을 이용하여
각 예외 케이스의 이벤트가 발생하면 그 Handler가 작동하는 형태로 구현이 가능하다.

HANDLER 정의 구문

DECLARE handler_type HANDLER
FOR condition_value [, condition_value] ...
handler_statements

handler_type:

CONTINUE

| EXIT

Handler type

CONTINUE 정의된 handler_statements를 실행하고, Stored routine의 마지막 실행 지점으로 다시 돌아간다.
EXIT 정의된 handler_statements를 실행하고, 현재 Handler가 정의된 BEGIN ... END 블록을 벗어난다. (만약, 현재 Handler가 최상위 블록에 정의되었다면, 현재 Stored routine을 종료하게 된다.)

Condition value

SQLSTATE
${sqlstate_value} Stored routine에서 코드를 실행하던 중 어떤 이벤트가 발생했을 때, 그 이벤트의 SQLSTATE 값을 체크해서 실행되는 Handler를 정의할 때 사용.
SQLWARNINGStored routine에서 코드를 실행하던 중 SQLWarning이 발생했을 때, 실행되는 Handler를 정의할 때 사용. SQLWARNING은 "01"로 시작되는SQLSTATE 를 표현하는 동의어와 같은 것이다.
NOT FOUNDSELECT 쿼리 문의 결과 건수가 1건도 없거나, CURSOR의 레코드를 마지막까지 읽은 경우 실행되는 Handler를 정의할 때 사용. NOT FOUND는 "02"로 시작되는 SQLSTATE를 표현하는 동의어와 같은 것이다.
SQLEXCEPTIONSQLWARNING과 NOT FOUND 그리고 "00"(정상 처리)으로 시작되는 SQLSTATE 이외의 모든 케이스를 의미하는 키워드이다.
${mysql_error_code}코드 실행 중 어떤 이벤트가 발생했을 때, SQLSTATE값이 아닌 MySQL의 ErrorNo 값을 비교해서 실행되는 Handler를 정의할 때 사용.
${condition_name}DECLARE ${condition_name} CONDITION FOR ... 구문을 이용하여 사용자 정의 CONDITION을 생성할 수 있는데, 그 이름을 여기에 명시해서 Handler를 정의할 때 사용. (자세한 설명은 매뉴얼 참조)

Condition value 정의시 주의사항

Condition value는 콤마(",")를 이용하여 여러 개 동시에 나열 하는 것이 가능하다.
Condition value에 SQLSTATE '00000' 또는 MySQL ErrorNo 0 은 정의해서는 안 된다. 이 두 개의 값은 모두 정상 처리를 의미하는 SQLSTATE와 MySQL ErrorNo 값이다.
SQLSTATE와 ErrorNo에 대한 자세한 사항은 "MySQL ErrorNo와 SqlState 는 무엇을 의미할까?"를 참조

Handler statements

특정 이벤트가 발생했을 때, 그 이벤트에 대한 처리(Handling) 코드를 여기에 명시하며, Handler statements에는 단순히 명령문 하나만 명시될 수도 있으며, BEGIN ... END 블록으로 정의된 코드 블록이 명시될 수도 있다.

예제로 간단한 Handler 정의 문장을 살펴보자.

예제 1)

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_flag=1;

SQLException (Sql State가 "00", "01", "02" 이외의 값으로 시작되는 에러)이 발생했을 때, error_flag 변수의 값을 1로 설정하고, 마지막 실행했던 Stored routine의 코드로 돌아가서 계속 실행(CONTINUE)

예제 2)

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

SELECT 'Error occurred - terminating';

END;

SQLException (Sql State가 "00", "01", "02" 이외의 값으로 시작되는 에러)이 발생했을 때, ROLLBACK을 실행하고, SELECT ... 문장을 실행 후, 이 Handler가 실행된 코드 블록을 벗어나거나, 이 Handler 가 Stored routine의 최상위 블록에 정의된 경우에는 실행을 종료한다.

Stored routine에서 실행되는 SELECT 문장의 결과를 Console이나 Client로 즉시 출력된다.

(이 예제에서는 " Error occurred - terminating "라는 값이 출력된다.)

예제 3)

DECLARE CONTINUE HANDER FOR 1062 SELECT 'Duplicate key in index';

MySQL ErrorNo가 1062인 이벤트가 발생했을 때, Console이나 Client에 "Duplicate key in index"라는 결과 셋을 출력하고, Stored routine의 원래 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다.

예제 4)

DECLARE CONTINUE HANDER FOR SQLSTATE '23000' SELECT 'Duplicate key in index';

MySQL SqlState가 "23000"인 이벤트가 발생했을 때, Console이나 Client에 "Duplicate key in index"라는 결과 셋을 출력하고, Stored routine의 원래 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다.

예제 5)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET process_done=1;

SELECT 문의 실행 결과 레코드 건이 없거나, CURSOR의 결과 레코드를 모두 Fetch하고 나면 process_done 변수 값을 1로 설정하고 Stored routine의 마지막 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다.

예제 6)

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET process_done=1;

SELECT 문의 실행 결과 레코드 건이 없거나, CURSOR의 결과 레코드를 모두 Fetch하고 나면 process_done 변수 값을 1로 설정하고 Stored routine의 마지막 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다. ("02000"는 NOT FOUND를 의미하는 SQLSTATE값이다.)

예제 7)

DECLARE CONTINUE HANDLER FOR 1329 SET process_done=1;

SELECT 문의 실행 결과 레코드 건이 없거나, CURSOR의 결과 레코드를 모두 Fetch하고 나면 process_done 변수 값을 1로 설정하고 Stored routine의 마지막 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다. (1329는 NOT FOUND를 의미하는 MySQL ErrorNo값이다.)

예제 8)

DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION

BEGIN

ROLLBACK;

SELECT 'Process terminated, Because error';

SHOW ERRORS;

SHOW WARNINGS;

END;

SQLWARNING 이나 SQLEXCEPTION이 발생하면, 지금까지의 데이터 변경을 모두 ROLLBACK하고, 에러와 경고 메시지를 출력하고 Stored routine을 종료한다.

MySQL ErrorNo와 SqlState 는 무엇을 의미할까?

PHP나 JDBC로 MySQL 데이터베이스를 이용하는 프로그램을 작성하다 보면 ErrorNo와 SqlState라는 말이나 값들을 자주 접하게 된다.

그러면, "왜 MySQL은 에러나 상태 코드를 이렇게 ErrorNo와 SqlState라는 것으로 따로 분리해서 사용할까"라는 의문이 들 것이다.

이 글에서는 ErrorNo와 SqlState의 차이점과 대표적인 ErrorNo와 SqlState값을 확인 해보자.

mysql> SELECT * FROM not_found_table;

ERROR 1146 (42S02): Table 'test.not_found_table' doesn't exist

위의 예제에서와 같이 mysql client를 이용해서 쿼리를 실행하면, 에러나 경고 발생시에 콘솔에
"ERROR ${ERROR-NO} (${SQL-STATE}): ${ERROR-MESSAGE}"
와 같은 형태의 결과물을 확인할 수 있다. 각 부분의 출력 값들의 의미는 아래와 같다. 이런 ErrorNo나 SqlState값은 기본적인 프로그램 작성뿐만 아니라 MySQL의 Stored program (Procedure, Function, Trigger)을 작성할 때 예외 처리를 위해서도 반드시 알아야 할 내용이기도 하다.

* ${ERROR-NO}
4자리 (현재까지는) 숫자 값으로 구성된 에러 코드이며, 이 에러 코드 값은 MySQL에서만 유효한 에러 식별 번호이다. 즉, 1146이라는 에러 코드 값은 MySQL에서는 "테이블이 존재하지 않는다"라는 것을 의미하지만, 다른 DBMS 벤더에서는 다른 의미로 사용될 수 있다는 것을 의미한다. (또한 다른 DBMS 벤더에서는 다른 형태로 에러 코드가 구성되어 있을 수도 있다.)

* ${SQL-STATE}
5 글자의 Alpha-Numeric 으로 구성되며, 에러뿐만 아니라 여러 가지 상태를 의미하는 코드로써, 이 값은 DBMS 종류가 다르다 하더라도 ANSI SQL 표준을 준수하는 DBMS (ODBC, JDBC 포함)에서는 모두 동일한 값을 가지고 된다. 즉 이 값은 표준 값으로 정의되기 때문에 DBMS 벤더에 의존적이지 않다. 대 부분의 ErrorNo는 특정 SqlState값과 매핑이 되어 있으며, 그 이외의 매핑되지 않는 ErrorNo의 경우에는 SqlState 값이 "HY000" (General error)으로 설정된다.
SqlState 값의 앞 2글자는 아래와 같은 의미를 가지고 있다.
- "00" 정상 처리됨
- "01" 경고 메시지 (Warning)
- "02" Not found (SELECT 나 Cursor 에서 결과가 없는 경우에만 사용됨)
- 그 이외의 값들은 각자의 에러 케이스들을 의미한다.

* ${ERROR-MESSAGE}포맷팅된 텍스트 문장으로, Human readable한 에러 메시지이다. 이 정보 또한 DBMS 벤더 별로 내용이나 구조가 다르다.

MySQL의 ErrorNo와 SqlState 및 Error message의 목록은 아래 링크에서 확인
http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html

SqlState와 ErrorNo의 예제를 몇 개 살펴보자.

경고

Error: 1262 SQLSTATE: 01000 (ER_WARN_TOO_MANY_RECORDS)

Message: Row %ld was truncated; it contained more data than there were input columns

NotFound

Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Message: No data - zero rows fetched, selected, or processed

그 외 에러

Error: 1061 SQLSTATE: 42000 (ER_DUP_KEYNAME)

Message: Duplicate key name '%s'