CakePHP2.x系でRESTful API(JSON)のSQLデバッグを簡単に行う方法
この記事で行うことは、
http://127.0.0.1/cakephp/Test/index
のようにアクセスすればjsonのレスポンスが返り、
http://127.0.0.1/cakephp/Test/index?debug
のようにアクセスすれば通常のweb画面が表示される（SQLログの確認ができる）方法の解説です。
ソースファイル
Controller/AppController.php
<?php class AppController extends Controller { public $uses = array('Music'); public function beforeFilter() { // $data = $this->Music->findById(1); // $this->renderJson($data); } public function invokeAction(CakeRequest $request) { parent::invokeAction($request); // $data = $this->Music->findById(1); // $this->renderJson($data); } public function renderJson($data) { if (isset($this->request->query['debug'])) { $this->set('data', json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); $this->render('/debug/index'); } else { $this->response->type('application/json'); $this->response->body(json_encode($data, JSON_UNESCAPED_UNICODE)); } $this->response->send(); exit; } }
renderJsonメソッドの中でhtmlを表示する処理とjsonを返す処理を行っています。
どちらの処理を行うかはdebugパラメータの有無で判断しています。
Controller/TestController.php
<?php class TestController extends AppController { public function index() { $data = $this->Music->find('all'); $this->renderJson($data); } }
renderJsonメソッドの使用例です。
最後に
実は最初、RequestHandlerComponentを使用してjsonとhtmlを切り替えていたのですが、beforeRenderで複雑な処理を記述したら何故かhtmlでアクセスした時だけbeforeRenderが何度も呼び出される現象が発生してしまい、その解決方法がわからないため今回のような方法をとりました。
まぁ、今回の方がわかりやすいので結果オーライかな(;´･ω･)
EC-CUBEのデータ(MySQL)の自動バックアップとリストアの方法
2013/08/28頃からロリポップでWordPressサイトの改ざん被害が多数報告されるようになりました。
設定ファイルの情報が抜き出されて、データベースの内容が書き換えられてしまったのが原因のようです。
現在運営しているEC-CUBEでも同様の現象が発生する可能性があり、また不測の事態が発生しても迅速に業務を再開させることができるように、データベースをバックアップ/リストアすることにしました。
というか今までは面倒だからバックアップをとっていなかった（；＞＜）
被害に遭われた方には申し訳ないですが、今回の問題はリスク管理を高めるよいきっかけになりました。
EC-CUBEのデータの自動バックアップとリストアを行うのに必要な作業は次のとおりです。
順を追って説明します。
なお、ロリポップ(チカッパプラン)のsshの使用を前提に話を進めるので、参考にされる方は環境の差異にご注意下さい。
mysqldumpを使用してバックアップを行うシェルスクリプトを作成
ディレクトリの作成
サーバにログインしてから、binディレクトリとbackupディレクトリを作成します。
% mkdir ~/web/bin % mkdir ~/backup
binディレクトリの中にバックアップを行うシェルスクリプト、backupディレクトリの中にバックアップファイルが入ります。
本当はbinディレクトリもwebディレクトリと同階層に作成したかったのですが、ロリポップではcron実行するシェルスクリプトはwebディレクトリ以下に作成しないといけないため、仕方なくwebディレクトリの下に作成します。
.htaccessの作成
binディレクトリの中に.htaccessを作成します。
% cd ~/web/bin % echo deny from all > .htaccess % chmod 604 .htaccess
シェルスクリプト内にDB接続情報を記述するので、.htaccessでhttpによるアクセスを全て拒否する(deny from all)ことにします。
シェルスクリプトの作成
% vi db_backup.sh #下の内容でdb_backup.shを作成します。 #!/bin/sh DAYS=7 BACKUP_PATH=/home/xxxxx/xxxxx/xxxxx/backup BACKUP_FILE=`date +%Y%m%d`.sql.gz OLD_BACKUP_FILE=`date -d "-$DAYS days" +%Y%m%d`.sql.gz DB_USER=db_user DB_PASS=db_pass DB_SERVER=db_server DB_NAME=db_name cd $BACKUP_PATH mysqldump -u $DB_USER -p$DB_PASS -h $DB_SERVER $DB_NAME | gzip > $BACKUP_FILE chmod 600 $BACKUP_FILE rm -f $OLD_BACKUP_FILE
シェルスクリプト作成後は他人が実行できないようにパーミッションを700に変更します。
% chmod 700 db_backup.sh
ロリポップのcronはログインユーザの権限で実行するため、自身に実行権限があれば問題ありません。
シェルスクリプトの内容について
DAYS=7
バックアップファイルを残しておく日数を設定します。
DAYS=7の場合は6日前までのバックアップファイルが保存されます。
BACKUP_PATH=/home/users/xxxxx/xxxxx/backup
バックアップファイルを保存するディレクトリをフルパスで設定します。
BACKUP_FILE=`date +%Y%m%d`.sql.gz OLD_BACKUP_FILE=`date -d "-$DAYS days" +%Y%m%d`.sql.gz
バックアップファイル名を設定します。
20130901.sql.gzのような日付が入ったファイル名になります。
BACKUP_FILEは本日付けで新たに作成されるバックアップファイル、OLD_BACKUP_FILEは削除されるバックアップファイルになります。
DB_USER=db_user DB_PASS=db_pass DB_SERVER=db_server DB_NAME=db_name
MySQLのユーザ名、パスワード、ホスト名(IPアドレス)、データベース名を設定します。
EC-CUBEのdata/config/config.phpの中に次のような記述があるので、それをそのまま設定すればOKです。
define ('DB_USER', 'db_user');
define ('DB_PASSWORD', 'db_pass');
define ('DB_SERVER', 'db_server');
define ('DB_NAME', 'db_name');
cd $BACKUP_PATH mysqldump -u $DB_USER -p$DB_PASS -h $DB_SERVER $DB_NAME | gzip > $BACKUP_FILE
バックアップディレクトリの中に移動して、mysqldumpでデータベースの内容をgzip形式でファイル出力します。
chmod 600 $BACKUP_FILE
バックアップファイルのパーミッションを自分しか参照・変更できないように600に変更します。
rm -f $OLD_BACKUP_FILE
古いバックアップファイルを削除します。
本日が9/15、DAYS=7の場合は20130908.sql.gzというファイルが削除されます。
定期実行するようにcronにシェルスクリプトを登録
ロリポップではcrontabがない代わりにcronの設定を行う専用の管理画面が用意されています。
ロリポップのユーザ専用ページ → WEBツール → cron設定からcronの設定を行います。
上記は1日1回午前4時にシェルスクリプトを起動する例です。
ユーザアクセスの少ない早朝などに起動するのがベターだと思います。
問題が発生した場合はバックアップファイルをもとにリストア
不測の事態によりデータベースに何らかの障害が発生した場合は、バックアップファイルをもとにデータベースの復旧を行います。
サーバにログインしてから、次のコマンドを実行します。
% cd ~/backup % zcat [バックアップファイル] | mysql -u [ユーザ名] -p[パスワード] -h [ホスト名] [データベース名]
[バックアップファイル]はバックアップディレクトリ内にある巻き戻したい日付のファイルを指定します。
[ユーザ名]、[パスワード]、[ホスト名]、[データベース名]はシェルスクリプト内に記載してあるものを指定します。
ロリポップにはphpMyAdminが標準搭載されているので、そちらのインポート機能を利用しても構いません。
その場合はバックアップファイルを解凍することなく、gzip形式のまま取り込むことが可能です。
MySQLクライアントソフト「HeidiSQL」が軽快・多機能で便利すぎる
MySQLのクライアントソフトといえばCSE、黒猫 SQL Studio、MySQL Workbench、phpMyAdminなど数多くありますが、今回はその中でも私がイチオシのHeidiSQLをご紹介します。
HeidiSQLとは
HotmailにEC-CUBEの注文完了メールを送ることができない
久しぶりに更新です。
先月(2013/07/28)のことなのですが、突然Hotmail宛にEC-CUBEの注文完了メールを送ることができないという現象が発生しました。
不思議なことにGmail・Yahoo!メールなどのWebメールは問題ありません。
Hotmailだけメールを送ることができず、しかもメールを送ると次のようなエラーメールが返信されてきました。
Hotmailから返信されるエラーメールの内容
This is the mail system at host users038.phy.lolipop.jp.
I'm sorry to have to inform you that your message could not
be delivered to one or more recipients. It's attached below.
For further assistance, please send mail to postmaster.
If you do so, please include this problem report. You can
delete your own text from the attached returned message.
The mail system
<xxxxxxxxxx@live.jp>: host mx4.hotmail.com[65.55.37.88] said: 550 SC-001
(COL0-MC2-F29) Unfortunately, messages from 210.172.144.88 weren't sent.
Please contact your Internet service provider since part of their network
is on our block list. You can also refer your provider to
http://mail.live.com/mail/troubleshooting.aspx#errors. (in reply to MAIL
FROM command)
※ ロリポップのメールサーバを利用しています。
原因調査(汗
とりあえずメールに書いてあるとおりトラブルシューティングを参照してみると、、
SMPTエラーコード(550 SC-001)
ポリシーを理由にメールが Outlook によって拒否されました。拒否された理由は、メールの内容が迷惑メールの特徴に似ていることか、IP/ドメインの評価に関連している可能性があります。メールまたはネットワーク管理者のどちらでもない場合は、メールまたはインターネット サービス プロバイダーに連絡して支援を依頼してください。
えっ？
(;´Д｀)ﾊｧﾊｧ ← 何故か興奮している
理由は全くわかりませんがスパム認定されたために拒否されているようです。
一日に多くても10件ほどの仕事関係のメールしか送っていないのに、それをスパム扱いされるとは。。
誰かが迷惑メールを送りまくって巻き添えを食らったのかな？
う～ん、心当たりが全然ない(´･д･｀)
解決策は？
次のURLにアクセスしてフォームに必要事項を入力→送信すればカスタマーサポートが対応してくれます（私の場合は1日もかかりませんでした）
https://support.msn.com/eform.aspx?productKey=edfsmsbl2&ct=eformts
フォームの「送信 IP または範囲」には、Hotmailから返信されたエラーメール内の「Unfortunately, messages from XXX.XXX.XXX.XXX weren't sent.」のIPアドレス(XXX.XXX.XXX.XXX)を記入します。
カスタマーサポートに問い合わせた後
対応完了後、カスタマーサポートから「配送問題の登録」というメールが届きます。
Hotmail Domain サポートへお問い合わせいただき、
誠にありがとうございます。
カスタマーサポートのXXXXXX と申します。
この度は、Hotmail への配信問題におきまして、
調査に時間がかかりましたことをお詫び申し上げます。
弊社担当部署より、調査報告が戻りましたので、ご連絡申し上げます。
お知らせいただきました IP アドレスを調査いたしましたところ、
御社からのメール送信に対する Hotmail アドレスでの受信規制が、
Hotmail スパムフィルタ (*1) で発生していることが判明いたしました。
なお、今回のメール配信上の問題につきましては、
緩和処置を実施させていただきましたことをご報告申し上げます。
お客様のシステムへ完全に反映されますまで、
24 - 48 時間程度要しますことを予めご了承くださいませ。
少々長文のご案内となり誠に恐れ入りますが、
以下ご確認いただけますと幸いに存じます。
(以下省略)
急に規制されたのは腹が立ちましたが、対応は迅速・丁寧で好印象でした。
1か月経ちましたが問題は再発していません。
CakePHP2.x系でバルクインサートを使用して高速なインサート処理を実現する
CakePHPで複数のレコードをDBに投入する時に、forループでsaveメソッドを何度も呼び出していませんか？
今のプロジェクトでもそういったソースをよく見かけるのですが、とてつもなく遅い(;´Д｀)
ということで、今回はCakePHPでバルクインサートを使用してインサート処理を高速に行う方法を説明します。
今回使用するテーブルは？
musicsテーブル
| id | singer | song_title | created | modified |
|---|---|---|---|---|
| 1 | 初音ミク | Tell Your World | 2012-09-29 20:18:57 | 2012-09-29 20:18:57 |
| 2 | 鏡音リン | 炉心融解 | 2012-09-29 20:18:57 | 2012-09-29 20:18:57 |
| 3 | 鏡音レン | Fire◎Flower | 2012-09-29 20:18:57 | 2012-09-29 20:18:57 |
| 4 | 巡音ルカ | Just Be Friends | 2012-09-29 20:18:57 | 2012-09-29 20:18:57 |
前2コの記事でも同じようなテーブルを使用しており、特に言及していませんでしたが、idはAUTO_INCREMENT、modifiedはON UPDATE CURRENT_TIMESTAMPを指定しています（MySQL独自の属性です）
ちなみに、CREATE文は次のとおりです。
CREATE TABLE IF NOT EXISTS `musics` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
`singer` VARCHAR(256) NOT NULL COMMENT '歌手',
`song_title` VARCHAR(256) NOT NULL COMMENT '曲名',
`created` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '作成日時',
`modified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;次回以降は特に断りがない限り、同じようなテーブル構成であれば今回のような属性が付加されているとします。
まずはsaveAllメソッドを使ってみます
CakePHPを使用していれば、バルクインサート ⇒ 一括挿入 ⇒ Model::saveAll（あるいはModel::saveMany）が思い浮かぶと思います。
そこでsaveAllメソッドを使用して、複数のレコードをテーブルに投入するサンプルプログラムを書いてみました。
public function insertMultiMusic($data) {
$ret = $this->saveAll($data['Music'], array('atomic' => false));
if (in_array(false, $ret, true)) {
// エラー処理
}
}このメソッドはMusic.phpにあるとします。
insertMultiMusicメソッドの引数には、次のような連想配列を指定します。
$data = array(
'Music' => array(
array(
'singer' => '初音ミク',
'song_title' => 'Tell Your World',
),
array(
'singer' => '鏡音リン',
'song_title' => '炉心融解',
),
array(
'singer' => '鏡音レン',
'song_title' => 'Fire◎Flower',
),
array(
'singer' => '巡音ルカ',
'song_title' => 'Just Be Friends',
),
),
);saveAllメソッドは直感的で使いやすいメソッドですが、注意すべき点が2つあります。
1つ目は第2引数の指定です。
もし第2引数に何も指定しなかった場合は、saveAllメソッドの内部でトランザクション処理が行われて勝手にコミットしてしまいます。
それを避けるためにサンプルプログラムでは、「array('atomic' => false)」を指定しています。
この指定によって、トランザクション処理が行われなくなります。
恐らく、自前でトランザクション制御をすることの方が多いと思うので、常に「array('atomic' => false)」を指定する癖を付けておいた方がよいかもしれません。
2つ目は戻り値です。
もし全てのレコードのINSERTが成功した場合は、投入レコード数分の要素を持った次のような配列が返却されます。
array(true, true, true, true);
この流れでいくと、3レコード目だけINSERTが失敗した場合は、次のような配列が返りそうですが、
array(true, true, false, true);
にはならず、PDOExceptionがスローされます（not null項目にnullを設定した場合）
う～ん(汗
saveAllメソッドのロジックを見る限りでは、INSERTに失敗したレコード（戻り値の配列要素）にはfalseが設定されそうなのですが。。。
かなり粘ったのですが、falseを返すデータを作ることができませんでした(´・ω・｀)ｺﾞﾒﾝﾈ
まぁ、エラー処理に関しては戻り値の配列要素のfalseチェックと、呼び出し元でPDOExceptionをキャッチして、ロールバックするロジックを入れるようにすれば十分だと思います。
saveAllメソッドが発行するSQLは？
INSERT INTO `LAA0200849-cakephp`.`musics` (`singer`, `song_title`, `modified`, `created`) VALUES ('初音ミク', 'Tell Your World', '2012-09-29 20:18:57', '2012-09-29 20:18:57')
INSERT INTO `LAA0200849-cakephp`.`musics` (`singer`, `song_title`, `modified`, `created`) VALUES ('鏡音リン', '炉心融解', '2012-09-29 20:18:57', '2012-09-29 20:18:57')
INSERT INTO `LAA0200849-cakephp`.`musics` (`singer`, `song_title`, `modified`, `created`) VALUES ('鏡音レン', 'Fire◎Flower', '2012-09-29 20:18:57', '2012-09-29 20:18:57')
INSERT INTO `LAA0200849-cakephp`.`musics` (`singer`, `song_title`, `modified`, `created`) VALUES ('巡音ルカ', 'Just Be Friends', '2012-09-29 20:18:57', '2012-09-29 20:18:57')・・・
(;ﾟ∀ﾟ)=3ﾊｧﾊｧ
投入レコード数分だけINSERT文を発行しています。
これではバルクインサートになっていないですねｗ
次にqueryメソッドを使ってみた
調べてみたらCakePHPではバルクインサート用のメソッドが用意されておらず、自前でバルクインサートのSQLを書かなければならないようです。
仕方がないので、サクっと書いてみました。
public function insertMultiMusic($data) {
$fields = array('singer', 'song_title', 'created');
$holder = '(' . implode(',', array_fill(0, count($fields), '?')) . ')';
$holders = implode(',', array_fill(0, count($data), $holder));
$params = array();
foreach ($data as $val) {
foreach ($fields as $field) {
if ($field == 'created') {
$params[] = date('Y-m-d H:i:s');
} else {
$params[] = $val[$field];
}
}
}
$fields = implode(',', $fields);
$sql = "INSERT INTO musics ({$fields}) VALUES {$holders}";
$ret = $this->query($sql, $params);
if ($ret === false) {
// エラー処理
}
if ($this->getAffectedRows() != count($data)) {
// エラー処理
}
}処理内容はsaveAllメソッドを使用したサンプルプログラムと同じです。
insertMultiMusicメソッドの引数は、saveAllメソッドの第1引数と同じにしています。
微妙に汎用的な作りにしました(汗
$fieldsとSQL文のテーブル名をパラメータ化すれば、多分どのモデルでも使用できるようになります。
DboSource::insertMultiに近い感じかなぁ。
実は、このプログラムもおかしなデータをqueryメソッドに渡すとPDOExceptionが発生します。
なので、状況によってはPDOExceptionをキャッチした後にロールバックするロジックが必要になります。
あと、MySQLの使用を想定しているので、MySQL以外を使用する場合は、DBに応じてバルクインサートSQLを構築する部分を書き換えて下さい。
また、DB毎のバルクインサートの使用制限（SQL文の最大長、投入レコード数など）には注意を払って下さい。
場合によっては、一定件数毎に分けてバルクインサートする必要があるかもしれません。
queryメソッドが発行するSQLは？
INSERT INTO musics (singer,song_title,created) VALUES (?,?,?),(?,?,?),(?,?,?),(?,?,?) , params[ 初音ミク, Tell Your World, 2012-09-30 05:13:19, 鏡音リン, 炉心融解, 2012-09-30 05:13:19, 鏡音レン, Fire◎Flower, 2012-09-30 05:13:19, 巡音ルカ, Just Be Friends, 2012-09-30 05:13:19 ]
複数レコードの投入がバルクインサートの1クエリだけで済んでいますね^^
ベンチマークは如何ほど？
1万件のレコードを投入した場合のベンチマークを取ってみました。
| saveメソッド＋forループ | saveAllメソッド | queryメソッド | |
|---|---|---|---|
| 1回目 | 14.3828558922 | 15.3304040432 | 0.311769008636 |
| 2回目 | 14.4124500751 | 15.4717850685 | 0.304137945175 |
| 3回目 | 14.5008730888 | 15.3734378815 | 0.319990158081 |
【単位:sec】
queryメソッドを使用したバルクインサートが桁違いに速いことがわかります（その差は約50倍！）
CakePHPに限ったことではないですが、インサート処理においてパフォーマンスを求める場合はバルクインサートの使用をオススメします。
