フリーズしたmysqlのプロセスを停止する

mysqlにログインして、

mysql> show processlist;

これで今動いてるプロセスが確認できる。
そしてkillしたいプロセスを

mysql> kill 1234;

で強制終了できます。

http://spiri-tua-lism.com/?p=1132

mysqlだけでできる便利な処理いろいろ

mysqlで調べたことをいくつかメモ。

■insert ... select

これは前から知ってたけどもう一度おさらい。

insert into TABLE (
col1,
col2,
col3
) select
val1,
val2,
val3
from TABLE2


■on duplicate key update

行がなければinsertして、なければupdateしてくれる。すごい便利。

insert into TABLE (
col1,
col2,
col3
) select
val1,
val2,
val3
from TABLE2
on duplicate key update col2 = values(col2), col3 = col3 + values(col3)

※この場合はcol1が主キーである必要がある。


■update ... select

insert ... select のupdate版みたいな感じ。
※やってみたけどmysqlが固まる。すごく重いかも。

update TABLE A, (
select
col1,
col2,
col3
from TABLE2
) B set A.col4 = B.col1


■replace

これも前から知ってたけどおさらい。
新しいデータはinsert、古いデータはdeleteしてinsertされる。上書きみたいな感じ。

replace into TABLE (
col1
)
select col2 from TABLE2


mysql単体でもかなり色々なことができますね。

is nullは重い

テーブルAとテーブルBがあって、2つを結合し、AにデータがあってBにデータがないものを抽出するのに、B.hoge is null って感じでis nullを使ってました。

最初はこれで問題なかったものの、データ量が多くなるにつれてパフォーマンスが悪くなってきており、調べてみたらこの is null が原因でした。

is nullを使うと、強制的に全表走査になっちゃうそうです。
http://memo.sonorilo.net/post/187525676/sql-11

今回は仕様の都合上、is nullは外せませんでした。
代わりに絞り込みを少し強めにして、パフォーマンスを改善しました。
平たく言うとその場しのぎです。

実装後に変更するのは難しいので、設計段階でできるだけnullを使わないよう、心がけないといけませんね。

1回のinsertでまとめて複数行を挿入する

DBMSはmysqlです。

1回のinsertでまとめて複数行挿入できないかな、できるんじゃないかな、と思って検索したら、やっぱりできました。

insertで複数行追加する

INSERT INTO user_table (id, name, memo, status) VALUES
(1, 'test1', 'メモ', 1),
(2, 'test2', 'メモ', 1),
(3, 'test3', 'メモ', 1),
(4, 'test4', 'メモ', 1);

こんな感じ。けっこう便利です。

mysqlでランダムな10桁の文字列を作る

select substring(md5(rand()), 1 , 10)

こんな感じで。
もちろんユニークにはならないです。

mysqlでconcatの中でcaseを使う

こういう感じです。

select
concat(
case when 1 then 2 else 3 end,
case when 0 then 4 else 5 end,
case when 1 then 6 else 7 end,
case when 0 then 8 else 9 end
) as test


これで返り値は「2569」です。

あとconcat_wsというのも便利。

select
concat_ws(
',',
case when 1 then 2 else 3 end,
case when 0 then 4 else 5 end,
null,
case when 0 then 8 else 9 end
) as test

これで返り値は「2,5,9」になります。

mysqlのgroup_concat関数が便利

http://treeapps.hatenablog.com/entry/20120530/p1

これは便利です。覚えておこう。

自宅サーバー再構築メモ

自宅サーバーを新しく構築しなおしました。
これまでCentOS4＋ThinkPad R50eで運用してましたが、少し前に不穏なディスクエラーが出てたこともあったので、CentOS5＋let's note CF-Y4に変えました。

ThinkPad R50eはCDROMドライブなのでCDを3枚くらい焼いた記憶がありますが、CF-Y4はDVD読めるのでディスク1枚で済みました。


以下はOSインストール後のサーバー構築手順メモ。
備忘録として残しておきます。

■selinuxを無効化

# vi /etc/sysconfig/selinux
SELINUX=disabled
再起動

■yum

# yum list installed
インストールされているパッケージを調べる

■SSH

# vi /etc/ssh/sshd_config
Port 10022
PermitRootLogin no
PubkeyAuthentication yes
AuthorizedKeysFile .ssh/authorized_keys
(sshd_configじゃなくてssh_configを編集するミスをした。。)

■iptables

/etc/sysconfig/iptablesを旧サーバーからまるごとコピペ

■vsftpd

この辺を参考に。
http://pro-grammer.info/archives/902

■yumでphpとMySQLのインストール

この辺を参考に。
http://iwbc.info/archives/877

# yum install php
# yum install php-pear
# yum install php-devel
# yum install php-gd
# yum install php-mysql
# yum install php-mbstring
# yum install php-xml
# yum install mysql
# yum install mysql-server

■pear

# pear upgrade --force PEAR-1.5.4
# pear upgrade PEAR
# pear update-channels
pearが古い場合、最初に強制的に1.5.4にアップグレードする必要がある。

