カテゴリー別アーカイブ: LAMP[Linux, Apache, MySQL, PHP]

このWordPressを動かしていてページ送り(古い記事へ・新しい記事へ)が動作しない問題が発生した。
MySQLで全SQLログを取得してみると、SELECT文の前に必ず’EXPLAIN 実行予定SELECT文’が実行されていた。
WordPressでは、ページ数を取得するために、MySQLのFOUND_ROWS()を実行して取得している。　これは

SELECT SQL_CALC_FOUND_ROWS * FROM stock LIMIT 0,10;
SELECT FOUND_ROWS();

このように実行すると、SQL_CALC_FOUND_ROWS キーワードをつけたSELECT文のLIMITをつけなかった場合の件数（SELECT count(*) FROM stockの結果に同じ）を得られるのだが、このFOUND_ROWS()は直近の文を読むので、今回の様にEXPLAINが挿入されると↓のようになってしまうので、FOUND_ROWSの戻り値が0になってしまう。

SELECT SQL_CALC_FOUND_ROWS * FROM stock LIMIT 0,10;
EXPLAIN SELECT FOUND_ROWS();
SELECT FOUND_ROWS();

で、このEXPLAINがどこから来ているのか。　WordPressの全ソース検索をしても出てこない。
色々調べて、PHPに行き着いた。
PHP5.3.5ソース
php_mysql.c:1431-1432

int newql = spprintf (&newquery, 0, “EXPLAIN %s”, query);
mysql_real_query(mysql->conn, newquery, newql);

これで実行されていた。　これが実行される条件は、
php_mysql.c:1426

if (MySG(trace_mode))

の条件が指定されていて、このtrace_modeは
php_mysql_structs.h:125

long trace_mode;

で定義されていて、php.iniの

mysql.trace_mode

で設定することが出来て、On(=1)の場合にEXPLAINが自動実行されて、Off(=0)なら実行されないため、WordPressを実行するサーバではmysql.trace_mode=Offとする(php.iniでグローバル設定する以外に、wpの設置ディレクトリに.htaccessを配置してphp_flag mysql.trace_mode Offの記述を入れてもOK)か、WordPressのquery.phpでカウントするSQLをcount()を使う様に修正しなければいけない。

(426)

テーブルstockでレコードが削除されるとき自動でバックアップテーブルに挿入するトリガ

DELIMITER ..
CREATE TRIGGER stock_auto_backup
BEFORE DELETE ON stock FOR EACH ROW
BEGIN
INSERT INTO stock_backup(code, name, num, deleted_on) VALUES (OLD.code, OLD.name, OLD.num, unix_timestamp());
END..
DELIMITER ;

MySQLのunix_timestamp()関数により削除時間(now)を追加して記録することが出来る。
全てのUPDATE操作をログしたいとき、DELETEをUPDATEに書き換えれば、トリガでログしておけるので、操作側の修正の手間が省ける。

更に、一定以上古いバックアップを削除する機能もつける場合、END..の前に

DELIMITER ..
CREATE TRIGGER stock_auto_backup
BEFORE DELETE ON stock FOR EACH ROW
BEGIN
INSERT INTO stock_backup(code, name, num, deleted_on) VALUES (OLD.code, OLD.name, OLD.num, unix_timestamp());
DELETE FROM stock_backup WHERE unix_timestamp(deleted_on)<(unix_timestamp()-60*60*24*7); END.. DELIMITER ;

の様にすれば、トリガ実行タイミングで7日より前のレコードをバックアップテーブルから自動削除できる。

なお、MySQLでは同じテーブルの同じ条件に複数のトリガを設定できないので、同じ条件に機能追加する場合は

DROP TRIGGER stock_auto_backup

のようにしてトリガを一度削除する。

とあるテーブルのAUTO_INCREMENT値を変更する。

ALTER TABLE stock AUTO_INCREMENT = 1;

この場合はstockテーブルを1にしている。

(144)

　開発用にMySQLの全ログを取りたいのでmy.cnfに

log-slow-queries = /var/log/mysql-all-sql.log
long_query_time = 0

を追記して

#touch /var/log/mysql-all-sql.log
#chown mysql:mysql /var/log/mysql-all-sql.log

として、0秒以上かかるクエリをスロークエリログに入れる(＝全て記録する)設定をしたらMySQL起動エラーが出た。　古いMySQLでは、ロングクエリタイムは1秒以上を設定しなければならず、0秒指定は5.1系にしないとダメらしい。
　早速、yumでMySQLを更新しようとするが、デフォルトのリポジトリだとMySQLは5.0.77が最新。　ソースを取ってきてビルドするのもありだが、今回は手軽にyumから入れたいので、リポジトリの追加で対応する。
　新しいバージョンが登録されているリポジトリとしては、epelやremiがメジャーだ。

