Function 생성(ERROR 1046, ERROR 1418 ERROR 1419)

함수 및 프로시저 확인 쿼리

--함수 확인
show function  status where db = 'test';
Empty set (0.00 sec)

--프로시저 확인
show procedure  status where db = 'test';
Empty set (0.00 sec)

함수 예제 기본코드

--Function 예제 기본코드

-- 상수값 12을 반환하는 함수
DELIMITER ||
CREATE FUNCTION functionname()
RETURNS INT
BEGIN
    RETURN 12;
END;
||
DELIMITER ;


--입력값에 +2를 하는 함수
mysql> DELIMITER $$
mysql> CREATE FUNCTION test.add_2 ( my_arg INT )
    ->   RETURNS INT
    -> BEGIN
    ->   RETURN (my_arg + 2);
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql>

확인
mysql> select test.add_2(30);
+----------------+
| test.add_2(30) |
+----------------+
|             32 |
+----------------+
1 row in set (0.00 sec)

 

 

위의 함수 중 상수값 12를 반환하는 기본적인 이 함수를 Mysql에서 생성하여 사용할 수 있게 직접 테스트해보겠습니다.

 

사용 유저 : test

사용 DB : test

Mysql Version : 8.0.22

--함수 생성 시도
mysql>
DELIMITER ||
CREATE FUNCTION functionname()
RETURNS INT
BEGIN
    RETURN 12;
END;
||
DELIMITER ;


--에러 발생
Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.

 

Error Code 1046

No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar

원인 : 데이터베이스가 선택되지 않아서 발생하는 에러

 

방법1

CREATE FUNCTION DB명.함수이름 형식으로 수정 후 사용

CREATE FUNCTION TEST.functionname()

 

방법2

Mysql Workbench 사용의 경우 DB를 default Schema로 지정하기

 

cmd 창에서 Mysql을 사용할 경우 use DB명; 을 입력하여 DB를 지정해주기

use TEST;

 

지정해주고 함수를 생성하려고 보니 또 다른 에러 발생

 

mysql>
DELIMITER ||
CREATE FUNCTION test.functionname()
RETURNS INT
BEGIN
    RETURN 12;
END;
||
DELIMITER ;


--에러 발생
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)

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)

 

설명 : 이 함수에는 DETERMINISTIC,NO SQL 또는 READS SQL DATA가 없으며 바이너리 로깅이 가능합니다.

 

그래서 함수에 DETERMINISTIC옵션을 추가해보고 다시 수행해보니 다른 에러 발생

 

mysql>
DELIMITER ||
CREATE FUNCTION test.functionname()
RETURNS INT
deterministic
BEGIN
    RETURN 12;
END;
||
DELIMITER ;

--에러 발생
Error Code: 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)

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)

 

원인 : 현재 접속한 유저가 Super 권한이 없어서 그렇다고 함

해결1

Super유저 (root)로 접속해서 함수 생성

--root유저로 접속
# mysql -u root -p
Enter password:

--함수 수행
mysql>
DELIMITER ||
 CREATE FUNCTION test.functionname()
   RETURNS INT
 deterministic
 BEGIN
  RETURN 12;
END;
||
Query OK, 0 rows affected (0.01 sec)

--함수 실행 확인
mysql> select test.functionname();
+---------------------+
| test.functionname() |
+---------------------+
|                  12 |
+---------------------+
1 row in set (0.00 sec)

--함수 생성된거 확인
mysql> show function status where db='test';
+------+--------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name         | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+--------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | functionname | FUNCTION | root@localhost | 2020-12-09 19:59:58 | 2020-12-09 19:59:58 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
+------+--------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

 

 

해결2

global 시스템 변수인 log_bin_trust_function_creators 의 값을 변경해주면 됩니다.

 

기본적으로는 OFF로 되어 있는데 이 값을 ON or 1로 변경 후 생성합니다.

set global log_bin_trust_function_creators=ON; 명령어로 변경가능합니다만, 명령어로 변경 시 동적 변경이 되기 떄문에 재기동을하면 원래값으로 돌아갑니다.

영구적으로 변경을 하려면 my.cnf에

log_bin_trust_function_creators=ON 을 추가하면 됩니다.

root유저로 수행
--생성한 함수 삭제
mysql> drop function test.functionname;
Query OK, 0 rows affected (0.00 sec)


--시스템 변수 확인
 mysql> show global variables like 'log_bin_trust_function_creators' ;
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)


--시스템 변수 변경
mysql> set global log_bin_trust_function_creators=ON;
Query OK, 0 rows affected (0.00 sec)


--변경된 시스템 변수 확인
mysql> show global variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+


--(SUPER유저가 아닌) test유저로 접속
# mysql -u test -p
Enter password:

--함수 생성
mysql> DELIMITER ||
mysql> CREATE FUNCTION test.functionname()
    -> RETURNS INT
    -> deterministic
    -> BEGIN
    ->     RETURN 12;
    -> END;
    -> ||
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

--함수 확인
mysql> select test.functionname();
+---------------------+
| test.functionname() |
+---------------------+
|                  12 |
+---------------------+
1 row in set (0.00 sec)


--함수 상태 확인
mysql> show function status where db='test';
+------+--------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name         | Type     | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+--------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | functionname | FUNCTION | test@%  | 2020-12-09 20:09:17 | 2020-12-09 20:09:17 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
+------+--------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

 

함수 생성 완료.

 

Mysql에 대해서는 아는게 없어서 공부하면 할수록 새로운게 나오네요.

어서 많은 지식을 쌓아서 Mysql에 대해서 어느정도 잘 하고 싶네요

함수 생성 구문에 대해서 정리하려고 했는데 에러에 대해서 정리가 더 많아져 버렸네요,

간단할수도 있지만 제대로 정리해야지 머리속에 남을거 같아서 정리합니다.

 

참고 : riptutorial.com/mysql/example/4408/create-a-function