そしてもう一度upgradeして最新版に。
# pear upgrade PEAR

で、あとは必要なパッケージを入れていく。
http://kawama.jp/archives/2006/06/pear.html

■php

# vi /etc/php.ini
error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED
date.timezone =Asia/Tokyo

■mysql

http://kawama.jp/archives/2011/02/mysql5-5_character-set-server.html
データはdumpで移行する

■http

/etc/httpd/conf.d/vhost.confをコピペして移行

■https

こちらを参照
http://kawama.jp/archives/2011/07/07/centos5_mod_ssl.html

■cron

コピペで移行

■バックアップ

バックアップスクリプトやcronなど各種設定を移行。

■USBメモリの設定

この辺参考。
http://kawama.jp/archives/2005/12/linuxusb.html
http://kawama.jp/archives/2008/03/usb.html
http://kawama.jp/archives/2009/10/linuxusb_1.html

■モニタの電源消灯

http://kawama.jp/archives/2010/07/linux%E3%81%A7%E3%83%A2%E3%83%8B%E3%82%BF%E3%81%AE%E9%9B%BB%E6%BA%90%E3%82%92%E7%AE%A1%E7%90%86.html

■postfix

Postfixを入れ、mtaを変更
http://kawama.jp/archives/2007/10/mtasendmailpost.html

sendmail削除しておく
# yum remove sendmail

OP25対応
http://kawama.jp/archives/2007/03/postfixop25b.html

送信テスト
http://kawama.jp/archives/2011/05/linux_mail_oneliner.html

main.cfの編集
# vi /etc/postfix/main.cf
inet_interfaces = all
myhostname = home.nainai16.com
mynetworks = 192.168.1.0/24
mydestination = $myhostname

■メール転送

# vi /etc/aliases
rootとかをgmail宛に転送
# newaliases

DiCEの設定

http://www.hi-ho.ne.jp/yoshihiro_e/dice/linux.html

とりあえず以上。
移行漏れがあれば随時対応していきます。

Pear::DBからMDB2への移行で修正した箇所

Pear::DBからMDB2への移行をしたのですが、けっこう面倒でした。
覚えている範囲でメモしておきます。

■DBへの接続

変更前
$db = DB::connect(DSN);

変更後
$db = MDB2::connect(DSN);
$db->loadModule('Extended');


■FETCHMODE定数の変更

変更前
$rs->fetchRow(DB_FETCHMODE_ASSOC)

変更後
$rs->fetchRow(MDB2_FETCHMODE_ASSOC)


■limit句

変更前
$db->limitQuery($sql, 0, 10);

変更後
$db->setLimit(10, 0);
$db->query($sql);


■quote関数

変更前
$db->quoteSmart($v["value"])

変更後
$db->quote($v["value"])


■その他

※MDB2ではexecuteMultipleで連想配列が使えないので注意。DBから値を取得して使う場合などは、MDB2_FETCHMODE_ASSOCじゃなくMDB2_FETCHMODE_ORDEREDを使うと良い。

※quoteSmartではnullを自動的に空白文字列化してくれていたが、quoteではその機能がないので、nullが入る可能性のあるカラムは予めnull化にしておく。

※上記修正の該当箇所はこんなふうにfindで調べました。

find . -name \*.php -print xargs grep limitQuery

MySQL5.5で文字化け(character-set-serverで解消)

クライアントマシンのmysql(xamppでインストールしたやつ)が、最近非常に調子が悪く、まともにinsertやupdateもできないような状態になってしまったので、xamppを入れ直すことにしました。

xamppの設定をするのは何年ぶりでしょうか。2年以上は間が空いてると思います。

とりあえずapacheやphpは大きな問題もなく移行が完了。
mysqlも普通に動いてる、、、と思いきや、phpからinsertをしたデータが文字化け起こしてました。

というわけで、my.cnfにお約束の文字化け回避設定をします。

[mysqld]
default-character-set = utf8
skip-character-set-client-handshake

が、mysqlが起動しなくなってしまいました。なぜ。。。

ググってみたところ、バージョン5.5以降では[mysqld]セクションのdefault-character-setが廃止され、代わりにcharacter-set-serverが追加されたそうです。

[mysqld]
character-set-server = utf8
skip-character-set-client-handshake

これで無事文字化け解消できました。ひとまず安心。

mysql5.5ではこの他にもいろいろ変更や新機能があるっぽいです。
今はまだローカルのテスト環境ですが、サーバー環境で使うとなると色々問題出てきそうですね。

php,smarty,mysql,javascriptでゼロ埋め

※javascriptも追加しました。

ゼロ埋めとかゼロパディングと言われるものです。
よく忘れてしまうのでまとめておきます。

■php
echo sprintf("%05d", $number);

echo str_pad($number, 5, "0", STR_PAD_LEFT);

引数をひとつ以上渡すことも可能。
echo sprintf("%04d-%02d-%02d", $year, $month, $day);

■smarty
{$number|string_format:"%05d"}

