今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQLでストアドファンクションであったエラーについての日。

目次

とある日

LeetCodeで問題を解いていてストアドファンクションの問題があったので解いてて遭遇したエラーについて記録。

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

発生時のSQL

mysql> DELIMITER $$
mysql> CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    -> BEGIN
    -> DECLARE out_put int;
    -> SELECT salary into out_put from nth order by salary limit 1 offset N;
    ->   RETURN out_put;
    -> END$$
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)
mysql> 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)

エラー 1418 (HY000)。この関数は、宣言に DETERMINISTIC、NO SQL、READS SQL DATA のいずれも含んでいません。 バイナリログが有効である場合(安全性の低い log_bin_trust_function_creators 変数を使用したい場合があります)。

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

回避方法

グローバル log_bin_trust_function_creators システム変数を1に設定

デフォルトをではオフになってます。

mysql> show 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 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

log_bin_trust_function_creators とは

この変数は、バイナリロギングが有効になっている場合に適用されます。これは、保存された関数の作成者が、安全でないイベントがバイナリログに書き込まれる原因となる可能性のある保存された関数を作成しないことを信頼できるかどうかを制御します。0(デフォルト)に設定すると、ユーザーはSUPER 、CREATE ROUTINEまたはALTER ROUTINE特権に加えて特権を持っていない限り、保存された関数を作成または変更することはできません。0の設定は、関数で宣言されなければならないという制約強制 DETERMINISTIC特性、またはで READS SQL DATAまたはNO SQL特性。変数が1に設定されている場合、MySQLは保存された関数の作成にこれらの制限を適用しません。この変数は、トリガーの作成にも適用されます。セクション25.7「ストアドプログラムのバイナリログ」を参照してください。

ちなみに、バイナリロギングはMySQL 8.0以降、デフォルトで有効になっており 、起動時に--skip-log-bin または --disable-log-binオプションを指定した場合にのみ無効になります 。

DETERMINISTICを追加

権限が別れてて開発者用のロールの場合は上記の方法は取れないのでこちらになるかなと。

元のストアドファンクション

DELIMITER $$
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    DECLARE out_put int;
    set N = N - 1;
    SELECT salary into out_put from nth group by salary order by salary asc limit 1 offset N;
  RETURN out_put;
END$$
DELIMITER ;

DETERMINISTIC追加のストアドファンクション

DELIMITER $$
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE out_put int;
    set N = N - 1;
    SELECT salary into out_put from nth group by salary order by salary asc limit 1 offset N;
  RETURN out_put;
END$$
DELIMITER ;

↓実行

mysql> DELIMITER $$
mysql> CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE out_put int;
    -> set N = N - 1;
    -> SELECT salary into out_put from nth group by salary order by salary asc limit 1 offset N;
    ->   RETURN out_put;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

まとめ

ストアドファンクション作成時にバイナリログに影響を与える場合があるためファンクションの内容で明示的に指定するか、変数で指定があっても無視するのかを指定しないと作成できないようになっている。

ストアドファンクションって久々に作るとどうやってやるっけとなる。