MySQL, MariaDB에서 CREATE FUNCTION 할 수 없음

ID를 생성하는 SQL문을 실행

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

 

문제는 MySQL, MariaDB의 시스템 변수값을 변경해 주면 해결됩니다.

mysql> SET GLOBAL log_bin_trust_function_creators = 1;