[MySQL error] DDL trigger failed to create new function

특정 MySQL 유저가 새로운 함수를 생성할 때 다음과 같은 에러가 발생하였다.

SQL 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 Error [1419] 

You do not have the SUPER privilege and binary logging is enabled

(you *might* want to use the less safe log_bin_trust_function_creators variable)

 

MySQL 전역 변수 "log_bin_trust_function_creators" 가 default off 상태이며 해당 유저가 super 권한이 아닌경우 trigger 권한이 있어도 생성이 안될 수 있다.

 

해당 파라미터 값을 on 으로 변경하여 안정성이 조금 떨어지지만 일반 계정으로 생성이 가능하게 하거나 해당 유저에게 super 권한을 주어서 해결할 수 있다.

 

From dev.mysql.com:

This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that may cause unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation. See Section 25.7, “Stored Program Binary Logging”.

MySQL :: MySQL 8.0 Reference Manual :: 25.7 Stored Program Binary Logging

https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html