2012-09-14

CakePHP 2.x系のbindModel＋recursive=2はちょっとヤバイ？

CakePHP

CakePHPで3テーブル以上をbindModelで結合することってよくありますよね？

しかも、その後に、findメソッドにrecursive=2を指定することってよくありますよね？（汗

実はbindModelとrecursive>=2の組み合わせはSQLの発行回数が爆発的に増えてしまうことがあるんです。

そこで、今回はテーブル結合時に発行されるSQLの発行回数を減らす方法を述べたいと思います。

まずはサクっと今回使用するテーブルについて

3テーブル用意しました。

テーブルのレイアウトは次のとおりです。

usersテーブル

id	name	created	modified
1	初音ミク	2012-08-18 13:24:52	2012-08-26 13:24:52

ownsテーブル

id	user_id	music_id	created	modified
1	1	1	2012-08-26 13:24:52	2012-08-26 13:24:52
2	1	2	2012-08-26 13:24:52	2012-08-26 13:24:52
3	1	3	2012-08-26 13:24:52	2012-08-26 13:24:52

musicsテーブル

id	name	created	modified
1	みくみくにしてあげる♪【してやんよ】	2012-08-26 13:24:52	2012-08-26 13:24:52
2	メルト	2012-08-26 13:24:52	2012-08-26 13:24:52
3	ワールズエンド・ダンスホール	2012-08-26 13:24:52	2012-08-26 13:24:52

