レンタルサーバー・自宅サーバー設定・構築のヒント
レンタルサーバー・自宅サーバー設定・構築のヒント - レンタルサーバー・自宅サーバーの設定・構築情報を公開しています。

mysqlでUnsafe statement written to the binary log using statement の警告が出た時の対処

2013年8月7日 2014年1月8日
mysql warning Unsafe statement

mysql のレプリケーションを使っている時、以下のような警告メッセージが出力されることがあります。

130529 12:22:26 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_feed_mod_57bc725ad6568758915363af670fd8bc', '1369797746', 'no') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)
130529 12:22:26 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_timeout_dash_de3249c4736ad3bd2cd29147c4a0d43e', '1369840946', 'no') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)

この警告の意味は、直訳すると以下のような意味あいになります。

安全でないステートメント(SQL)が、ステートメント 形式でロギングされている バイナリログへ書き込まれた。

ステートメント 形式は、バイナリログフォーマットの一つで、そのバイナリログフォーマットには、以下の3種があります。

  • row : 実際に変更された行のデータが記録されます。
  • statement : 実際に実行された SQL が記録されます。
  • mixed : 危険な関数をつかった場合などには row で記録し、そうでなければ statement で記録します。

つまり、発行されたSQLは、ログとしては危険ですよ・・・という意味で、もっと意訳すれば、
レプリケーションの各スレーブ側のサーバーで同じSQLを実行しても必ずしも同じになるとは限らないSQLが発行された・・・ということでしょうか。

今回は、この警告が出力された場合の対処を簡単に解説してみます。

mysqlでUnsafe statement written to the binary log using statement の警告が出た時の対処

mysqlでUnsafe statement written to the binary log using statement の警告が出た時の対処方法は、2つだと思います。

binlog_format = mixed を使用する

この方法が最も用いられている対処方法だと思います。
単純に バイナリログフォーマットを statementmixed へ変更する方法です。

  1. 全てのスレーブ側サーバーでスレーブを停止する
    mysql> STOP SLAVE;
  2. マスターサーバーにて、に対して、更新系クエリをロック(一時停止)するして更新系のクエリを止める
    mysql> FLUSH TABLES WITH READ LOCK;
  3. マスターサーバーにて、ログファイルの再オープンを実施する
    mysql> FLUSH LOGS;

    全てのログ ファイルを閉じ、再オープンします。もしバイナリ ログが有効であれば、バイナリ ログ ファイルのシーケンス番号は前のファイルと比較して1つ増加されます。

  4. マスターサーバーにて、ログフォーマットを mixed へ変更する
    mysql> SET GLOBAL binlog_format = 'mixed';
  5. マスターサーバーにて、ログファイルの再オープンを実施する
    mysql> FLUSH LOGS;

    全てのログ ファイルを閉じ、再オープンします。もしバイナリ ログが有効であれば、バイナリ ログ ファイルのシーケンス番号は前のファイルと比較して1つ増加されます。

  6. マスターサーバーにて、に対して、更新系クエリのロック(一時停止)を解除する
    mysql> UNLOCK TABLES;
  7. 全てのスレーブ側サーバーでスレーブを開始する
    mysql> START SLAVE;
  8. マスター側スレーブ側の /etc/(mysql/)my.cnf のバイナリ ログフォーマットの設定を変更する
    [mysqld]
    ...
    binlog_format=mixed
    ...
レプリケーションを使用していない場合は、マスター の部分の設定のみを行うと良いでしょう。

安全なステートメントとなるように変更する

先に示した警告メッセージについて簡単に解説してみましょう。

...
130529 12:22:26 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: 
  INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_feed_mod_57bc725ad6568758915363af670fd8bc', '1369797746', 'no') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)
...

警告メッセージの最後に問題となったSQL文が付いています。
そのSQL文では、wp_options へ行を挿入する際に警告メッセージが出力されたことがわかります。
wp_options のテーブルは、以下のとおりです。

CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
);

