文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

MySQLでレプリケーション環境下でlog_bin_trust_function_creatorsが0のままストアドファンクションを追加すると酷い目に合います

知らないとハマります〜


f:id:treeapps:20180418131549p:plain

最近やってしまったミスの紹介です。

結論からですが、

レプリケーション環境下でlog_bin_trust_function_creatorsが0のままストアドファンクションを追加すると酷い目に合います!

サーバ構成はこんな感じで、レプリケーションはマルチマスター構成でした。

【サーバA】web01 / db01
【サーバB】web02 / db02

やってしまって例は↓こうです。

  1. log_bin_trust_function_creatorsオプションの存在を知らず、db01にストアドファンクション追加。
  2. db01で 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) が発生。
  3. 2のエラーがそのままdb02にレプリケーションされる・・・
  4. 慌ててdb01とdb02で SET GLOBAL log_bin_trust_function_creators =1; でストアド有効化。
  5. 2のエラーはスキップしても全く問題無いので、一旦 db02を stop slave; する。
  6. set global sql_slave_skip_counter = 1;を実行して、問題のストアド追加SQLをスキップ。
  7. start slave; でレプリケーション再開。
  8. 解決!!

ちなみにバイナリログの受信を止める必要は無いので、5は stop slave sql_thread; の方がよかったかもしれません。

set global sql_slave_skip_counterは、デッドロックでレプリケーションが止まった場合も有効です。