ユーザ(usersテーブル）は複数の曲を所持することが可能（ownsテーブル）で、曲の詳細情報はmusicsテーブルに入っている、という作りにしています。

リレーションはusers ： owns ： musics ＝ 1 ：多： 1というよくある構成です。

次にこの3テーブルを結合してミクちゃんが所持している曲の一覧を取得します。

ミクちゃんの所持する曲を取得するプログラム（bindModelバージョン）

public function getMusics($user_id) {
    $this->bindModel(
        array(
            'hasMany' => array('Own'),
        )
    );
    ClassRegistry::init('Own')->bindModel(
        array(
            'belongsTo' => array('Music'),
        )
    );
    $ret = $this->find('all',
        array(
            'conditions' => array(
                'User.id' => $user_id,
            ),
            'recursive' => 2,
        )
    );
    return $ret;
}

このメソッドはUser.phpにあるとします。

usersテーブルとownsテーブルは1対多、ownsテーブルとmusicsテーブルは多対1の関係にあるので、それぞれhasMany、belongsToを使用してアソシエーションの設定を行っています。

私は普段bindModelを使用することがないので間違っていたらゴメンナサイ。

何故だかわからないですが、CakePHPでは結合するときにbindModelを使用するのが一般的みたいです。

リレーションを意識してhasManyやbelongsToなどのキーワードを使い分けしなければならないので、少し面倒くさいですね。

アソシエーションの設定が完了したら、findメソッドを使用してレコードを取得するだけなのですが、この時にrecursive=2を指定するのを忘れてはいけません。

これがないとメインテーブル（usersテーブル）の孫テーブル（musicsテーブル）のレコードを引っ張ってこないので注意が必要です。

bindModel＋recursive=2の場合に発行されるSQLは？

上記のfindメソッドが発行するSQLは次のようになります。

SELECT `User`.`id`, `User`.`name`, `User`.`created`, `User`.`modified` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1
SELECT `Own`.`id`, `Own`.`user_id`, `Own`.`music_id`, `Own`.`created`, `Own`.`modified` FROM `LAA0200849-cakephp`.`owns` AS `Own` WHERE `Own`.`user_id` = (1) 
SELECT `Music`.`id`, `Music`.`name`, `Music`.`created`, `Music`.`modified` FROM `LAA0200849-cakephp`.`musics` AS `Music` WHERE `Music`.`id` = 1
SELECT `Music`.`id`, `Music`.`name`, `Music`.`created`, `Music`.`modified` FROM `LAA0200849-cakephp`.`musics` AS `Music` WHERE `Music`.`id` = 2
SELECT `Music`.`id`, `Music`.`name`, `Music`.`created`, `Music`.`modified` FROM `LAA0200849-cakephp`.`musics` AS `Music` WHERE `Music`.`id` = 3

(°д°)ﾊｧ?

テーブル毎にSQLを発行しています。

しかも、孫テーブルに至っては、レコード毎にSQLを発行しています。

これではミクちゃんの所持する曲が増えればその分、SQLの発行回数も増えてしまいます。

こういった問題があるため、私は結合する時にはbindModelの代わりにjoinsを使用しています。

ミクちゃんの所持する曲を取得するプログラム（joinsバージョン）

public function getMusics($user_id) {
    $ret = $this->find('all',
        array(
            'fields' => array(
                'User.id', 'User.name', 'Music.name',
            ),
            'conditions' => array(
                'User.id' => $user_id,
            ),
            'joins' => array(
                array(
                    'type' => 'INNER',
                    'table' => 'owns',
                    'alias' => 'Own',
                    'conditions' => 'User.id = Own.user_id',
                ),
                array(
                    'type' => 'INNER',
                    'table' => 'musics',
                    'alias' => 'Music',
                    'conditions' => 'Own.music_id = Music.id',
                ),
            ),
        )
    );
    return $ret;
}

findメソッドの第2引数の配列の中にjoinsという項目があります。

これが結合条件を指定するためのものです。

joinsに指定できる項目は次のとおりです。

項目	意味
type	結合の種類（内部結合：INNER、左外部結合：LEFT）
table	テーブル物理名
alias	テーブル論理名
conditions	結合条件（aliasで指定した論理名で指定します）

サンプルプログラムを見ていただけたら、直感的に使い方がわかると思います。

bindModelよりjoinsの方が親しみやすさを感じるのは私だけでしょうか？

あと、joinsを使用する際に1つだけ注意点があります。

それはfieldsに取得カラムを指定する必要があるということです。

fieldsにカラム指定をしなかった場合は、メインテーブル（usersテーブル）のカラムしか取得しません。

まぁ、普段からfieldsに取得カラムをきちんと指定する人にとっては、全く苦にならないでしょう。

joinsを使用した場合に発行されるSQLは？

最後にjoinsを使用したfindメソッドが発行するSQLをお見せします。

SELECT `User`.`id`, `User`.`name`, `Music`.`name` FROM `LAA0200849-cakephp`.`users` AS `User` INNER JOIN `LAA0200849-cakephp`.`owns` AS `Own` ON (`User`.`id` = `Own`.`user_id`) INNER JOIN `LAA0200849-cakephp`.`musics` AS `Music` ON (`Own`.`music_id` = `Music`.`id`) WHERE `User`.`id` = 1

想定どおりのSQLになっていますね^^

2012-09-08

CakePHP 2.x系の更新時のSQL発行回数を減らしたい

CakePHP

今年7月に入ってから業務でCakePHPを使用しており、外国人プログラマ達によって書かれたソースの改修とパフォーマンス・チューニングを行っています。

その際、DB更新処理でSQLの発行回数を減らす必要が生じたので、その対応方法を述べたいと思います（需要はあまりないかもなぁ）

saveメソッドのSQL発行回数は多い？

DBの更新処理にはsaveメソッドを使用することが多いと思いますが、実はこいつ、かなりの曲者です。

百聞は一見に如かずです。

まず下のメソッドを見てください。

public function addPoint($user_id, $point) {
    $user = $this->findById($user_id);
    $user['User']['point'] += $point;
    $this->save($user);
}

実際に今の案件で散見されるsaveメソッドの使い方です。

上記のメソッドはUser.phpというモデルに書かれているとします。

ユーザが保持しているポイントを加算するだけです。

ちなみに、usersテーブルのフォーマットは次のとおりです。

usersテーブル

カラム	値
id	1
name	初音ミク
point	1000
created	2012-08-18 21:24:44
modified	2012-08-18 21:24:44

saveする前に何故かfindしているのが気になりますが（後述）、特に問題がなさそうなプログラムである、と最初はそう思っていました。

このメソッドをコントロール側で「$this->User->addPoint(1, 50);」のように呼び出すとミクちゃんのポイントが50加算されます。

しかし、これを実現するためにCakePHPが発行するSQLを見て驚きました。

SELECT `User`.`id`, `User`.`name`, `User`.`point`, `User`.`created`, `User`.`modified` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1 LIMIT 1
SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1
SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1
SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1
UPDATE `LAA0200849-cakephp`.`users` SET `id` = 1, `name` = '初音ミク', `point` = 1050, `created` = '2012-08-18 21:24:44', `modified` = '2012-08-18 21:24:44' WHERE `LAA0200849-cakephp`.`users`.`id` = '1'

(°д°)ｴｯ?

SQLが5回も発行されており、しかもそのうち3個は同じSQLです。

というわけで、何故このようなSQLが発行されるのか調査しました。

どこでSQLが発行されているのか？

1つ目のSQL

SELECT `User`.`id`, `User`.`name`, `User`.`point`, `User`.`created`, `User`.`modified` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1 LIMIT 1

これは例で提示したメソッドの１行目「$user = $this->findById($user_id);」で発行されています。

「（ ´_ゝ｀）ふーん、だったらいいじゃん」と思われるかもしれませんが、よ～く見てください。

usersテーブルの全カラムを取得する必要があるでしょうか？

更に言うと、このSQLそのものが不要です。

何故ならupdateする前に、わざわざselectで更新カラムの値を取得することはないでしょう。

この部分は改善の余地がありそうです。

2つ目のSQL

SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1

これは/cakephpのルート/lib/Cake/Model/Model.phpの1634行目で発行されています。

$exists = $this->exists();

レコードの存在チェックをして、insertとupdateのどちらを行うか判断しています。

あっ、それと言い忘れていましたがCakePHP 2.2.1の場合で説明しています。

バージョンが違うと行番号がズレる可能性がありますのでご注意を。

3つ目のSQL

SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1

これは /cakephpのルート/lib/Cake/Model/Model.phpの1643行目のvalidateメソッドで発行されています。

if ($options['validate'] && !$this->validates($options)) {

おそらくsaveメソッドの第2引数（$validate = true）が無指定なので、$options['validate']がtrueになって、validatesメソッドが呼ばれているのだと思います。

試しに最初に提示したプログラムのsaveメソッドの第2引数にfalseを指定したら、$options['validate']もfalseになってvalidatesメソッドが呼ばれなくなりました。

4つ目のSQL

SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1

これは/cakephpのルート/lib/Cake/Model/Model.phpの1715行目で発行されています。

$success = (bool)$db->update($this, $fields, $values);

updateメソッドの本体は/cakephpのルート/lib/Cake/Model/Datasource/DboSource.phpにあります。

その中でdefaultConditionsメソッドをコールしています。

defaultConditionsメソッドには「$exists = $model->exists();」という処理があり、この部分でクエリを発行しています。

ちなみに、defaultConditionsメソッドの役割はWHERE条件句の一部を作成することです（たぶん）

5つ目のSQL

UPDATE `LAA0200849-cakephp`.`users` SET `id` = 1, `name` = '初音ミク', `point` = 1050, `created` = '2012-08-18 21:24:44', `modified` = '2012-08-18 21:24:44' WHERE `LAA0200849-cakephp`.`users`.`id` = '1'

長かった。

これが実行したかったupdate文です。

ただし、1つ目のSQLと同様に不要な項目が混入しています。

しかもcreatedやmodifiedをfindの取得結果で更新しています。

バグっていますね(汗

SQLの発行回数を減らしてみます！

以上の調査から、saveメソッドを使用して、かつ、SQLの発行回数を減らそうと思ったらModel.phpなどのコアライブラリを修正する必要があります。

しかし、そのようなことをダメプログラマの私はガクブル怖くてできないです。

というわけで実際には、最初に提示したメソッドを次のように修正しました。

public function addPoint($user_id, $point) {
    $ret = $this->query('UPDATE users SET point = point + ? WHERE id = ?', array($point, $user_id));
    if ($ret === false) {
        // エラー処理
    }
    if ($this->getAffectedRows() == 0) {
        // エラー処理
    }
}

修正のポイントは2つあります。

1つ目はqueryメソッドでupdate文を発行すること。

もともとsaveメソッドはSQLを知らない人でも簡単にDBアクセスできるようにするために導入されているので（違うかも、適当に言っちゃいましたｗ）、SQLが得意な人はわざわざ使用する必要はありません。

むしろ直接SQLを書いたほうが速いでしょう。

念のため戻り値のチェックもしています。

あとqueryメソッドはバインド変数が使用できるので積極的に利用したいところです。

2つ目はgetAffectedRowsメソッドを使用してSQLで影響を受けた行数をチェックすること。

今回は必ず更新処理が行われることを前提としているので更新が行われなかった（影響行数が0だった）場合、エラーにしています。

さてさて、修正版メソッドが発行するSQLを調べたら次のようになりました。

UPDATE users SET point = point + ? WHERE id = ? , params[ 50, 1 ]

1回のクエリですみ、SQLの発行回数を削減するという目的が達成できました^^

総括すると

saveメソッドによりデータベースの種類に依存せず、追加・更新が簡単にできるのはスゴイことですが、その利便性ゆえにパフォーマンスが犠牲となっているのは否めないです。

これらの関係はトレードオフでしょうね。

saveメソッドは非常に便利なので、わざわざqueryメソッドで全て書き換える必要はありませんが（最初のメソッドの例は酷すぎますが）、パフォーマンスを要求する部分はquery＋getAffectedRowsの組み合わせがおススメだと思います。

（パフォーマンス云々言うなら、そもそもフレームワークを使うなと言われそうですが、立場上そういう発言ができないのがツライところです）

2012-09-02

突然、ロリポップ！で動かしているEC-CUBEのメールが送信できなくなった

EC-CUBE

私は以前、本業とは別に知人に依頼を受けてEC-CUBEでサイト構築をしたことがあります。

（詳しくは、EC-CUBEのカート機能を既存のHTMLに組み込む（その1）にあります。）

2012年4月から運用を開始して、今まで大きな問題がなかったのですが、2012年8月下旬のある日から突然メールが送信できなくなったと連絡がありました。

具体的にどのような問題が発生したのか？

注文決済メール、会員登録メール、お問い合わせメール等々のメール送信が一切できなくなりました。

メール送信時のログを調査したところ、次のようなエラーメッセージが出力されていました。

2012/08/29 20:33:56 [/xxxxx/shopping/confirm.php] Failed to connect to 127.0.0.1:25 [SMTP: Failed to connect socket: 接続を拒否されました (code: -1, response: )] from IPアドレス

完全に拒否られています(汗

ロリポップ！の障害情報を見る限り、メール送信できなかった日には障害が発生していませんでした。

なお、EC-CUBEのバージョンは2.11.5で、メールの設定はインストール時のまま（定数MAIL_BACKENDの値は「smtp」）で特に変更していません。

最初にとった対応は？

原因は全く不明。

しかし、ネットでそれらしき対応策を見つけることができました。

EC-CUBE メール送信されない時の対策 | みつばらぼ

半信半疑で試してみたところ、、、

なんとかメール送信することができるようになりました。

実はダメだった。。。

上記の対応をとった翌日、再度、サイト運営者の方から連絡がありました。

それによると新たに問題が発生しているとのことでした。

会員登録メールのみが送信できない

ゴメンナサイ的な感じで、急いで再調査を行いました。

会員登録メールを送信した際のログには、次のようなエラーメッセージが出力されていました。

2012/08/30 19:47:46 [/XXXXX/entry/index.php] sendmail returned error code 127 from IPアドレス

う～ん(汗

ググってもログに関する有益な情報が出てこない。。。

次にとった対応は？

定数MAIL_BACKENDの値を「mail」に変更しました。

この修正により、会員登録メールは送信できるようになりました。

しかし、メールのReturn-PathにはFromと異なるメールアドレス（XXXXX@users038.phy.lolipop.jp）が設定されていました。

このままではエラーメールが返ってきませんし、スパムメール扱いされる可能性も高くなります。

どうやら真剣にソースを見ないといけないようです（最初からそうしろよ）

真の解決策は？

SC_SendMail.phpのgetBackendParamsメソッドを修正します。

/data/class/SC_SendMail.php

/**
 * メーラーバックエンドに応じたパラメーターを返す.
 *
 * @param string $backend Pear::Mail のバックエンド
 * @return array メーラーバックエンドに応じたパラメーターの配列
 */
function getBackendParams($backend) {
    switch ($backend) {
    case 'mail':
        $arrParams = array('-f[Fromメールアドレス]'); // ここを修正します。
        break;
    case 'sendmail':
        $arrParams = array('sendmail_path' => '/usr/bin/sendmail',
                           'sendmail_args' => '-i'
                           );
        break;
    case 'smtp':
    default:
        $arrParams = array(
                           'host' => $this->host,
                           'port' => $this->port
                           );
        break;
    }
    return $arrParams;
}

switch文のcase 'mail'に合致した時に、$arrParamsに-fオプション＋Fromメールアドレスを配列で渡すようにします（-fとFromメールアドレスの間にスペースは必要ありません、括弧[ ]も不要です）

私は早くアニメが見たかったので分かりやすさを考慮してSC_SendMail.phpを直接修正してしまいましたが、本来は/data/class_extends/SC_SendMail_Ex.phpでgetBackendParamsメソッドをオーバーライドしてやるべきです。

あと、定数MAIL_BACKENDの値を「mail」に設定するのをお忘れなく。

この修正によりReturn-PathにFromメールアドレスが設定されるようになります。

どうしてReturn-PathにFromメールアドレスが設定されるようになったのか？

EC-CUBEのメール送信処理はPEAR::Mailを使用しています。

getBackendParamsメソッドはPEAR::Mailのfactoryメソッド（インスタンス生成）に渡すパラメータを定義します。

PEARマニュアル: - Mail::factory()

マニュアルとgetBackendParamsメソッドを見比べて頂いたら、getBackendParamsメソッドの役割がわかると思います。

今は、定数MAIL_BACKENDが「mail」なので、パラメータ「array('-f[Fromメールアドレス]')」がfactoryメソッドの第2引数に渡され、その時の挙動はマニュアルによると次のように定義されています。

セーフモードが無効の場合、 $params は PHP mail() 関数の 5 番引数として渡されるでしょう。 $params が配列の場合、その要素はスペース区切りの文字列として連結されるでしょう。

つまり、mail関数の第5引数に「array('-f[Fromメールアドレス]')」が渡されます。

これにより何が起きるかというと、その答えは次のブログに書かれています。

[PHP-Tips:01] mail: Return-Pathの設定：ブログではありません。 - スタ☆ブロ -

はい、Return-PathにFromメールアドレスが設定されます^^

まとめると

SC_SendMail.php（あるいはSC_SendMail_Ex.php）のgetBackendParamsメソッドを修正する。
定数MAIL_BACKENDの値を「mail」に変更する。

以上２点の修正により正常なメール送信が可能になりました。

とりあえず同様の事例で困っている人がいるかもしれないので記事にしました。

対応方法は明確なのですが、原因自体ははっきりせず非常にモヤモヤした気持ちです(´・д・`)

もし、原因が判明するようなことがあれば、この記事に追記します。

ダメプログラマの技術メモ

プログラミングの技術メモや駄文など

CakePHP 2.x系のbindModel＋recursive=2はちょっとヤバイ？

まずはサクっと今回使用するテーブルについて

usersテーブル

ownsテーブル

musicsテーブル

ミクちゃんの所持する曲を取得するプログラム（bindModelバージョン）

bindModel＋recursive=2の場合に発行されるSQLは？

ミクちゃんの所持する曲を取得するプログラム（joinsバージョン）

joinsを使用した場合に発行されるSQLは？

CakePHP 2.x系の更新時のSQL発行回数を減らしたい

saveメソッドのSQL発行回数は多い？

usersテーブル

どこでSQLが発行されているのか？

1つ目のSQL

2つ目のSQL

3つ目のSQL

4つ目のSQL

5つ目のSQL

SQLの発行回数を減らしてみます！

総括すると

突然、ロリポップ！で動かしているEC-CUBEのメールが送信できなくなった

具体的にどのような問題が発生したのか？

最初にとった対応は？

実はダメだった。。。

次にとった対応は？

真の解決策は？

/data/class/SC_SendMail.php

どうしてReturn-PathにFromメールアドレスが設定されるようになったのか？

まとめると