[Database] Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA 오류 해결

sookyoung.k·2024년 10월 9일

Database GSC신촌 mysql sql 교보DTS 글로벌소프트웨어캠퍼스 내맘대로TIL챌린지 클라우드교육

🌿 교보DTS TIL

목록 보기

11/39

🚨 문제 상황

User-Defined Function을 작성하고 실습하려던 중 에러 상황이 발생했다.

DELIMITER // 
CREATE FUNCTION check_stock(productId INT, threshold INT)
RETURNS VARCHAR(50)
BEGIN 
	DECLARE STOCK_COUNT INT;
    SELECT STOCK INTO STOCK_COUNT FROM PRODUCTS WHERE PRODUCT_ID = productId;
    
    IF STOCK_COUNT < threshold THEN
		RETURN '재고 부족';
	ELSE 
		RETURN '재고 충분';
	END IF;
END; //
DELIMITER ;

작성한 쿼리문은 위와 같다.

check_stock이라는 함수를 정의했고, productId와 threshold를 인자값으로 받는다. 그리고 반환은 VARCHAR(50)로 받는다.

맨 처음 STOCK_COUNT 변수를 선언한다. 그 다음 PRODUCT_ID와 입력값 productId가 일치하는 값을 조회하여 나온 STOCK 값을 STOCK_COUNT에 넣어준다.

그 결과 STOCK_COUNT가 입력값 threshold보다 작으면 '재고 부족'을, 크면 '재고 충분'을 반환하는 함수이다.

문법에는 전혀 문제가 없었으나 ㅠ

Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)	0.000 sec

이러한 에러코드 발생...

해결책은 두 가지가 있다. 하나씩 살펴보면서 이러한 에러가 왜 발생하는지를 알아볼 계획이다.

🧐 원인 분석

Error Code: 1418 에러는 사용자 정의 함수를 작성할 때 발생하는 에러이다. DETERMINISTIC, NO SQL 또는 READS SQL DATA 등의 속성 중 하나를 선언하지 않아서 + 바이너리 로깅이 활성화 되어서 이다. ~~그게뭔데...~~

우리가 사용자 정의 함수를 만들 때 MySQL이 함수의 실행 결과가 데이터베이스 상태에 영향을 줄 수 있다고 판단했기 때문에 에러를 반환하는 것이다. 이 판단이 바이너리 로깅과 관련이 있는데 이는 데이터 베이스의 변경 사항을 기록하는 기능이다. (데이터 복제나 복구에 사용됨)

속성	설명
DETERMINISTIC	동일한 입력에 대한 동일한 출력 보장함을 명시 (외부 상태에 의존하지 않는다)
NO SQL	함수가 SQL문을 실행하지 않는다 (읽기, 쓰기 x) (데이터베이스의 상태를 변경하지 않는다)
READS SQL DATA	함수가 SQL 데이터를 읽지만 쓰지 않는다 (데이터베이스를 읽기만 상태를 변경하지 않는다)

이 세 가지 속성 중 하나도 없고 바이너리 로깅이 활성화 된 상황에서 사용자 정의 함수를 생성하려고 하면 MySQL은 데이터 무결성을 보장하기 위해서 에러를 발생시킨다. 함수로 인해서 데이터베이스의 상태가 예상치 못하게 변경되는 것을 막기 위해서다.

근데 지금 다시 읽어보니 에러코드 자체에서 이미 you *might* want to use the less safe log_bin_trust_function_creators variable 이렇게 힌트를 준다. 너는... 덜 안전한 log_bin_trust_function_creators 변수를 사용하고 싶을 것이다... 영어가 싫어서 흐린눈하고 넘어가서 못 본거지...

결론 : 데이터 무결성을 해칠 위험이 있기 때문에 발생하는 에러이다. 관련 설정 혹은 선언을 해주도록 하자.

✳️ 해결 1. MySQL 서버 시스템 변수 변경

SET GLOBAL log_bin_trust_function_creators = 1;

내가 가장 먼저 찾았던 방법은 이거였다. 서버의 전역 변수인 log_bin_trust_function_creators 를 1로 설정해준다. 이 설정의 의미는 사용자 정의 함수가 비결정적일지라도 바이너리 로깅을 허용해준다는 것이다. MySQL에게 그냥 좀 넘어가줘~ 하는 것... 사용자 정의 함수가 데이터베이스의 상태를 변경할 수 있는지에 대해서 검사를 하지 않고 넘어가게 만든다. 안정성 검사를 건너 뛰어버리는 것...!

주의사항

이 명령어는 MySQL 서버의 전역 변수를 변경한다. 때문에 서버가 재시작되기 전까지는 모든 클라이언트 세션에 적용된다.
만약 SET SESSION log_bin_trust_function_creators = 1과 같이 세션 변수를 설정하게 되면 현재 세션에만 적용된다. 마찬가지로 서버를 재시작하면 이 설정이 사라진다.
이 명령어를 실행하려면 SUPER 권한이 필요하다. 일반 사용자 계정으로는 실행이 불가하다.

⚠️ 위험성

여러 사용자가 있는 환경에서 왜 위험하다는 것일까?

비결정적 함수 사용 가능
- 이 설정을 활성화하게 되면 MySQL은 사용자 정의 함수가 비결정적(NON-DETERMINISTIC)인지 여부를 검사하지 않는다. 그러면 외부 상태나 데이터베이스 상태에 따라 결과가 달라지는 함수를 만들 수 있다. 그러면 예상치 못한 결과를 가져올 수 있다.
데이터 무결성 위험
- 비결정적 함수가 데이터베이스의 상태를 변경하게 되면 데이터 무결성이 손상될 수 있다. 여러 사용자가 동시에 같은 비결정적 함수를 호출할 때 결과가 서로 다를 수 있고, 이로 인해 데이터 일관성 문제가 생길 수 있다.
보안 문제
- 악의적인 사용자가 비결정적 함수를 작성해서 시스템에 영향을 줄 수도 있다. 예를 들어 특정 조건에서만 결과가 바뀌는 함수를 만들면, 이를 이용해서 데이터베이스의 상태를 조작할 가능성이 생긴다.
디버깅 어려움
- 비결정적 함수의 결과가 예측이 불가해, 문제가 발생했을 때 원인 추적이나 해결이 어려워질 수 있다