#wget http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm
#wget http://rpms.famillecollet.com/enterprise/5/remi/i386/remi-release-5-8.el5.remi.noarch.rpm
#rpm -Uvh remi-release-5-8.el5.remi.noarch.rpm epel-release-5-4.noarch.rpm

※release-x-yの部分はバージョンなので、新しいのが出ると変わるので、エラーが出るときはブラウザでディレクトリを覗いてファイル名を確認する。
　これで各リポジトリの情報が登録される。
　各リポジトリの情報が追加されただけで、yumコマンドを打っただけでは有効にはならないので

yum –enablerepo=epel,remi list

等の様に、–enablerepoで使用するリポジトリを指定する（標準で有効にすることも出来るが、自動アップデートで更新されると都合の悪いときもあるので、コマンドで指定した方が安心）

yum –enablerepo=remi install MySQL

　
　で導入すると5.1.55に更新出来るが、依存関係でPHPも更新しなければならないようだ。　PHPを更新しても特に不都合はないので、まとめて更新した。
MySQLをバージョンアップしたので、おまじない

#mysql_upgrade –password=パスワード

を実行して、データベースをアップグレードしておく。
　PHPの更新で、date系関数でタイムゾーンを設定していないとWarnが出るようになっているので/etc/php.iniの修正が必要になる。

date.timezone=’Tokyo/Asia’

　設定を済ませてapache/mysqlを再起動

#/etc/init.d/httpd restart
#/etc/init.d/mysqld restart

　今回の更新ではセッション関連のエラーが出た。
初期設定でroot:apacheだったphpの実行がdaemon:daemonになっていたので、session保管場所のパーミッション不足でエラーが出ていた。
実行権限をrootに戻しても良いが、daemon:daemonで実行した方がセキュリティ的によさげなので

chown daemon:daemon /var/lib/php/session
rm /var/lib/php/session/*

して、対応した。
　なお、phpMyAdminを使っている場合、2.11系になっていると思うが、PHP5.3では推奨されない機能を使用しているためDeprecatedメッセージが出るので、こちらも対応のバージョンに更新する(remiリポジトリに3.3系が入っている；　php.ini設定でDeprecatedメッセージを単純に消すことも出来るが、新しいバージョンを使用しない理由もないので更新した)
phpMyAdminを3系にするとblowfishの設定を求められるので、/usr/share/phpMyAdmin/config.inc.phpで

$cfg[‘blowfish_secret’] = ‘適当な文字列’;

を設定する。
　なお、現在のMySQLではlog-slow-queriesが非推奨になっている。
現在は

slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log

の様に、スロークエリログの有効をslow_query_logに1を与え(0なら無効)、slow_query_log_fileでスロークエリログファイルを指定する。

(325)

MySQL5シリーズからサポートされているストアド（ストアドプロシージャ）＆トリガ。

ストアドはSQL文をDBに保管(ストア)しておいて呼び出す機能で、解析などが完了した状態で保存されるので、複雑な構文のクエリで応答が高速になったり、簡単な分岐などを用いてまとまった処理を行ったりすることが出来る便利機能(CREATE PROCEDURE)
トリガはINSERTやUPDATE、DELETEを引き金（トリガ）にして処理を行うようにする機能（CREATE TRIGGER）

トリガの使いどころは、在庫表と発注表を用意しておいて、在庫表がUPDATEされてアイテムの数量が0になったら発注表に発注を追加すると言った自動処理や、NEWエイリアスを組み合わせることでINSERTされたレコードに定型処理を加えるというような場合だろう。
また、MySQLのトリガ処理ではタイミングにBEFOREとAFTERを指定することが出来て、BEFOREの場合には実行されるSQL文に先行して実行されるので、条件に満たない値が与えられた場合に引き金となったSQL文の処理を止めたりすることができる。

例：Yahooショップのリンクリストで、#ItemInfoアンカーがつくのを自動で取り除くトリガ

DELIMITER ..
CREATE TRIGGER remove_ii
BEFORE INSERT ON yahoo_link FOR EACH ROW
BEGIN
SET NEW.address = REPLACE(NEW.address, ‘%23ItemInfo’, ”);
END..
DELIMITER ;

yahoo_linkテーブルにINSERT文が発行された場合、address中の#ItemInfoを消している。　NEWは追加される行、あるいは更新後の行を指すエイリアスで、UPDATE文なら更新前の行を指すOLDエイリアスもある。

プログラム側に依存せず、DBシステム上の動作も高速になるため、ストアドやトリガを上手く使うのがDB連携システムのキモ。

(562)