option_id : プライマリキー
option_name : ユニークキー
となっています。
ここで問題なのが、ON DUPLICATE KEY UPDATE の対応です。
先の問題となったSQLにも INSERT INTO ... ON DUPLICATE KEY UPDATE ... のようにON DUPLICATE KEY UPDATE が設定されています。
ON DUPLICATE KEY UPDATE は、プライマリキーあるいはユニークキーで二重キーとならないように、もし、同じキーが存在したら、行挿入せずに、既存の行のON DUPLICATE KEY UPDATE 以降に指定するカラム名毎のデータを設定するように指示するものです。

例えば、以下のようにwp_optionsにデータが設定されてあったとします。

mysql> select * from wp_options;
+-----------+-------------+--------------+----------+
| option_id | option_name | option_value | autoload |
+-----------+-------------+--------------+----------+
|         1 | 'abc'       | 'option1'    | 'yes'    |
+-----------+-------------+--------------+----------+
|         2 | 'efg'       | 'option2'    | 'no'     |
+-----------+-------------+--------------+----------+

この状態に以下のようなSQL文を発行します。

mysql> INSERT INTO `wp_options` (`option_id`,`option_name`,`option_value`,`autoload`) 
              VALUES (2,'abc', 'option3', 'autoload') 
              ON DUPLICATE KEY UPDATE `autoload` = 'off';

これだと、option_id は、既存の2行目と一致、option_name は、既存の1行目と一致します。
つまり、ON DUPLICATE KEY UPDATE 以降の `autoload` = ‘off’ は、1行目に対して実行すべきか、2行目に対して実行すべきかわからないのです。

これは、ちょっと極端な例ではありますが、理屈は同じです。 先の警告メッセージが出力された例では、`option_id` を指定していないだけです。 `option_id` は、AUTO_INCREMENTになっているので、省略しているのですが、レプリケーションでは、マスター、スレーブで実行して同じ `option_id` になる保障はないことから、警告メッセージが出力されます。

つまり、もし、これらの警告メッセージを回避するためには、これらのSQL文を見直す必要があるということになります。

もし、今から、これらのテーブルを設計するのなら、検討の価値はあります。
しかし、Wordpressのテーブルのように 既に利用している場合、これは、非常に、非現実的ですね。だから、上記のバイナリログフォーマットを変更することが現実的なんですね。


ちょっと長くなりましたが、警告メッセージの意味を理解していると、後々の対応が楽になることがあります。 同じような警告メッセージが出力されても慌てることなく対処できるようになると思いますので、少し、細かい解説をしてみました。

何か参考になればうれしく思います。
ご利用のブラウザは、広告ブロック(AdBlockなど) が適用となっていませんか?
このサイトでは、コンテンツの一部が非表示、あるいは、コメント、お問い合わせの投稿ができない検索ができないことがあります。


関連記事 :

MySQLのログの種類とログの仕方を調べてみた(実施例)

今回は、MySQLでロギングを行ってみます。 一口にロギングと言ってもMySQLでは、以下の4つの種類があります。 エラー ログ ...

mysqlでマルチマスター レプリケーションを設定してみる

以前の記事 mysqlのレプリケーションを使う で mysqlを使ったレプリケーションの設定を行ってみました。 今回は、そのmysqlのレプリ ...

MySQLのバイナリログを使ったロールバック(リストア)を行ってみる

以前の記事 MySQLのログの種類とログの仕方を調べてみた(実施例) で、MySQLのログの採取方法について簡単に解説してみました。 その中の ...

Debian(Ubuntu) で PHP をインストールしてもデフォルトでSQLiteは動作しない

Debian(Ubuntu) でもRedHat系と同じようにバイナリパッケージをインストールできます。 そこで、PHPのバイナリパッケージをイ ...

mysqlのレプリケーションを使う(3) エラー1062の対処

以前に mysqlのレプリケーションを使う mysqlのレプリケーションを使う(2) バイナリログを削除する で、m ...



