[MySQL/오류노트] Solved - ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

해당 글에서는 MySQL에서 발생하는 문제를 해결하기 위한 해결방법에 관련된 글입니다.

1) 문제점

---

💡 문제점

- MySQL Workbench 내에서 Function을 생성하는 중에 아래와 같은 에러가 발생하였습니다.

ERROR 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)

 

💡 오류 발생 SQL문

- 아래와 같이 정상적으로 구성이 된 함수에서 아래의 오류가 발생하였습니다.

CREATE FUNCTION `f_rand1`(_str VARCHAR(255)) 
RETURNS varchar(31) 
BEGIN
	declare v_ret VARCHAR(31);
    declare v_len tinyint;
    
    set v_len = char_length(_str);
    set v_ret = substring(_str, CEIL(rand() * v_len), 1);
    
RETURN v_ret;
END

 

 

 

2) 해결방법

---

💡 해결방법

- 해당 오류는 DETERMINISTIC, NO SQL 또는 READS SQL DATA 등의 속성 중 하나를 선언하지 않았을 때 발생합니다.
- 이러한 속성들은 함수가 어떤 방식으로 데이터베이스와 상호작용하는지를 명시하는데, 이들이 없으면 함수가 예상치 못한 방식으로 데이터베이스에 영향을 미칠 수 있습니다.

속성	설명
DETERMINISTIC	함수가 항상 동일한 결과를 반환한다는 것을 나타냅니다. 즉, 동일한 입력에 대해 동일한 출력이 보장됩니다.
NO SQL	함수가 SQL 데이터를 읽지 않거나 쓰지 않는다는 것을 나타냅니다.
READS SQL DATA	함수가 SQL 데이터를 읽지만 쓰지 않는다는 것을 나타냅니다.

 

1. 직접적으로 명시하는 방법

---

💡 직접적으로 명시하는 방법

- 함수가 어떤 방식으로 데이터베이스와 상호작용 할지를 DETERMINISTIC 속성을 넣고 함수를 만드는 방법을 이용합니다.

CREATE FUNCTION `f_rand1`(_str VARCHAR(255)) 
RETURNS varchar(31) 
DETERMINISTIC
BEGIN
	declare v_ret VARCHAR(31);
    declare v_len tinyint;
    
    set v_len = char_length(_str);
    set v_ret = substring(_str, CEIL(rand() * v_len), 1);
    
RETURN v_ret;
END

 

 

2. MySQL 서버 시스템 변수 변경 방법 : log_bin_trust_function_creators

---

💡 Global Option 변경

- log_bin_trust_function_creators는 MySQL 서버 시스템 변수입니다.
- 이 변수가 0으로 설정되어 있으면 (기본값), MySQL은 스토어드 함수 또는 트리거가 DETERMINISTIC, NO SQL, 또는 READS SQL DATA 속성 중 하나를 갖도록 요구합니다.
- 이는 바이너리 로깅이 활성화된 경우에만 적용됩니다. 이 변수를 1로 설정하면, 이러한 요구사항을 무시하고 스토어드 함수 또는 트리거를 생성할 수 있습니다.

$ show global variables like 'log_bin_trust_function_creators';

 

 

 

 

 

💡 아래의 명령어를 통해 MySQL 서버 시스템 변수를 변경하여 문제를 해결합니다.

$ SET GLOBAL log_bin_trust_function_creators = 1; -- ON
$ SET GLOBAL log_bin_trust_function_creators = 0; -- OFF

 

 

 

3. 환경 파일(my.cnf)을 수정 방법

---

💡 환경 파일(my.cnf)을 수정 방법

- 환경 파일 내에 접근하여서 해당 문제를 대응하기 위한 값을 추가하는 방법입니다.

# Intell MacOS 경로
$ vi /usr/local/opt/mysql/my.cnf

# ARM (Apple Silicon) MacOS 경로
$ vi /opt/homebrew/etc/my.cnf

 

💡 [참고] 루트 경로에 접근하여 vi 편집기로 해당 파일을 엽니다.

- 저는 M1 MacOS를 사용하고 있기에 아래와 같은 경로로 접근하였습니다.

 

 

 

💡 아래의 속성들을 추가해 주고 저장합니다.

1. sql_mode
- MySQL 서버가 SQL 문을 해석하고 검증하는 방식을 설정하는 데 사용하는 모드입니다. 이 모드를 설정하면 서버의 SQL 모드를 변경하여 특정 동작을 활성화하거나 비활성화할 수 있습니다.

- NO_ENGINE_SUBSTITUTION : 이 모드가 활성화되면, 사용자가 지원하지 않는 스토리지 엔진을 사용하려고 하면, MySQL은 에러를 반환합니다. 이 모드가 비활성화된 경우, MySQL은 대신 기본 스토리지 엔진을 사용합니다.

- STRICT_TRANS_TABLES: 이 모드가 활성화되면, MySQL은 엄격한 SQL 데이터 유효성 검사를 수행합니다. 즉, 데이터 값이 테이블의 열 유형에 맞지 않거나 필수 열이 누락된 경우에 INSERT 나 UPDATE 문을 거부합니다.

2. log_bin_trust_function_creators
- 이 변수가 0으로 설정되어 있으면 (기본값), MySQL은 스토어드 함수 또는 트리거가 DETERMINISTIC, NO SQL, 또는 READS SQL DATA 속성 중 하나를 갖도록 요구합니다.

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log_bin_trust_function_creators=1

 

 

 

💡 환경 파일을 적용시키기 위해서는 homebrew services를 다시 실행하면 해당 환경 파일이 적용됨을 확인할 수 있습니다.

# mysql services stop
$ brew services stop mysql

# mysql services start
$ brew services start mysql

 

 

3) 결과 확인

---

💡 해당 적용 이후 Function이 잘 생성됨을 확인하였습니다.

 

 

 

 

오늘도 감사합니다. 😀