MySQLでCREATE TRIGGERができなかったので調べてみた

MySQL Memo 
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)

というエラーが出ていて CREATE TRIGGER ができなかった。

結論から言うと、 CREATE TRIGGER などのストアドファンクションの作成、変更はバイナリログが有効な環境の場合にSUPER権限が必要であるが、CREATE TRIGGERを実行しようとするユーザーがSUPER権限を持っていないということだった(エラーメッセージそのままだけど)。

以下の方法で解決できることがわかった。

  • DBに接続するユーザーにSUPER権限をもたせる
  • log_bin_trust_function_creatorsというグローバルパラメータを1にする

👉SUPER権限でできること(MySQL5.6)

  • CHANGE MASTER TO
    • スレーブがマスターに接続する設定をする構文
  • KILL または mysqladmin kill コマンド
    • 実行中のクエリを強制終了させる構文
  • PURGE BINARY LOGS
    • バイナリログを削除する構文
  • SET GLOBAL
    • システムのグローバル設定を変更する構文
  • mysqladmin debug コマンド
  • ロギングの有効化または無効化
  • read_only システム変数が有効な場合の更新の実行
  • max_connectionsに接続数が達しても1つの接続を受け入れる
  • スレーブサーバー上でのレプリケーションの開始と停止
  • バイナリロギングが有効な場合のストアドファンクション作成、変更の権限

またSUPER権限はグローバル権限であるため、システム全体に影響を及ぼすことができる権限である。安易にSUPER権限をもたせてしまうと様々な管理者用の機能を利用できてしまうため危険。ちなみにあるデータベースやテーブルのみのSUPER権限は存在しない。

👉log_bin_trust_function_creators パラメータ

MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.7 ストアドプログラムのバイナリロギング を参考。

ストアドファンクションを利用するための条件は以下。

  • CREATE ROUTINE または ALTER ROUTINE権限以外に、SUPER権限が必要
  • CREATE FUNCTIONの際は DETERMINISTIC 、 NO SQL または READS SQL DATA の少なくとも 1 つを明示的に指定する必要
    • ファンクションの実行によるデータの変更が確定的である、またはデータを変更しないということを明示
    • 例えば関数内でUUID()などを利用すると実行のたびに結果が違うためレプリケーション時にマスターとスレーブの整合性がとれなくなる
    • ちなみにTRIGGERはDETERMINISTICであるため構文に指定する必要はないが、UUID()などは注意が必要

そして、上記を緩和するためにlog_bin_trust_function_creators パラメータを1にすることでストアドファンクションを作成、変更することができるようになる。しかし上記のようなレプリケーションの整合性の問題を理解し危険がないということを確認の上、設定することが必要ということだった。

その他参考URL

このあたり理解していくとMySQLにもっと踏み込めていけそうだ〜。