[MySQL]ERROR 1418:MySQL’s deterministic function

MySQL で Error 1418 発生
社内環境で検証した MySQL のストアドファンクションをお客さんの環境に引っ越そうと CREATE FUNCTION  を実行すると次のエラーが発生した

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)

社内環境だとこんなエラー起きなかったのになあぁーと思いつつ、時間がなかったので、エラーメッセージの前半に

  • DETERMINISTIC
  • NO SQL
  • READS SQL DATA

のいずれかを宣言しろと書かれており、件のファンクションはランダムな要素がなかったので DETERMINISTIC  宣言してその場は凌いだ。

エラーの原因

なんで違いが発生したのかなぁと、エラーメッセージでググると、MySQL のマニュアルに丁寧に書かれていた。

MySQL 5.0 Reference Manual :: 17 Stored Programs and Views :: 17.6 Binary Logging of Stored Programs

When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:
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)

環境によるエラー発生の有無
環境によるエラー発生の有無は、一つ下の項目に書かれていた。

To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global log_bin_trust_function_creators system variable to 1. By default, this variable has a value of 0, but you can change it like this:

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the –log-bin-trust-function-creators=1 option when starting the server.

If binary logging is not enabled, log_bin_trust_function_creators does not apply. SUPER is not required for function creation unless, as described previously, the DEFINER value in the function definition requires it.

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)

ということで

  • バイナリーロギングが行なわれていなければ、関数の宣言はゆるくても実行は可能。
  • バイナリーロギングが行なわれていても log_bin_trust_function_creators フラグ(controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log) が ON になっていれば、宣言不要。

後者の場合 untrusted な関数が紛れ込んだ時の責任は自分がもつことになる。

フラグの設定方法

フラグの確認方法

この 2つの設定値はコンソールから次のようにして確認可能。

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
2 rows in set (0.00 sec)

log-bin-trust-function-creators フラグの設定

log-bin-trust-function-creators フラグは dynamic variable なので、起動時でも起動後でも設定可能。

起動時のフラグ設定

MySQL サーバの起動オプションに –log-bin-trust-function-creators=1 を追加

起動後のフラグ変更

CLI から次のように変更する

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

エキスパートのためMySQLトラブルシューティング

@nippondanji さんのエキスパートのためのMySQL[運用+管理]トラブルシューティングガイド では§1-6-4(P.92)にバイナリログに関しての記載がある。(この本はシステム運用についての本なので、SQL やストアドファンクションについてのまとまった記述はない。)

ミッションクリティカルシステムで以下の様なケースでバイナリログ役立つと書かれている。

  • 最終バックアップからのロールフォワードリカバリ
  • 予備のレプリケーションスレーブの用意(←お客様環境ではこの目的のためにバイナリログが有効になっていた)
  • XAトランザクションの利用
Advertisements
Tagged with: , , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

%d bloggers like this: