[mysql] MYSQL CREATE FUNCTION 에러

MYSQL CREATE FUNCTION 에러

아래와 같은 FUNCTION을 생성시키려 할 때,

CREATE DEFINER=`test`@`%` FUNCTION `GET_CREATE_ID`(
    `tableName` VARCHAR(50)
)
RETURNS char(6) CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'ID 생성 함수'
BEGIN
    DECLARE id VARCHAR(50);

    UPDATE T_ID_GENERATION
    SET NEXT_ID = NEXT_ID + 1
    WHERE TABLE_NAME = tableName;

    SET id = ( SELECT CONCAT(IFNULL(PREFIX,''), LPAD(NEXT_ID,(BLOCK_SIZE - LENGTH(IFNULL(PREFIX,''))),0)) AS ID 
               FROM T_ID_GENERATION
               WHERE TABLE_NAME = TABLENAME);

    RETURN id;
END

 

아래와 같은 에러코드가 발생한다.

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)

 

이 상황엔, 아래와 같이 에러 해결

SHOW VARIABLES LIKE '%log_bin_trust_function_creators%';

SET Global log_bin_trust_function_creators = 'ON';

 

 

일반 사용자 프로시저 확인

SELECT * FROM information_schema.ROUTINES;