스토어드 프로그램 2

스토어드 프로그램에 이어, 스토어드 프로그램(stored program)을 만들 때 붙이는 옵션 중 사고로 이어지기 쉬운 두 가지를 다룬다. 누구의 권한(privilege)으로 실행되는가(DEFINER·SQL SECURITY)와 같은 입력에 늘 같은 결과를 내는가(DETERMINISTIC)다. 끝으로 결과를 한 행씩 처리하는 커서와 에러를 처리하는 핸들러를 짧게 본다.

DEFINER와 SQL SECURITY

스토어드 프로그램에는 DEFINER라는 계정이 붙는다. 생략하면 만든 사람의 계정이 들어간다. SQL SECURITY는 실행할 때 누구의 권한을 쓸지 정한다.

• SQL SECURITY DEFINER(기본값): 호출한 사람과 상관없이 DEFINER 계정의 권한으로 실행한다.
• SQL SECURITY INVOKER: 호출한 사람의 권한으로 실행한다.

이 옵션은 스토어드 프로시저(stored procedure)·스토어드 함수(stored function)·뷰(view)에만 있다. 트리거(trigger)와 MySQL 이벤트(event)는 누가 호출하는 구조가 아니므로 항상 DEFINER 권한으로 실행된다.

DEFINER 방식은 편리하지만 위험도 같이 온다. DEFINER가 강한 계정이면, EXECUTE 권한만 받은 사용자도 그 프로시저를 통해 강한 작업을 할 수 있다. 매뉴얼은 가능하면 SQL SECURITY INVOKER를 쓰고, DEFINER에는 필요한 권한만 가진 계정을 두라고 권한다(사용자·권한·역할 참고).

또 하나의 함정은 DEFINER 계정이 사라진 경우다. 이런 객체를 고아 객체(orphan object)라 하며, DEFINER 권한으로 실행되는 객체는 실행 자체가 실패한다. 다른 서버로 옮겼는데 그 계정이 없을 때도 같은 일이 생긴다. MySQL 8.4는 어떤 객체의 DEFINER인 계정을 DROP USER·RENAME USER로 지우거나 이름을 바꾸려 하면 고아 객체가 생기지 않게 에러로 막는다. 자기 계정이 아닌 다른 계정을 DEFINER로 지정하려면 8.4에서는 SET_ANY_DEFINER 권한이 필요하다.

DETERMINISTIC과 바이너리 로그

DETERMINISTIC은 "같은 입력이면 늘 같은 결과를 낸다"는 선언이다. 선언하지 않으면 기본값은 NOT DETERMINISTIC이다. 중요한 점은 MySQL이 이 선언이 맞는지 검사하지 않는다는 것이다. 매뉴얼 표현대로 만든 사람의 정직함에 맡긴다. 이 선언은 두 곳에 영향을 준다.

1. 옵티마이저(optimizer). 옵티마이저는 쿼리를 어떻게 실행할지 고르는 모듈이다. WHERE id = f()에서 f()가 NOT DETERMINISTIC이면 행마다 결과가 다를 수 있으므로 행마다 함수를 다시 부른다. 인덱스로 찾으려면 찾을 값이 하나로 정해져 있어야 하는데 그럴 수 없으니, 인덱스로 한 건을 찾는 대신 풀 테이블 스캔(full table scan)이 될 수 있다. 결과가 고정된 함수라면 DETERMINISTIC을 선언하거나, 먼저 SET @v = f();로 값을 구해 WHERE id = @v로 쓴다. 반대로 결과가 바뀌는 함수를 DETERMINISTIC으로 속이면 옵티마이저가 잘못된 가정으로 계획을 세워 예상과 다른 결과가 나올 수 있다.