コメントを投稿 :

お名前 *

メールアドレス *
( メールアドレスが公開されることはありません。)

サイトアドレス

コメント *

* 印の項目は、入力が必要な項目です。




最近投稿の記事

[ 画像提供元 : Amazon ] 先日、1TBのディスクの入れ替え時にバックアップをとろうとディスクコピーを行いました。 その際 ...

Windows で Linux ファイルシステム Ext4 のディスクをマウントするには? Ext3Fsd が、おそらく、最も簡単なツール ...

今回は、Windows で Compass を使ってみました。 Compass とは、 Sass(サス、Syntactica ...

今回は、Anti Adblock を使ってみました。 Anti Adblock とは、 そもそも Adblock という ウェブ ...

デスクトップ環境でない サーバーで、Webページのキャプチャー画像をコマンドで撮る には、wkhtmltoimage, CutyCapt ...


さくらのVPS 全プラン リニューアルです。(石狩(北海道)も選択可)


root権限ありで ¥685 / 月 ~ と非常にリーズナブルな CPU 1(core)の 512 プランから、 最高 CPU 10(core), メモリ 32(GB), SSD容量 800(GB) までとプランが充実。
ディスクは、SSDとHDDの選択が可能になった他、データセンターは人気の東京、石狩(北海道)となりました。

また、どのプランでも好きなOSが選べます
( CentOS, Fedora, Scientific Linux, FreeBSD, Ubutu, Debian )

管理人もおすすめのVPSです。
試用期間がありますから、一度、お試しを!!

詳しくは、http://vps.sakura.ad.jp/さくらのVPSのサイトへ へどうぞ!!

カテゴリ


Serverman@VPS 完全1ヶ月無料 キャンペーン実施中です。


Serverman@VPS 完全1ヶ月無料 キャンペーン実施中です。
最近、スワップにも対応した Serverman@VPS は、かなりリーズナブルかもです。

  • メモリ1GB~2GBのEntryプラン :月額:490円
  • メモリ2MB~4GBのStandardプラン :月額:980円
  • メモリ4GB~8GBのProプラン :月額:1,980円

新規申し込みで1ヶ月間完全無料となるキャンペーンを実施中です。
Serverman@VPSの特徴は、安さとIPv6対応です。また、初期設定費0円もポイントです。

IPv6でちょっと遊んでみたい方には、おすすめかもしれませんね。最低利用期間もありませんから、気に入らないときは即解約もできます。

Serverman@VPSの詳細については、 http://dream.jp/vps/ Serverman@VPSのサイトへへどうぞ。



KVM採用 ConoHa VPSは、時間単位で借りれる便利なVPSです。


ConoHa VPS は、初期設定費0円最低利用期間無し時間単位で清算可能、 さらに、Web APIを使って自動化を図ることもできる便利なVPSです。

海外サーバー設置も可能で、ローカル接続にも対応と、かなり、機能豊富なサーバーです。

新規ユーザ登録で、クーポンもらえますから、まずは、お試しですね。

ConoHa VPSの詳細については、
http://www.conoha.jp/ へどうぞ。

KVM採用 お名前.com VPS(KVM) 2G プラン 初期設定費無料 キャンペーン 実施です。


メモリ2GBプラン CPU:3core、Disk:200GB
月額:1,153円から (初期設定:1,680円0円)

さくらのVPSがリニューアルされてもなんのその。
1GBメモリ / 2Core を ¥834 – の格安価格で提供中です!
間違いなくスペックからすると割安感ありです。
年間割引時の途中解約で返金がないのは、 ちょっと残念ですが、それでもこの割安感は魅力です。

まずは、お試しですね。

お名前.com VPS(KVM)の詳細については、
http://www.onamae-server.com/vps/ へどうぞ。(お試し期間が15日あります。)



  • ソーシャルブックマーク

  • はてなブックマークへ追加するはてな登録数
ページトップへ
Time : 0.6026 [s]