■mysql
select lpad(number, 5, '0') from table;

select to_char(number, '00000') from table;

■javascript

var number = 5;
alert(("0"+number).slice(-2));
> 05
alert(("00"+number).slice(-3));
> 005




他にもいろいろやり方はありますが、とりあえずよく使うものはこんなところだと思います。

MySQLのカラム名の大文字・小文字

MySQLでカラム名を

regiDate

とか

startTime

みたいな感じにしてみたんですが、どうもこれはよくないみたいです。
Pear::MDB2でデータを引っ張り出して配列に格納したら、添字がregidateとstarttimeになってました。

http://dev.mysql.com/doc/refman/5.1/ja/identifier-case-sensitivity.html

カラム名、インデックス名、ストアされたルーチン名とカラムのエイリアスは、どのプラットフォームでも大文字と小文字が区別されません。トリガ名では大文字と小文字が区別されます。

長いことMySQLを使ってましたが、これは知らなかったです。
小文字＋アンダースコアの形式を使っておくのがいいみたいです。

MySQLで日付の差分を求める

日付型のカラム２つの差分を調べようと思い、

select date1 - date2 from table

とやったら、変な数字が返ってきてしまいました。

調べてみたら、こういうときはdatediffという関数を使えばいいようです。
こんな具合です。

SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');

http://dev.mysql.com/doc/refman/4.1/ja/date-and-time-functions.html

SQLチューニングのヒント

対象RDBMSはMySQL。
2000行くらいのテーブルと10000行くらいのテーブルを結合するSQLが、実行時間が10秒くらいかかっており、これを解消しようとしてました。
で、いろいろいじってみたところ
... from hoge h left outer join fuga f on h.id = f.id and h.yyyymm = 200912
とやっていたのを、
... from hoge h left outer join fuga f on h.id = f.id and h.yyyymm = '200912'
という具合にシングルクォートで囲ってみたところ、実行時間が0.3秒と、劇的に速くなりました。
カラムyyyymmはvarchar型です。他にもいろいろいじってましたが、どうもこのシングルクォートが遅さの決め手だったと思われます。
チューニングは地道な作業ですが、これまでの経験を振り返ってみるととにかく気になることは試してみると、活路が開けるような気がします。

MySQLで奇数と偶数の判別

where句でこうしてやればＯＫ
■偶数
where numberColumn % 2 = 0
■奇数
where numberColumn % 2 != 0
MySQLでしか試していないけど、他のRDBMSでも使えそうです。

MySQLでフィールド値の重複をチェック

group by ... havingを使う。
こんな感じ。
SELECT id,title FROM table GROUP BY id HAVING COUNT(id) > 1;

Adminerでデータインポートする際の注意

Adminerでもテキストファイルからデータをインポートする機能があるが、オプションの指定が一切ない。
（逆にpmaはオプションの指定がかなり細かい）
何度かトライ＆エラーを繰り返してインポート成功したので、わかったことをメモしておきます。
（DBの文字コードはUTF-8です）
・改行コードはLF
・カンマ区切り
・各項目は"(ダブルクオート)で囲む
こんな感じ↓
"id1","name1","1","2","3"
"id2","name2","4","5","6"
"id3","name4","7","8","9"

Adminerを使ってみた

http://phpspot.org/blog/archives/2009/08/phpmyadminadmin.html
この記事を読んで興味が湧いたので、ローカルで使ってみました。
DLは本家サイトからAdminer
結論から言うと、すごくいいです。phpMyAdmin(以下pma)からこっちに乗り換えてしまおうかと思うくらい。
具体的に良いと思った点は、
・軽い
・シンプル
・かといって機能も豊富。pmaにひけをとらない
・フレームを使ってない(pmaはフレーム)
・Viewの作成、編集とかもできる。まだ使ってないけど、Create procedure / Create function とかもあるのでpmaより多機能かもしれない
など。
しばらくはpmaと併用してみようと思います。

MySQL GUI Toolsを試す

普段使うのはもっぱらphpMyAdminなんですが、こちらの「MySQL GUI Tools」はMySQL社純正のツールで、しかもフリーということで試してみました。
http://dev.mysql.com/downloads/gui-tools/
MySQL Administrator
MySQL Query Browser
これら２つのアプリが主力で、前者がDBの接続、ユーザー、ログ、ステータスなどを管理する機能を、後者がSQLでのデータ入出力を管理する機能を持ち、これらが相互に連携して総合的なGUI管理が可能になっています。
まだちょっとしか触ってませんが、かなり機能が豊富です。ビューとかプロシージャの管理などもできるので、機能だけでいえばphpMyAdmin以上だと思います。開発者ならMySQL GUI Toolsのほうがいいかもしれません。
またいくつかのブログ記事等で日本語の扱いが弱いという点を指摘されていますが、今のところそういった問題には遭遇していません。
なかなか使えそうなので、今後も継続してチェックしてみようと思います。

関数の中でdistinctを使う

select count(distinct column_name) from table_name
こういう使い方もできるんですね。