2. 바이너리 로그(binary log). 바이너리 로그는 서버의 데이터 변경을 순서대로 적어 두는 로그로, 복제(replication)와 시점 복구(point-in-time recovery)에 쓴다. MySQL 8.4는 바이너리 로그가 기본으로 켜져 있다. 이 상태에서 스토어드 함수를 만들 때 DETERMINISTIC, NO SQL, READS SQL DATA 중 하나를 선언하지 않으면 에러 1418로 생성이 거부된다. NO SQL은 함수 안에 SQL 문장이 없다는 선언이고, READS SQL DATA는 데이터를 읽기만 하고 고치지는 않는다는 선언이다. 아무것도 적지 않으면 기본값은 SQL은 있지만 데이터를 읽거나 쓰지 않는다는 CONTAINS SQL인데, 이것만으로는 검사를 통과하지 못한다. 결과가 바뀔 수 있고 데이터도 고치는 함수는 복제·복구에 안전하지 않다고 보기 때문이다.

위험이 실제로 생기는 곳은 SQL 문장 자체를 기록하는 문장 기반 복제(statement-based replication)다. 레플리카(replica)가 같은 문장을 다시 실행할 때 UUID()나 SYSDATE()를 쓰는 함수는 원본과 다른 값을 만든다. 바뀐 행 값을 기록하는 행 기반 복제(row-based replication)에서는 결과 행이 그대로 넘어가므로 이 문제가 없다. MySQL 8.4의 binlog_format 기본값은 ROW다. 두 포맷의 차이는 바이너리 로그 포맷과 동기화 방식에서 자세히 다룬다.

검사를 끄는 방법도 있다. log_bin_trust_function_creators 시스템 변수(system variable)를 1로 두면 이 검사를 건너뛴다. 하지만 이 변수는 8.0.34부터 폐기 예정(deprecated)이다. 문장 기반 로그에서만 쓸모가 있는 변수라, 문장 기반 포맷과 함께 없어질 예정이기 때문이다. 검사를 끄기보다 함수에 맞는 특성을 정확히 선언하는 편이 안전하다.

커서와 핸들러

커서(cursor)는 SELECT 결과를 한 행씩 꺼내 처리하는 장치로, 스토어드 프로그램 안에서만 쓴다. MySQL 커서는 읽기 전용이고, 앞으로만 한 행씩 이동하며 건너뛸 수 없다. 핸들러(handler)는 에러·경고·"더 읽을 행 없음" 같은 조건이 생겼을 때 할 일을 미리 정해 두는 선언이다.

DECLARE done INT DEFAULT FALSE;                              -- 1. 변수
DECLARE v_id BIGINT;
DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status = 'X'; -- 2. 커서
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     -- 3. 핸들러
OPEN cur;
read_loop: LOOP
  FETCH cur INTO v_id;
  IF done THEN LEAVE read_loop; END IF;
  -- v_id로 한 행 처리
END LOOP;
CLOSE cur;

• 선언 순서는 정해져 있다. 변수·조건을 먼저, 그다음 커서, 마지막에 핸들러를 선언한다.
• CONTINUE 핸들러는 처리 후 다음 문장으로 계속 가고, EXIT 핸들러는 핸들러를 선언한 BEGIN ... END 블록을 빠져나간다.
• 핸들러가 없으면 에러(SQLEXCEPTION)는 그 자리에서 프로그램을 멈추고, 경고(SQLWARNING)는 무시하고 계속 간다.

커서로 한 행씩 처리하면 행마다 문장을 따로 실행하게 된다. 같은 일을 UPDATE ... WHERE 한 문장으로 할 수 있다면 그쪽이 보통 단순하고 빠르다.

면접 포인트

• DEFINER vs INVOKER: DEFINER(기본)는 만든 계정 권한, INVOKER는 호출자 권한. 트리거·이벤트는 늘 DEFINER. DEFINER 계정이 없어지면 실행 실패.
• "DETERMINISTIC을 왜 신경 쓰나요?" → MySQL은 선언을 검사하지 않는다. 틀리면 옵티마이저가 인덱스를 못 쓰거나(행마다 호출) 잘못된 결과. 바이너리 로그가 켜져 있으면 선언 없는 함수는 에러 1418.
• 문장 기반 복제에서 비결정적 함수는 레플리카와 값이 어긋난다. 8.4 기본은 행 기반.