쉬운 방법이지만 당연히 후에 문제가 생길 수도 있는 것?! 보안이나 데이터 무결성 측면에서 위험할 수 있다. 여러 사용자가 있는 환경에서는 의도치 않게 데이터가 엉켜버리는 상황이 발생할 수도 있을 것 같다. 물론 편리함의 측면에서는 이 방법이 빠르고 쉽다. 하지만 보안상 위험이 있기에 이후에 큰 프로젝트에 다같이 참여하는 환경에서는 이 방법보다는 다른 방법을 사용하는 것이 좋을 것 같다.

🙄 결정적 함수와 비결정적 함수

더이상은 흐린눈 할 수 없다... 결정적 함수와 비결정적 함수라는 말이 자꾸 나오는데 대체 그게 뭔데...? 뭔 차이인데...?

🔁 결정적 함수 (Deterministic Function)

동일한 입력 값에 대해 항상 동일한 결과를 반환하는 함수이다. 이는 함수의 결과가 외부 요인이나 데이터베이스의 상태에 의존하지 않는다.

예를 들면 간단한 수학 연산 같은 수학적 계산이나 고정된 값을 반환하는 함수를 말한다.

CREATE FUNCTION multiply(x INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN x * 2;  -- 항상 같은 결과
END;

🔀 비결정적 함수 (Non-Deterministic Function)

동일한 입력 값에 대해 결과가 달라질 수 있는 함수이다. 함수가 외부의 상태나 현재 날짜 및 시간, 또는 무작위 요소 등에 의존하기 때문이다.

예를 들어서 NOW(), CURDATE() 같은 현재 날짜와 시간을 반환하는 함수나 RAND() 처럼 무작위 랜던 값을 반환하는 함수가 있다.

CREATE FUNCTION get_current_time()
RETURNS DATETIME
BEGIN
    RETURN NOW();  -- 호출 시점에 따라 다른 결과
END;

정리

결정적 함수	비결정적 함수
항상 같은 입력에 대해 같은 결과를 반환	같은 입력에 대해 호출할 시점에 따라서 다른 결과를 반환할 수 있음
데이터의 변환이나 계산을 위한 함수로 사용	현재 상태나 시간, 무작위 요소를 반영해야 할 때 사용
바이너리 로깅이 활성화된 경우에도 안전하게 사용 가능	바이너리 로깅을 사용할 때 주의가 필요하며, 설정에 따라 함수의 안정성을 검사해야 함

데이터 베이스 설계 혹은 함수 작성 시 매우 중요한 개념으로 꼭 알고 가는 것이 좋을 것 같다.

✳️ 해결 2. 명시적 선언

사실 이 방법은 강사님이 알려주셨는데, 이게 더 안전하다고 하시기에 '왜 안전한건지?' 궁금증이 생겨서 이 글을 작성하게 된 것이다. 왜?! 왜 이게 더 좋은 방법일까?! 위가 더 쉬워보이는데?! 하는 궁금증에서 시작함.

함수 정의에 DETERMINISTIC을 추가하는 방법이다. 이는 MySQL에게 해당 함수가 동일한 입력에 대해 항상 동일한 결과를 반환한다고 알리는 것이다. 그러면 바이너리 로깅을 사용할 수 있게 된다.

CREATE FUNCTION my_function(param INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN param * 2;
END;

예를 들어 이런식으로 함수의 정의 부분에서 DETERMINISTIC 키워드를 추가하는 것이다. 혹은 NO_SQL, READS_SQL_DATA 키워드도 상관 없다. 함수가 안전하다는 것을 알리면 되는 것임.

장점

바이너리 로깅
- DETERMINISTIC 함수는 바이너리 로깅이 활성화된 환경에서 더 쉽게 사용될 수 있다.
성능
- MySQL이 결과를 캐시할 수 있어 성능이 향상될 수 있다.

하지만 이 또한 함수가 정말로 결정적일 경우에만 사용하는 것이 좋다. 함수가 외부 상태에 의존하지 않고, 항상 같은 결과를 반환한다면 이 방법이 더 안전하고 데이터 무결성을 유지하는데 도움이 된다.

🎓 결론

강사님이 왜 키워드를 사용하는 방법이 더 좋다고 한 건지 궁금했는데 완벽(?) 이해...

상황에 따라서 적절한 방법을 사용하면 좋을 것 같다.

안정성의 측면에서는 DETERMINISTIC 키워드를 사용하는 것이 더 좋다. 함수가 항상 같은 결과를 반환한다고 명시적으로 보장하는 것이기 때문이다.

하지만 편리함의 측면에서는 SET GLOBAL log_bin_trust_function_creators = 1;이 더 빠르다.

함수가 결정적이지 않다면 DETERMINISTIC 키워드를 사용하는 것이 좋고 그렇지 않다면, 첫 번째 방법을 사용할 수도 있다. 상황에 맞게 사용할 수 있도록 해결 방법을 여러가지로 알아두는 것이 좋을 것 같다!

본 포스팅은 글로벌소프트웨어캠퍼스와 교보DTS가 함께 진행하는 챌린지입니다

sookyoung.k

영차영차 😎

이전 포스트

[Database] SQL 심화

다음 포스트