mysql create procedure / function 에서 발생하는 오류들

1. SQL 1418 오류

 MySQL/maria DB 에서 procedure / function 을 생성할때, 아래와 같은 오류가 발생하는 경우가 있다.

/* SQL 오류 (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 서버에 접속해서 아래 변수의 값을 확인한다.

show global variables like 'log_bin_trust_function_creators'

 value 값이 off 가 나온다면 my.cnf 에 다음과 같이 설정하고 서버를 재시작한다.

log_bin_trust_function_creators = 1

2. 'create procedure syntax error'

 procedure / function 을 제대로 작성했다고 생각하고, execute 를 실행하면 아래와 같은 오류가 발생하는 경우가 있다.

/* SQL 오류 (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 8 */

 해당 라인은 "declare name varchar(80);" 이었는데, 도무지 에러가 날 이유가 없었다. 비슷한 오류를 찾아보니 procedure/function 시작과 끝 부분에 "DELIMITER" 를 붙여줘야하는 것이었다. oracle.com 에 있던 function 예제를 사용해 보았다.

CREATE FUNCTION fnGetXMLinfoVraag4 (xmlTag varchar(30),message text) returns varchar(255)
DETERMINISTIC
READS SQL DATA
begin
declare lenField int;
declare xmlTagBegin varchar(30);
declare xmlTagEnd varchar(30);
declare fieldresult varchar(255);
set xmlTagBegin = concat('<', xmlTag, '>');
set xmlTagEnd = concat('');
set lenField = length(xmlTag) + 2;
set fieldresult = case when locate(xmlTagBegin,message) = 0 then ''
else substring(message,locate(xmlTagBegin,message) + lenField,locate(xmlTagEnd,message) - (locate(xmlTagBegin,message) + lenField)) end;
return fieldresult;
end

를 다음과 같이 변경한다.

DELIMITER $$
CREATE FUNCTION fnGetXMLinfoVraag4 (xmlTag varchar(30),message text) returns varchar(255)
DETERMINISTIC
READS SQL DATA
begin
declare lenField int;
declare xmlTagBegin varchar(30);
declare xmlTagEnd varchar(30);
declare fieldresult varchar(255);
set xmlTagBegin = concat('<', xmlTag, '>');
set xmlTagEnd = concat('');
set lenField = length(xmlTag) + 2;
set fieldresult = case when locate(xmlTagBegin,message) = 0 then ''
else substring(message,locate(xmlTagBegin,message) + lenField,locate(xmlTagEnd,message) - (locate(xmlTagBegin,message) + lenField)) end;
return fieldresult;
end$$

DELIMITER ;

출처 :

http://forums.mysql.com/read.php?98,157522,240184#msg-240184

http://www.mysqlkorea.co.kr/gnuboard4/bbs/board.php?bo_table=community_03&wr_id=1965