タグ: SQLアンチパターン

Tweet

MySQLにて、カテゴリなどの階層構造のデータの持ち方を、書籍「SQL アンチパターン」を参考に「経路列挙型」で保持してみたところ、UPDATE にて下記の Warning が発生。

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.


ブログ用にちょっと書き換えましたが、UPDATE 文の内容はだいたい下記のような内容。

UPDATE category SET 
  category_c_path = (
    SELECT temp_cd.category_c_path FROM (
      SELECT category_c_path FROM category WHERE category_i_id = 1
    ) as temp_cd
  ) || LAST_INSERT_ID() || "/"
WHERE category_i_id = LAST_INSERT_ID();

自分自身のテーブルからデータを取得して UPDATE をかけてるので、マスタとスレーブでデータが異なってしまう場合があるよ、ということらしい。

調べてみたところ、BINLOG_FORMAT を MIXED にすれば、危険な関数を使用した場合には ROW（実際に変更された行データを記録）モードになり、それ以外は STATEMENT（実際に実行されたSQLを記録）モードになるとのこと。

危険な関数は使用してないけど、今回のケースでも適用できるのかな？と疑問に思いつつ試してみる。

ということでまずは、SET SESSION で現在の接続だけを MIXED に変更してテスト。

SET SESSION binlog_format = 'MIXED';

# 設定が MIXED に変わっていることを確認
SHOW SESSION VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

# 一応、グローバルは STATEMENT のままなのを確認
SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

問題の SQL を実行してみて Warning が発生しないことを確認。
問題がなければ、グローバルの設定も変更。

SET GLOBAL binlog_format = 'MIXED';

あとは、再起動後も設定が保持されるように、my.cnf を編集。

vim /etc/my.cnf

[mysqld]
・・・
# レプリケーションフォーマット
binlog_format = MIXED

これにて設定完了。
my.cnf への記述が間違ってないか心配なら、再起動して確認をしておいた方が無難かな。

service mysqld restart

SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

「SQLアンチパターン」はテーブル設計の仕方など、かなり参考になる良書なのですが、今回のような環境によって Warning が発生するケースがある場合は、注釈あたりで MIXED にするといいよって書いてあると、より親切だな～と思いました。

参考サイト
MySQL :: MySQL 5.1 リファレンスマニュアル :: 5.1.2.1 レプリケーション フォーマットのセッティング

MySQL – BINLOG_FORMAT 関連のエラーログ！ – mk-mode BLOG

Tweet