MySQLでクロス集計っぽいこと

以前Accessを使っていた時に、TRANSFORM .. PIVOT ..を使ったクロス集計クエリが意外と便利で
（TRANSFORM .. PIVOT ..が便利というより、クロス集計ウィザードが便利だった）
MySQL単体でもアレやりたいなぁとか思ったり思わなかったり。

実際のところ、MySQLにTRANSFORM .. PIVOT .. に該当する構文はMySQLには無いので
かなり泥臭い感じのクエリにはなるけれども、
下手をしなければテーブルスキャン1回分 + テンポラリテーブル + ファイルソートのコストで処理できたりするので、
手でログテーブルの中身をざっと洗ってざっとした傾向を見るのくらいには意外と使える。

テストデータはこんな感じ。



mysql> SELECT * FROM t1 LIMIT 3;
+-----+-----------+---------------------+
| seq | user_id   | dateval             |
+-----+-----------+---------------------+
|   1 | user_0009 | 2012-12-29 12:26:43 |
|   2 | user_0008 | 2012-12-29 01:12:07 |
|   3 | user_0007 | 2012-12-29 03:16:24 |
+-----+-----------+---------------------+
3 rows in set (0.00 sec)

アクセスログからカラムを絞って取り込んだ様なイメージ。

これを、user_idを縦軸に、datevalのxx時台を横軸にして引っこ抜く。

mysql> SELECT

    ->  user_id,

    ->  SUM(HOUR(dateval) = 0) AS '0',

    ->  SUM(HOUR(dateval) = 1) AS '1',

    ->  SUM(HOUR(dateval) = 2) AS '2',

    ->  SUM(HOUR(dateval) = 3) AS '3',

    ->  SUM(HOUR(dateval) = 4) AS '4',

    ->  SUM(HOUR(dateval) = 5) AS '5',

    ->  SUM(HOUR(dateval) = 6) AS '6',

    ->  SUM(HOUR(dateval) = 7) AS '7',

    ->  SUM(HOUR(dateval) = 8) AS '8',

    ->  SUM(HOUR(dateval) = 9) AS '9',

    ->  SUM(HOUR(dateval) = 10) AS '10',

    ->  SUM(HOUR(dateval) = 11) AS '11',

    ->  SUM(HOUR(dateval) = 12) AS '12',

    ->  SUM(HOUR(dateval) = 13) AS '13',

    ->  SUM(HOUR(dateval) = 14) AS '14',

    ->  SUM(HOUR(dateval) = 15) AS '15',

    ->  SUM(HOUR(dateval) = 16) AS '16',

    ->  SUM(HOUR(dateval) = 17) AS '17',

    ->  SUM(HOUR(dateval) = 18) AS '18',

    ->  SUM(HOUR(dateval) = 19) AS '19',

    ->  SUM(HOUR(dateval) = 20) AS '20',

    ->  SUM(HOUR(dateval) = 21) AS '21',

    ->  SUM(HOUR(dateval) = 22) AS '22',

    ->  SUM(HOUR(dateval) = 23) AS '23'

    -> FROM t1

    -> GROUP BY user_id WITH ROLLUP;

+-----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+

| user_id   | 0    | 1    | 2    | 3    | 4    | 5    | 6    | 7    | 8    | 9    | 10   | 11   | 12   | 13   | 14   | 15   | 16   | 17   | 18   | 19   | 20   | 21   | 22   | 23   |

+-----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+

| user_0001 |   79 |   88 |   86 |   88 |   70 |   71 |   84 |   91 |   70 |   77 |   79 |   91 |   85 |   90 |   89 |   93 |   84 |   67 |   90 |   82 |   88 |   88 |   83 |   80 |

| user_0002 |   88 |   71 |   85 |   74 |   75 |   85 |   82 |   93 |   85 |   71 |   91 |  106 |   80 |  103 |   70 |   95 |   89 |   90 |   86 |   72 |  103 |   80 |   72 |   79 |

| user_0003 |   84 |   79 |   81 |   85 |   90 |   87 |   84 |  111 |   80 |   88 |   80 |   86 |   84 |   85 |   70 |   81 |   85 |   92 |   75 |   75 |   85 |   89 |   74 |   82 |

| user_0004 |   82 |   73 |   83 |   89 |   75 |   79 |   74 |   93 |   76 |   90 |   89 |   86 |   70 |   88 |   93 |   87 |   68 |   73 |   91 |   90 |   94 |   86 |   82 |   93 |

| user_0005 |   75 |   83 |   82 |   96 |   96 |   80 |   85 |   78 |  101 |   79 |   82 |  102 |   80 |   99 |   67 |   92 |   79 |   78 |   82 |   76 |   88 |   95 |   88 |   86 |

