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 5.6 リファレンスマニュアル :: 6.2.1 MySQL で提供される権限
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.4.2.1 CHANGE MASTER TO 構文
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.6.4 KILL 構文
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.4.1.1 PURGE BINARY LOGS 構文
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.5 システム変数の使用
- MySQL ストアドプログラムのバイナリロギング メモ - blog @kimromi
このあたり理解していくとMySQLにもっと踏み込めていけそうだ〜。
MySQL ストアドプログラムのバイナリロギング メモ
そもそもバイナリログって?
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.2.4 バイナリログ
- テーブル作成操作やテーブルデータ変更などデータベース変更を記述する「イベント」が格納される
- マスターサーバーはスレーブサーバーへバイナリログに格納されているイベントを送信する
- スレーブはイベントを実行してマスターと同じデータ変更を実行する
- データを変更しない
SELECT
やSHOW
などのステートメントは使用されない- すべてをログに記録するには MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.2.3 一般クエリーログ を使う
- バイナリロギングはパフォーマンスに影響するがレプリケーションするには必須
mysqladmin flush-logs
で古いログを削除できる
要するにレプリケーションするために必要なイベントが記録されているログ。
ストアドプログラムのバイナリロギング
MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.7 ストアドプログラムのバイナリロギング
- ロギングがステートメントレベルで行われる場合、ストアドプログラムに関して該当するバイナリロギングの問題がある
- ※ストアドプログラム -> ストアドプロシージャ、ストアドファンクション、トリガー、イベント
- ステートメントがマスターとスレーブで別々の行に影響する場合がある
- SQLステートメントレベルでバイナリロギングが行われるときに起こる
- ルーチンorトリガーが実行されると行を変更は記録されるが変更をおこなったステートメントは記録されない
- ストアドプロシージャーでいうCALLが記録されない
プログラムの内容は記録されず、行の変更のみ記録されてそれがレプリケーションに利用されるということ。
- ストアドファンクションを利用するための条件
CREATE ROUTINE
orALTER ROUTINE
と、SUPER
権限が必要CREATE FUNCTION
の際はDETERMINISTIC
、NO SQL
またはREADS SQL DATA
の少なくとも 1 つを明示的に指定する必要- 上記を緩和するために
log_bin_trust_function_creators
に1に設定できる
- トリガーは
DETERMINISTIC
指定がないので常に決定的- ただし
UUID()
などは注意が必要
- ただし
誰かに影響を受けるということ
matsumotoryさんとはあまり関わりがなかったが、傍から見ていてもすごい人だなとずっと思っていた。技術的にものすごい方というのは言わずもがなだけど、すごく思慮が深くて自分の考えていることを言語化したり定型化したりするスペシャリストだと思っていて憧れの気持ちしかなかった。Twitterに「すごいエンジニア」リストを作ったときも一番にmatsumotoryさんを入れた。今後のご活躍を陰ながら応援していきたい。
またそれに対してのpyamaさんのアンサーブログがとてもよくて、胸にこみ上げるものがあった。
影響を与え合うというのはとても尊いことで、本当に素晴らしいなぁと感じたので自分のことも文章にしておきたいと思った。
ここからは自分の話。ペパボに入社して一番影響を受けたのは確実にpyamaさんである。入社してからムームードメインに配属されpyamaさんがメンターとしてついた。特にあれこれ細かく指導するわけでもなく、基本は自分で考えて行動しそれについてアドバイスをくれるようなやり方だったように思う。
僕は入社して一番に見たエンジニアがpyamaさんだったので、WEB業界のエンジニアは皆このようにストイックに技術を追いかけてプロダクトを生み出し、それをアウトプットしてもっとすごいエンジニアになっていくというのが良いエンジニアであるということを教わった。一番近くで見ていたし当然相当な影響を受けた。当時の僕はpyamaさんみたいになりたいと思っていたし、同じような振る舞いをしていたように思う。
しかし、だんだんこれは自分には合わないのではないか?という疑問を感じだしたのを覚えている。pyamaさんがプロダクトを生み出して導入しては良い影響が生まれるが、それを支えて運用していくフェーズもある。そのフェーズはすごく嫌だった。僕もすごいプロダクトを生みたいし、自分の好きなものを作りたいとの気持ちがあった。それができない自分にものすごく苛立っていた。その一方、運用していくフェーズのほうが自分にはあっているのではないかと思うようになってきた。フルスタックまではいかないにしてもサーバーからフロントエンドまで支えられるような環境において広く(深くなりたい)技術スキルを習得してきて、だんだんと日々発生する運用案件をいかに効率よく捌きながら問題の根本を捉えて最短で再発しないようにするか考えられるようになってきた。自分自身それが楽しいと思えてきたし、周りからも徐々に評価されるようになってきて、自分の強みとしても捉えられるようになってきた。
そのうちにpyamaさんはシニアエンジニア、CTOとなり組織のことも考えるようになっていった。その後姿をずっと見てきた。今もロールモデルにしているのはpyamaさんだと思う。しかし純粋に同じようになりたいとは思ってなく、逆にこんなにすごい人がいるのだからその人とは違う道を進んで自分なりの強みを持って勝負していけるように意識している。
「今のペパボは職位が上がれば上がるほど、社のエンジニア育成や組織へのコミットメントが必要なフェーズなのでそれもまた一つのミスマッチの要因になったように思う。」
アンサーブログ内で上のようなという文があったのだけど、恐縮ながら陰で見守ってきた僕の個人的な気持ちとしてはpyamaさんに対しても同じことを感じている。それを原因に退職されては困る(切実)ので、そういった道に進めるように自分が支えていけるようになっていけたらいいなという気持ちをしたためておく。
支離滅裂で赤裸々な文章になってしまってpyamaさん万歳!みたいになってしまったけど、たまにはいいか。
誰かに影響を受けるということは、その人と同じように行動して同じ道を進んでいくというよりも、その人の言動を噛み砕き良いところ悪いところをきちんと捉えて、それに対して自分には何ができるのだろう、そして強みを常に考えながら行動していくということだと思いました。いつか誰かに影響を与えられるようになるぞー。まる。