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

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にもっと踏み込めていけそうだ〜。

MySQL ストアドプログラムのバイナリロギング メモ

そもそもバイナリログって?

要するにレプリケーションするために必要なイベントが記録されているログ。

ストアドプログラムのバイナリロギング

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

  • ロギングがステートメントレベルで行われる場合、ストアドプログラムに関して該当するバイナリロギングの問題がある
    • ※ストアドプログラム -> ストアドプロシージャ、ストアドファンクション、トリガー、イベント
  • ステートメントがマスターとスレーブで別々の行に影響する場合がある
  • SQLステートメントレベルでバイナリロギングが行われるときに起こる
  • ルーチンorトリガーが実行されると行を変更は記録されるが変更をおこなったステートメントは記録されない
    • ストアドプロシージャーでいうCALLが記録されない

プログラムの内容は記録されず、行の変更のみ記録されてそれがレプリケーションに利用されるということ。

  • ストアドファンクションを利用するための条件
    • CREATE ROUTINE or ALTER ROUTINEと、SUPER 権限が必要
    • CREATE FUNCTIONの際は DETERMINISTICNO SQL または READS SQL DATA の少なくとも 1 つを明示的に指定する必要
    • 上記を緩和するために log_bin_trust_function_creators に1に設定できる
  • トリガーは DETERMINISTIC 指定がないので常に決定的
    • ただし UUID() などは注意が必要

誰かに影響を受けるということ

hb.matsumoto-r.jp

matsumotoryさんとはあまり関わりがなかったが、傍から見ていてもすごい人だなとずっと思っていた。技術的にものすごい方というのは言わずもがなだけど、すごく思慮が深くて自分の考えていることを言語化したり定型化したりするスペシャリストだと思っていて憧れの気持ちしかなかった。Twitterに「すごいエンジニア」リストを作ったときも一番にmatsumotoryさんを入れた。今後のご活躍を陰ながら応援していきたい。

またそれに対してのpyamaさんのアンサーブログがとてもよくて、胸にこみ上げるものがあった。

pyama.fun

影響を与え合うというのはとても尊いことで、本当に素晴らしいなぁと感じたので自分のことも文章にしておきたいと思った。

ここからは自分の話。ペパボに入社して一番影響を受けたのは確実にpyamaさんである。入社してからムームードメインに配属されpyamaさんがメンターとしてついた。特にあれこれ細かく指導するわけでもなく、基本は自分で考えて行動しそれについてアドバイスをくれるようなやり方だったように思う。

僕は入社して一番に見たエンジニアがpyamaさんだったので、WEB業界のエンジニアは皆このようにストイックに技術を追いかけてプロダクトを生み出し、それをアウトプットしてもっとすごいエンジニアになっていくというのが良いエンジニアであるということを教わった。一番近くで見ていたし当然相当な影響を受けた。当時の僕はpyamaさんみたいになりたいと思っていたし、同じような振る舞いをしていたように思う。

しかし、だんだんこれは自分には合わないのではないか?という疑問を感じだしたのを覚えている。pyamaさんがプロダクトを生み出して導入しては良い影響が生まれるが、それを支えて運用していくフェーズもある。そのフェーズはすごく嫌だった。僕もすごいプロダクトを生みたいし、自分の好きなものを作りたいとの気持ちがあった。それができない自分にものすごく苛立っていた。その一方、運用していくフェーズのほうが自分にはあっているのではないかと思うようになってきた。フルスタックまではいかないにしてもサーバーからフロントエンドまで支えられるような環境において広く(深くなりたい)技術スキルを習得してきて、だんだんと日々発生する運用案件をいかに効率よく捌きながら問題の根本を捉えて最短で再発しないようにするか考えられるようになってきた。自分自身それが楽しいと思えてきたし、周りからも徐々に評価されるようになってきて、自分の強みとしても捉えられるようになってきた。

そのうちにpyamaさんはシニアエンジニア、CTOとなり組織のことも考えるようになっていった。その後姿をずっと見てきた。今もロールモデルにしているのはpyamaさんだと思う。しかし純粋に同じようになりたいとは思ってなく、逆にこんなにすごい人がいるのだからその人とは違う道を進んで自分なりの強みを持って勝負していけるように意識している。

「今のペパボは職位が上がれば上がるほど、社のエンジニア育成や組織へのコミットメントが必要なフェーズなのでそれもまた一つのミスマッチの要因になったように思う。」

アンサーブログ内で上のようなという文があったのだけど、恐縮ながら陰で見守ってきた僕の個人的な気持ちとしてはpyamaさんに対しても同じことを感じている。それを原因に退職されては困る(切実)ので、そういった道に進めるように自分が支えていけるようになっていけたらいいなという気持ちをしたためておく。

支離滅裂で赤裸々な文章になってしまってpyamaさん万歳!みたいになってしまったけど、たまにはいいか。

誰かに影響を受けるということは、その人と同じように行動して同じ道を進んでいくというよりも、その人の言動を噛み砕き良いところ悪いところをきちんと捉えて、それに対して自分には何ができるのだろう、そして強みを常に考えながら行動していくということだと思いました。いつか誰かに影響を与えられるようになるぞー。まる。