MySQLのlog-bin-trust-function-creatorsパラメータ
MySQLにlog-bin-trust-function-creatorsというパラメータがあることを知ったので、
関連するストアドファンクションの話も絡めてまとめてみた。
ストアドファンクションはストアドプロシージャに構文が似ており、通常のSQL文に
含めて使用できる。ストアドプロシージャと違い呼び出し値に戻り値を返すという特徴
がある他、セキュリティ面と性能面でいくぶん問題があるらしい。
bin-logが有効な環境において、バイナリログにファンクション名が直接格納されるため、
場合によっては危険な状況を引き起こすようなバイナリログへの書き込みがなされる
可能性もある。問題のあるステートメントがバイナリログに記録されることを抑止するため、
ストアドファンクションの作成はSUPERまたはCREATE ROUTINE(ALTER ROUTINE)
権限を持つユーザだけに限定するのが望ましい。
（MySQL5.0.12以降、ストアドプロシージャの方は実際に実行された更新系のSQL文が
格納されるようになったため、この問題は解消している）
さらにファンクションをDETERMINISTIC / READS SQL DATA / NO SQL いずれかの
特性を持った形で作らなければならないという制限を課すため、デフォルトで
log-bin-trust-function-creatorsは無効となっている。
実行可能な条件を緩和させるためにはlog-bin-trust-function-creatorsを有効にする。
（MySQL5.0.16から導入。起動時のオプション、my.cnfに記述、システム変数のSET
にて1で有効、いずれかを使用）
ストアドファンクションの性能面の問題
ストアドファンクションの中ではトランザクションの開始／終了ができない。
START TRANSACTION / SET AUTOCOMMIT / COMMIT / ROLLBACKを含む
ストアドファンクションは作成時にコンパイラエラーとなる。
またストアドファンクションは処理の最初の時点でアクセスするすべてのテーブルに対し、
バイナリログの不整合を防ぐためにテーブルレベルのロックをかける。このためストアド
ファンクションを多用するとロック競合におけるパフォーマンスの低下につながるおそれがある。
ストアドプロシージャとストアドファンクションの違いを簡単にまとめると、
以下のようになるものと思われる。
ストアドプロシージャ
単に呼び出して実行する トランザクション開始によりコミットが走る
ストアドファンクション
呼び出して実行したら何らかの結果を呼び出し元に返す トランザクションの開始はできない 実行中テーブルロックがかかり、他のセッションは検索や更新ができない
自分は実際には両方とも使用したことがないので、よくわかっていない部分もあるのだが、、、
こうして書いておかないと、「勉強した」という事実さえ忘れてしまうから、ね。