MySQL Error Code 1418 원인과 해결 방법

MySQL Error Code 1418 원인과 해결 방법

MySQL에서 에러 코드 1418은 주로 저장 프로시저나 함수를 생성할 때 발생하는 에러로, 다음과 같은 메시지를 동반합니다.

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)

SQL Statement: 

이 에러는 함수나 프로시저가 선언될 때 DETERMINISTIC, NO SQL, 또는 READS SQL DATA 옵션 중 하나를 포함하지 않았고, 바이너리 로그가 활성화되어 있을 때 발생합니다. 이는 주로 복제 환경에서 데이터의 일관성을 유지하기 위한 MySQL의 안전장치 때문입니다.

원인

MySQL의 복제 및 바이너리 로그 환경에서, 저장 프로시저와 함수가 동일한 입력에 대해 항상 동일한 출력을 제공하는지 보장하는 것이 중요합니다. 그렇지 않으면 데이터 불일치가 발생할 수 있습니다. 따라서 다음 세 가지 속성 중 하나가 명시적으로 선언되어야 합니다:

• DETERMINISTIC: 함수가 동일한 입력에 대해 항상 동일한 결과를 반환하는지 명시합니다.
• NO SQL: 함수가 데이터베이스의 테이블을 읽거나 쓰지 않는다는 것을 명시합니다.
• READS SQL DATA: 함수가 데이터베이스의 테이블을 읽지만 쓰지 않는다는 것을 명시합니다.

이 속성들이 명시되지 않은 함수나 프로시저는 안전하지 않다고 간주되며, 바이너리 로그가 활성화된 환경에서는 오류를 발생시킵니다.

해결 방법

이 문제를 해결하는 방법은 두 가지가 있습니다:

1. 함수나 프로시저 선언 시 적절한 속성 추가
2. MySQL 설정 변경

방법 1: 함수나 프로시저 선언 시 적절한 속성 추가

함수나 프로시저를 선언할 때 DETERMINISTIC, NO SQL, 또는 READS SQL DATA 중 하나를 추가하면 됩니다. 예를 들어, 앞서 예제로 사용한 함수 AddNumbersFunction을 수정해보겠습니다.

DELIMITER // 
CREATE FUNCTION AddNumbersFunction(num1 INT, num2 INT) RETURNS INT 
DETERMINISTIC 
BEGIN 
	RETURN num1 + num2; 
END // 
DELIMITER ;

위와 같이 함수 선언에 DETERMINISTIC을 추가하여 함수가 동일한 입력에 대해 항상 동일한 결과를 반환한다고 명시하면 됩니다.

방법 2: MySQL 설정 변경

MySQL 설정에서 log_bin_trust_function_creators 변수를 1로 설정하면, MySQL은 사용자 정의 함수나 프로시저가 안전하지 않더라도 이를 허용하게 됩니다. 이는 my.cnf (또는 my.ini 파일)에서 설정하거나, MySQL 세션에서 동적으로 설정할 수 있습니다.

• 설정 파일에서 설정:
   
  my.cnf 파일에 다음 설정을 추가합니다:
  • MySQL 설정 파일은 OS마다 이름이 다릅니다.
    • Mac 또는 Linux의 경우 my.cnf
    • Windows의 경우 my.ini

[mysqld] log_bin_trust_function_creators = 1

           그런 다음 MySQL 서버를 재시작합니다.

 

• 동적으로 설정:

  SET GLOBAL log_bin_trust_function_creators = 1;

  이 설정은 서버가 재시작되면 초기화되므로, 영구적으로 설정하려면 설정 파일을 수정해야 합니다.
• MySQL 세션에서 다음 명령을 실행합니다:

결론

MySQL Error Code 1418은 저장 프로시저나 함수가 DETERMINISTIC, NO SQL, 또는 READS SQL DATA 속성 없이 선언되고 바이너리 로그가 활성화된 환경에서 발생하는 에러입니다. 이를 해결하기 위해 함수나 프로시저 선언 시 적절한 속성을 추가하거나, MySQL 설정을 변경하여 비신뢰 함수나 프로시저를 허용할 수 있습니다. 각각의 방법을 상황에 맞게 선택하여 적용하면 이 문제를 쉽게 해결할 수 있습니다.

 

.cnf 파일은 아래 경로 중에 있습니다.

• /etc/my.cnf
• /etc/mysql/my.cnf
• /usr/local/mysql/etc/my.cnf
• ~/.my.cnf

Windows 라면 아래 경로 중에 있습니다.

• C:\Windows\my.ini
• C:\Windows\my.cnf
• C:\my.ini
• C:\my.cnf
• C:\Program Files\MySQL\MySQL Server x.x\my.ini
• C:\Program Files\MySQL\MySQL Server x.x\my.cnf