| user_0006 |   79 |   82 |   81 |   81 |   93 |   95 |   76 |   87 |   86 |   73 |   91 |   74 |   87 |   85 |   87 |   94 |   96 |   91 |   71 |   73 |   92 |   89 |   85 |   81 |

| user_0007 |   87 |   73 |   79 |   87 |   97 |   75 |   98 |   85 |   82 |   94 |   82 |   76 |   80 |   93 |   81 |   81 |  100 |   74 |   88 |   79 |   58 |   94 |   73 |   80 |

| user_0008 |   86 |   81 |   82 |   90 |   83 |   73 |   67 |   92 |   81 |   90 |   75 |   85 |   79 |   81 |   80 |   90 |   63 |   84 |   70 |   82 |   75 |   76 |   89 |   75 |

| user_0009 |  109 |   75 |   66 |   82 |   83 |   98 |   96 |   80 |   52 |   93 |   72 |   78 |   85 |   86 |   66 |   70 |   82 |  102 |  101 |   89 |   77 |   79 |   67 |   85 |

| user_0010 |   97 |   87 |   71 |   78 |   75 |   78 |   87 |   98 |   81 |   82 |   85 |   77 |   79 |   66 |   69 |   73 |   91 |   71 |   98 |   93 |   88 |   77 |   94 |   95 |

| NULL      |  866 |  792 |  796 |  850 |  837 |  821 |  833 |  908 |  794 |  837 |  826 |  861 |  809 |  876 |  772 |  856 |  837 |  822 |  852 |  811 |  848 |  853 |  807 |  836 |

+-----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+

11 rows in set (0.87 sec)

……泥臭い。。

変なサブクエリでなく、SELECT 真偽文 ..で0か1を返させてSUMするのがポイント（たぶん）

ホントは横軸を日付にして、7日以上連続アクセスしているユーザーの数を数える、とか考えていて、

`この結果セットを作るところまでがSQLで、連続アクセスになるかどうかの判定はPerlでラッピングして判定しよう'とか思っている。

計らずしも、年越しエントリになってしまった。。

今年もお世話になりました、来年もよろしくお願いします。

cactiが動かないので遠回りに色々トレースしてみた

俺の仕事用VMはこんな感じでメジャーバージョン違いのMySQLが複数入っている

（しかも、昨日から同時起動している）関係で、ポートやソケットファイルがデフォルトと違う状態で4つある。

色々あってそこにcactiを突っ込みたくなって、cactiのデータ格納用MySQLに5.5.29を任命。

portは64055, socketは/usr/mysql/5.5.29/data/mysql.sock。

さて、cacti/include/config.phpを書き換えてっと。

多分接続先をlocalhostにすると、TCP portを使わずにソケット使おうとして、

/var/lib/mysql/mysql.sockを見に行って転けるだろうから、

（ソケットを指定できそうなパラメータはconfig.phpには無かったので)

$host = '127.0.0.1';

$port = '64055';

にしておく。

…が、敢え無く失敗。

FATAL: Cannot connect to MySQL server on '127.0.0.1'. Please make sure you have specified a valid MySQL database name in 'include/config.php'

ヽ(｀Д´)ﾉ 肝心の`どうして接続出来なかったのか'が書いてNEEE。

ポートもDB名もユーザー名もパスワードも、クライアントからは行けるのになぁ。。

と思ってtcpdumpを取る。

# tcpdump -i any port 64055

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode

listening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes

^C

0 packets captured

0 packets received by filter

0 packets dropped by kernel

…0 packets caputured。。

64055叩いてないねこりゃ。3306になってるかな？

行ってみようstrace。

# strace php index.php 2>&1 | egrep -v "map"

execve("/usr/bin/php", ["php", "index.php"], [/* 23 vars */]) = 0

..

socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 4

fcntl(4, F_SETFL, O_RDONLY)             = 0

fcntl(4, F_GETFL)                       = 0x2 (flags O_RDWR)

fcntl(4, F_GETFL)                       = 0x2 (flags O_RDWR)

fcntl(4, F_SETFL, O_RDWR|O_NONBLOCK)    = 0

connect(4, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("127.0.0.1")}, 16) = -1 EINPROGRESS (Operation now in progress)

fcntl(4, F_SETFL, O_RDWR)               = 0

poll([{fd=4, events=POLLIN|POLLPRI}], 1, 60000) = 1 ([{fd=4, revents=POLLIN|POLLERR|POLLHUP}])

setsockopt(4, SOL_SOCKET, SO_RCVTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0

setsockopt(4, SOL_SOCKET, SO_SNDTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0

setsockopt(4, SOL_IP, IP_TOS, [8], 4)   = 0

setsockopt(4, SOL_TCP, TCP_NODELAY, [1], 4) = 0

setsockopt(4, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0

poll([{fd=4, events=POLLIN}], 1, 60000) = 1 ([{fd=4, revents=POLLIN|POLLERR|POLLHUP}])

read(4, 0x189ede0, 16384)               = -1 ECONNREFUSED (Connection refused)

shutdown(4, 2 /* send and receive */)   = -1 ENOTCONN (Transport endpoint is not connected)

close(4)                                = 0

..

はい、大当たり。。

この出力のちょっと前あたりに

..

open("/var/www/cacti/lib/adodb/adodb-iterator.inc.php", O_RDONLY) = 4

..

open("/var/www/cacti/lib/database.php", O_RDONLY) = 4

..

open("/var/www/cacti/lib/adodb/drivers/adodb-mysql.inc.php", O_RDONLY) = 4

..

と並んでいるのを見つけるので、この辺りを探る。

lib/database.phpの中で

$cnn_id = ADONewConnection($dsn);

で接続しているところまでは雰囲気で判ったんだけど、その後がどうも判らない。。(PHP読めない)

$dsnはちゃんとport=64055を渡してるんだけどな。。書き方が違うのかしら。よく判らん。

結局こちらさまを参考にしてsocketファイルでつなぐ様に変えたら5秒で動かせたんだけど、

/etc/php.iniとかが怪しいのかなぁ…？

5秒で済むことに30分かけて結局謎が解けなくても、楽しかったから良いのです。

バイナリだったらこのままgdbでステップ実行してたと思う。

There's more than one way to do itなのですよ（いや、解決してないけど）

良いお年を。

mysqld_multiを使って強引に4つのメジャーバージョンを使い分ける

ちょこちょこ試したりする為に1つのVMにいくつもMySQLを入れてたりするけれど、
いちいちmy.cnfを書き分けるのが嫌になったのでついにmysqld_multiを使うことに。

mysqld_multiはたぶん1つのバイナリで複数のインスタンスを立ち上げることをメイン用途として想定している気がするけれど、意外と何とかなったのでメモ。


# ls -l /usr/mysql

合計 16
drwxr-xr-x 10 mysql mysql 4096 12月 27 16:23 2012 5.0.96
drwxr-xr-x 10 mysql mysql 4096 12月 27 11:26 2012 5.1.66
drwxr-xr-x 13 mysql mysql 4096 12月 27 11:44 2012 5.5.29
drwxr-xr-x 13 mysql mysql 4096 12月 27 16:48 2012 5.6.9

ソースコンパイルの時に、./configure --prefix=/usr/mysql/x.x.xxだったり
cmake -DCMAKE_INSTALL_PREFIX=/usr/mysql/x.x.xxだったりでまるっと突っ込んでいる。


強引にいったmy.cnfはこちら。



[mysqld_multi]
user       = mysqld_multi
password   = mysqld_multi
log        = /var/log/mysqld_multi.log

[mysqld]
user = mysql

log-error = error.log
log-warnings = 1
log-bin = bin
relay-log = relay
loose-binlog-format = mixed
loose-skip-general-log
loose-general-log-file = general.log
loose-slow-query-log
loose-slow-query-log-file = slow.log
long-query-time = 1
loose-log-output = FILE

character-set-server = utf8

innodb-buffer-pool-size = 1G
innodb-log-file-size = 128M
innodb-file-per-table = 1

[client]

[mysql]
no-auto-rehash

[mysqldump]
single-transaction
triggers
routines
events

[mysqld50]
basedir    = /usr/mysql/5.0.96
mysqld     = /usr/mysql/5.0.96/bin/mysqld_safe
socket     = /usr/mysql/5.0.96/data/mysql.sock
port       = 64050
pid-file   = /usr/mysql/5.0.96/data/mysql.pid
datadir    = /usr/mysql/5.0.96/data
log-slow-queries = slow.log
skip-log = general.log

[mysqld51]
basedir    = /usr/mysql/5.1.66
mysqld     = /usr/mysql/5.1.66/bin/mysqld_safe
socket     = /usr/mysql/5.1.66/data/mysql.sock
port       = 64051
pid-file   = /usr/mysql/5.1.66/data/mysql.pid
datadir    = /usr/mysql/5.1.66/data

[mysqld55]
basedir    = /usr/mysql/5.5.29
mysqld     = /usr/mysql/5.5.29/bin/mysqld_safe
socket     = /usr/mysql/5.5.29/data/mysql.sock
port       = 64055
pid-file   = /usr/mysql/5.5.29/data/mysql.pid
datadir    = /usr/mysql/5.5.29/data

[mysqld56]
basedir    = /usr/mysql/5.6.9
mysqld     = /usr/mysql/5.6.9/bin/mysqld_safe
socket     = /usr/mysql/5.6.9/data/mysql.sock
port       = 64056
pid-file   = /usr/mysql/5.6.9/data/mysql.pid
datadir    = /usr/mysql/5.6.9/data


5.0～5.6まで共通で使う部分を[mysqld]セクション、
バージョンごと個別の部分を[mysqldxx]セクションに書き込む。

[mysqldxx]セクションは[mysqld-x.x]セクション
(x.x以上のバージョンだったらこのセクション読む、というやつ)と違ってただの名前付けなので、
mysqld_multiから起動した時にだけ個々のセクションを読みに行く。
ので、動作確認でbin/mysqld_safeから上げた時に「あれれ？」となったり。。

この状態で、

# /usr/mysql/5.5.29/bin/mysqld_multi start 56

とかやると、5.6のインスタンスが起動できたり、
(mysqld_multiはただのラッパーなので、取り合えずGAバージョンのmysqld_multiを使っている)

# /usr/mysql/5.5.29/bin/mysqld_multi stop 51-56

とかやると5.1, 5.5, 5.6のインスタンスがいっぺんに止められたりする。


ちなみに.bashrcもひどいことになった。


alias mysqld_multi=/usr/mysql/5.5.29/bin/mysqld_multi
alias mysql50="/usr/mysql/5.0.96/bin/mysql -S /usr/mysql/5.0.96/data/mysql.sock -P 64050"
alias mysql51="/usr/mysql/5.1.66/bin/mysql -S /usr/mysql/5.1.66/data/mysql.sock -P 64051"
alias mysql55="/usr/mysql/5.5.29/bin/mysql -S /usr/mysql/5.5.29/data/mysql.sock -P 64055"
alias mysql56="/usr/mysql/5.6.9/bin/mysql -S /usr/mysql/5.6.9/data/mysql.sock -P 64056"
alias mysqladmin50="/usr/mysql/5.0.96/bin/mysqladmin -S /usr/mysql/5.0.96/data/mysql.sock -P 64050"
alias mysqladmin51="/usr/mysql/5.1.66/bin/mysqladmin -S /usr/mysql/5.1.66/data/mysql.sock -P 64051"
alias mysqladmin55="/usr/mysql/5.5.29/bin/mysqladmin -S /usr/mysql/5.5.29/data/mysql.sock -P 64055"
alias mysqladmin56="/usr/mysql/5.6.9/bin/mysqladmin -S /usr/mysql/5.6.9/data/mysql.sock -P 64056"



localhost向けにはsocket、-hでTCP/IP接続を使う場合と両方に備えて-Sと-Pが両方してあったり。
（つまり、この1台以外にもこんな環境が）

年末の大掃除の代わりに、ってことで。。（掃除しろ。。）

MySQL5.6.8(Red Hat系リポジトリ)の起動が失敗する

wingリポジトリさんのメーリングリストでご指名いただいたので調べた時のメモ。


MySQL5.6.8のrpmをビルドしてインストールすると、/etc/init.d/mysqld startでコケるらしい。
MySQL5.5.xを入れてあって、アンインストールしてからだとコケないらしい。
MySQL5.5.xを入れてあってアンインストールした後、/var/lib/mysqlを消してからだとコケるらしい。

メールに貼ってあったエラーログには、

ERROR: 1005  Can't create table 'db' (errno: 13)

errno: 13ってPermission Deniedだなぁ。。

MySQL5.5.xの作った/var/lib/mysqlを残しておけばコケないらしいので、

やっぱり/var/lib/mysqlを作る時とかのパーミッションがおかしいんだろうなぁ。

それでmysql_install_dbがコケてるのかしら。

でも/etc/init.d/mysqld startじゃなくて直接/usr/bin/mysql_install_dbを叩くと通るなぁ。

5.6系のmysql_install_dbはPerlスクリプトなので、シバンを#!/usr/bin/perl -dに書き換えて、

$DB::single=1をちょこちょこ追加して様子を見る。

Perl版mysql_install_dbがおかしいってどこかで見た気がするなぁとか思いながら。

（記憶にあったのはriywoさんのBlogだった。

 mysql_install_db perl version has a bug? http://weblog.riywo.com/post/35520875097）

うん、確かに/var/lib/mysqlをmysql:mysqlにした後、

/var/lib/mysql/mysqlをroot:rootで作ってその後chownされてないな。

Bugsにも上がってる。次回のビルドでは直るって。

Bug #67672 new perl mysql_install_db fails when run as root with --user option

取り敢えずBugsに上がっていた修正を自分のmysql_install_dbに入れて、

diff -c取ってＷＩＮＧ☆さんに丸投げしてみた。

こういう連携で何かが上手く動くのって、とても楽しかったりするよね。

ところで、WINGリポジトリのMySQLはepelのspecファイルとかを流用して作ってると聞いていたけど、

epel系のrpmってインストール時にmysql_install_dbじゃなくて、/etc/init.d/mysqldが叩くのね。

ちょっと新鮮でした。

せがれ vs とうさん in 2012

子育てエンジニア advent calendar 2012 参加エントリです。

せがれは今年5月で2歳になりました。
2012年のせがれとの攻防を以下に。

俺が子供の時もそうだった様に、ボタン（押す方）が大好き。
　⇒とうさん貧弱なデスクトップでMySQLコンパイル中。
　　⇒せがれ電源ボタン長押し。
　　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ
　⇒電源ボタンを押せない様に手でガードしながら、とうさん再度MySQLコンパイル。
　　⇒せがれ電源タップスイッチのスイッチ押下。
　　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ
家で勉強してられなーいorz

勉強用に安いノートPC買いました。これで死角は無い。
　⇒とうさん貧弱なノートPCでtpcc-mysqlロード中。コーヒーでも淹れるかな。
　　⇒せがれキーボード乱打。偶然Ctrl+Cが入ったっぽい。
　　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ
　⇒とうさん腹ばいになってログ解析中。
　　⇒迷わず背中にまたがる。いつもみたいにお馬さんパカパカしろよ、しないのかよ、うわーん！
　　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ背中を尻でどすどすしないで
結局デスクトップ用の机のモニターをどかしてやってるというorz

そろそろ買い換えたい初代XPERIA。でもまだまだ現役。
せがれはとうさんのすまほんでYoutube見るのがお気に入り。
　⇒とうさんメールなう。
　　⇒せがれ「なにみる？ なにみる？」
　　　⇒Σ(ﾟдﾟlll) たまにはYoutube意外のこともしてるんだよ！？
　⇒とうさんお風呂上り。
　　⇒せがれ「とうさんケイタイ、はいっ」 渡してくれる。
　　　⇒とうさん「あー、ありがとありがと。でもまだドライヤーしてないから持ってて」
　　　　⇒せがれ「うん。なにみる？」
　　　　　⇒Σ(ﾟдﾟlll) すまほんは湯上りの湿った指だと操作できないんだYO！？
最近はアプリを立ち上げてやれば自分で動画を選んで見るようになった。後世畏るべし。。

とうさん寝るときメガネ外す（あたりまえ）
いつもメガネを置くのはカーテンの後ろ（高さ1mくらい、窓の枠）
　⇒朝、せがれが先に起きている。床にはつるがねじ曲がって折れたメガネ。
　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰいつの間に手が届くように
眼鏡屋さんが引くくらいつるがべきっといってたけど、なんとか直してくれました。
とうさん寝るときメガネ外す（リピート）
　⇒朝、せがれが先に（ｒｙ
　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ
2回目持っていったときは流石に色々心配されたり怒られたり可哀想な目で見られたり。。orz
取り敢えずメガネドラッグさん愛してる。

とうさん(平成)仮面ライダーが好き。
　⇒1歳の頃からせがれにベルトを巻かせて変身ポーズを練習させた。
　　⇒せがれ「へんしんっ！ あいだー、きっく！(カブト風)」
　　　⇒Σ(ﾟдﾟlll) どめすてぃっくばいおれーんす！
　　⇒せがれ「へんしんっ！(右手を自分のおへそにつける) ぷぃーじゅ！ ひー！ ひー！」 でんぐりがえし。
　　　⇒Σ(ﾟдﾟlll) ねえそれキックストライクなの！？ ﾁｮｰｲｲﾈｻｲｺｰ！？
　　　　⇒地味に色々巻き込まれてとうさんのご飯とかご飯とかご飯とかorz
仮面ライダーウィザード(2012/09～)のベルトはまだ買ってません。



これみんな、去年はできなかった、今年になってできる様になった攻撃。
来年はどんな攻撃を仕掛けてくるのか。
とうさんとせがれの戦いは来年も再来年も続く。

かかってきたまえ。
ただしメガネへの打撃と武器の使用は勘弁な。。orz