문제
ERROR 1418 (HY000) : 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)
권한 부여도 잘 했는데 위와 같은 에러가 났다.
해결
방법은 간단했다.
아래 쿼리를 먼저 실행해 주고, 원래 실행하고자 했던 쿼리를 실행해 주면 된다.
SET GLOBAL log_bin_trust_function_creators=1;어떤 원리로 문제가 해결되는지 궁금해서 찾아 봤더니,
MySQL에서 CREATE FUNCTION 문을 사용할 때 발생하는 ERROR 1418 (HY000) 에러는 주로 함수가 DETERMINISTIC, NO SQL, 또는 READS SQL DATA 등의 속성을 포함하지 않았기 때문에 발생한다. 이 문제는 MySQL의 바이너리 로깅과 관련이 있다.
바이너리 로그는 MySQL 서버가 데이터 변경 작업을 기록하는 방식으로, 복제와 데이터 복구에 사용된다. 저장 프로시저나 함수가 비결정론적이면(즉, 동일한 입력에 대해 항상 동일한 결과를 반환하지 않는다면) 복제된 서버에서 동일한 결과를 보장하기 어렵다. 그래서 MySQL은 함수가 결정론적인지 비결정론적인지 명시하도록 요구한다.
SET GLOBAL log_bin_trust_function_creators = 1; 명령어를 실행하면, MySQL 서버에 결정론적이지 않거나 SQL을 읽거나 쓰는 함수도 허용하도록 설정된다. 이렇게 하면 함수가 결정론적이거나 SQL을 사용하지 않는지 확인하지 않고도 함수를 생성할 수 있다.
- DETERMINISTIC: 함수가 같은 입력값에 대해 항상 같은 결과를 반환함을 보장한다.
- NO SQL: 함수가 SQL문을 포함하지 않음을 나타낸다.
- READS SQL DATA: 함수가 SQL을 읽지만 데이터를 변경하지 않음을 나타낸다.
이 속성들 중 하나를 포함하지 않은 함수는 기본 설정에서는 바이너리 로그에 기록되지 않기 때문에, 이를 허용하려면 log_bin_trust_function_creators 변수를 1로 설정해야 한다.
이 설정을 적용하면, 결정론적이지 않거나 SQL을 포함하는 함수를 생성할 때 발생하는 에러를 피할 수 있다. 하지만, 이 설정을 사용하면 데이터 일관성 및 복제 관련 문제를 주의해야 한다.
~~라고 한다.
요약하자면,
log_bin_trust_function_creators 변수를 설정함으로써 MySQL이 함수의 결정론적 특성을 확인하지 않게 하고, 그 결과 함수 생성 시 발생하는 에러를 피할 수 있다는 것이다.
만약 SQL 쿼리를 실행하지 못하는 상황이라면, my.ini 또는 my.cnf 파일의 [mysqld] 하위에 log_bin_trust_function_creators = 1를 추가한 뒤 서버를 재실행 하면 해결된다고 한다.
