GA

2014/12/25

MySQL Fabricつらい(クライアントとしてのmysqlfabricとサーバーとしてのmysqlfabricのオプションの扱いが一緒)

/etc/mysql/fabric.cfg はイケてない。
何がどうイケてないかというと、クライアントとしてのmysqlfabricとサーバーとしてのmysqlfabricを全く区別していないところ。

最初に(そしてこれは随分前から)気が付いたのは、[protocol.xmlrpc]セクションと[protocol.mysql]セクションのdisable_authentication。

[protocol.xmlrpc]
disable_authentication = no


デフォルトはnoだが、認証を無効化したいと思うとyesにする。
だがしかしこれ、サーバーとして起動されたときとクライアントとして起動されたときで同じパラメーターを使っているため、MySQL Fabricサーバーが起動している最中に書き換えたりすると(サーバーは再起動するまでその内容が有効にならないので)、結構アレってなる。

それはいい。それは全然いい。困らない。


だがこれはつらい。


$ less /etc/mysql/fabric.cfg
..
[protocol.xmlrpc]
..
address = localhost:32274
..

$ mysqlfabric manage start
[INFO] 1419482563.650106 - MainThread - Initializing persister: user (backingstore), server (localhost:3306), database (fabric).
[WARNING] 1419482563.670879 - MainThread - Provider error: No module named novaclient.
[INFO] 1419482563.671135 - MainThread - Loading Services.
[WARNING] 1419482563.686219 - MainThread - Authentication disabled
[INFO] 1419482563.686369 - MainThread - MySQL-RPC protocol server started, listening on localhost:32275
[WARNING] 1419482563.686489 - MainThread - Authentication disabled
[INFO] 1419482563.695672 - MainThread - Fabric node starting.
[INFO] 1419482563.698753 - MainThread - Starting Executor.
[INFO] 1419482563.698855 - MainThread - Setting 5 executor(s).
[INFO] 1419482563.699274 - Executor-0 - Started.
[INFO] 1419482563.701372 - Executor-1 - Started.
[INFO] 1419482563.702225 - Executor-2 - Started.
[INFO] 1419482563.703481 - Executor-3 - Started.
[INFO] 1419482563.705299 - Executor-4 - Started.
[INFO] 1419482563.705777 - MainThread - Executor started.
[INFO] 1419482563.710501 - MainThread - Starting failure detector.
[INFO] 1419482563.711543 - XML-RPC-Server - XML-RPC protocol server ('127.0.0.1', 32274) started.
[INFO] 1419482563.712189 - XML-RPC-Server - Setting 1 XML-RPC session(s).
[INFO] 1419482563.712714 - XML-RPC-Session-0 - Started XML-RPC-Session.

$ ss -ltp
State      Recv-Q Send-Q                             Local Address:Port                                 Peer Address:Port
LISTEN     0      0                                      127.0.0.1:32274                                           *:*        users:(("mysqlfabric",675,4))
LISTEN     0      0                                      127.0.0.1:32275                                           *:*        users:(("mysqlfabric",675,6))
LISTEN     0      0                                             :::mysql                                          :::*

[protocol.xmlrpc]セクションのaddressはmysqldでいうところの--bind-addressと--portをあわせたような用途で使われる。つまりが、address=localhost:32274で起動していると、127.0.0.1:32274で起動してしまうので、他のホストから叩けなくなる。しょんぼり。

なので当然、address=0.0.0.0:32274とかやるわけだ。


[INFO] 1419482742.772749 - MainThread - Initializing persister: user (backingstore), server (localhost:3306), database (fabric).
[WARNING] 1419482742.792821 - MainThread - Provider error: No module named novaclient.
[INFO] 1419482742.793089 - MainThread - Loading Services.
[WARNING] 1419482742.805074 - MainThread - Authentication disabled
[INFO] 1419482742.805208 - MainThread - MySQL-RPC protocol server started, listening on 0.0.0.0:32275
[WARNING] 1419482742.805334 - MainThread - Authentication disabled
[INFO] 1419482742.814918 - MainThread - Fabric node starting.
[INFO] 1419482742.818677 - MainThread - Starting Executor.
[INFO] 1419482742.818770 - MainThread - Setting 5 executor(s).
[INFO] 1419482742.819158 - Executor-0 - Started.
[INFO] 1419482742.819843 - Executor-1 - Started.
[INFO] 1419482742.821553 - Executor-2 - Started.
[INFO] 1419482742.822262 - Executor-3 - Started.
[INFO] 1419482742.825073 - Executor-4 - Started.
[INFO] 1419482742.825355 - MainThread - Executor started.
[INFO] 1419482742.829459 - MainThread - Starting failure detector.
[INFO] 1419482742.830409 - XML-RPC-Server - XML-RPC protocol server ('0.0.0.0', 32274) started.
[INFO] 1419482742.830877 - XML-RPC-Server - Setting 1 XML-RPC session(s).
[INFO] 1419482742.831168 - XML-RPC-Session-0 - Started XML-RPC-Session.

State      Recv-Q Send-Q                             Local Address:Port                                 Peer Address:Port
LISTEN     0      0                                              *:32274                                           *:*        users:(("mysqlfabric",702,4))
LISTEN     0      0                                              *:32275                                           *:*        users:(("mysqlfabric",702,6))
LISTEN     0      0                                             :::mysql                                          :::*

$ mysqlfabric group lookup_groups
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                  group_id description failure_detector master_uuid
-------------------------- ----------- ---------------- -----------
fabric_server_in_container        None                0        None


ここまではいい。
さて、じゃあ隣のノードからこのmysqlfabricサーバーを叩くにはどうしたらいいか?

そう、 *クライアント側のfabric.cfgの[protocol.xmlrpc]のaddressをここに指定する*


$ mysqlfabric group lookup_groups
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                  group_id description failure_detector master_uuid
-------------------------- ----------- ---------------- -----------
fabric_server_in_baremetal        None                0        None

$ vim /etc/mysql/fabric.cfg
..
[protocol.xmlrpc]
..
#address = localhost:32274
address = 172.17.0.144:32274
..

$ mysqlfabric group lookup_groups
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                  group_id description failure_detector master_uuid
-------------------------- ----------- ---------------- -----------
fabric_server_in_container        None                0        None




--paramオプションを使えば特定のオプションだけ上書きできるはずだから、わざわざ書き換えなくてもいいはずなんだけど、エラーメッセージも謎いし、受け取っても気に食わない形だとエラーも何もせずに読み飛ばしやがるので、全然わからない。

フンス。


【2014/12/25 14:04】
もともとは MySQL FabricのファームになれるようなDockerイメージを作ったやつ のついでに、だったらMySQL FabricサーバーそのものもDockerイメージにすればいいんじゃん? と思ってハマったのがこの記事だったり。

yoku0825/mysql_fabric_server ってイメージもつくりました。バッキングストアとMySQL Fabricサーバーがそのまま起動できるヤーツ。

【2015/01/02 16:43】↑リポジトリとリンク張り替えた

2014/12/19

DockerでMySQL Fabricをさっくり試すための整備をしたつもり

みんな、僕と地雷友達になってよ!

【2014/12/25 14:03】 リンク間違ってんじゃん。。
https://github.com/yoku0825/my_script/blob/master/make_centos_baseimage_for_docker.sh
https://github.com/yoku0825/my_script/blob/master/make_mysqlfabric_sandbox.sh


MySQL Fabricのファームにすぐに突っ込めるようなDockerイメージ を使ってもろもろ整理した結果、こんなんなりました。

確認した環境は、 どっかのVPS の テンプレートイメージ"CentOS 6.5(64bit)"、epel-releaseは予め入ってました。


# rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
# yum install -y mysql-server mysql-utilities docker-io

DockerホストをそのままMySQL Fabricサーバー(バッキングストア相乗り)にするので、そのへんをゴニョゴニョ。
あとそういう趣旨なのでDockerも入れないと。


# vim /etc/mysql/fabric.cfg
..
# diff -C 0 /etc/mysql/fabric.cfg.orig /etc/mysql/fabric.cfg
*** /etc/mysql/fabric.cfg.orig  2014-12-19 17:57:22.512670132 +0900
--- /etc/mysql/fabric.cfg       2014-12-19 17:56:43.957666364 +0900
***************
*** 16 ****
! user = fabric
--- 16 ----
! user = backingstore
***************
*** 20 ****
! password =
--- 20 ----
! password = backingstore
***************
*** 34 ****
! password =
--- 34 ----
! password = fabric_password
***************
*** 45 ****
! disable_authentication = no
--- 45 ----
! disable_authentication = yes
***************
*** 64 ****
! disable_authentication = no
--- 64 ----
! disable_authentication = yes

色々面倒なのでXML経由もMySQLプロトコル経由もdisable_authentication=yesにしてしまう。
バッキングストアのユーザー名を変えてるのは単なる気まぐれ。
Dockerイメージには予めFabric管理用の特権ユーザーとしてfabricユーザーがいるので、なんとなくかぶせたくなかっただけ。


# /etc/init.d/mysqld start
# mysql
mysql> GRANT ALL ON fabric.* TO backingstore@127.0.0.1 IDENTIFIED BY 'backingstore';
Query OK, 0 rows affected (0.02 sec)

# /etc/init.d/docker start
# git clone https://github.com/yoku0825/my_script
# my_script/make_mysqlfabric_sandbox.sh start global shard1 shard2
..
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc:
Repeat Password:
Password set.
Password set.
No result returned
..
# my_script/mysqlfabric_tree.sh
global
        3d2f0764-8760-11e4-baf4-0242ac110002 172.17.0.2 PRIMARY READ_WRITE    1.0
shard1
        4395a789-8760-11e4-baf4-0242ac110003 172.17.0.3 PRIMARY READ_WRITE    1.0
shard2
        4b23890a-8760-11e4-baf4-0242ac110004 172.17.0.4 PRIMARY READ_WRITE    1.0

途中、mysqlfabricを初期化してるところでパスワードは聞かれる(そして空にできない)ので取り敢えず入力するけど、disable_authentication=yesなので実際にコマンドラインクライアントから叩くときには使われない。これだけで1グループ1台構成のシャーディングの準備ができる。そのうち、1グループに2台以上追加する何かも考えようかどうしようか。

大事なことなのでもう一度いいます。
僕と地雷友達になってください :)

さて、やっとここまで来られたので次はシャードの検証しましょうかね。。


【2015/02/23 16:00】
そういえば、コンマ区切りで各グループのインスタンスの数を制御できるようにしてあった。
https://github.com/yoku0825/my_script/commit/6fb06fae6784fe5eb8e2b83b1292a9b7b15f8504

# ./make_mysqlfabric_sandbox.sh start shard1,2 shard2,3
..

# ./mysqlfabric_tree.sh
shard2
        0a3c9c1c-bb2a-11e4-8ca9-0242ac110058 172.17.0.88 SECONDARY  READ_ONLY    1.0
        1265aaa4-bb2a-11e4-8ca9-0242ac110059 172.17.0.89    FAULTY  READ_ONLY    1.0
        1ada394c-bb2a-11e4-8ca9-0242ac11005a 172.17.0.90   PRIMARY READ_WRITE    1.0
shard1
        019dec7f-bb2a-11e4-8ca8-0242ac110057 172.17.0.87 SECONDARY  READ_ONLY    1.0
        fb300b5b-bb29-11e4-8ca8-0242ac110056 172.17.0.86   PRIMARY READ_WRITE    1.0

MySQL Fabricつらい(複数グループの視認性が悪いのでスクリプト書いた…けど実は既にあった)

MySQL Fabricの何が面倒って、複数グループあっても登録してるサーバーを全部一覧したいときにそのコマンドがない(ような気がする)ところ。

取り敢えずシェルスクリプト書いた。
https://github.com/yoku0825/my_script/blob/master/mysqlfabric_tree.sh


$ ./mysqlfabric_tree.sh
global
        105087a3-8752-11e4-ba97-4277c372408b 172.17.0.80 SECONDARY READ_ONLY    1.0
shard1
        134469a1-8752-11e4-ba98-1a32de75db4e 172.17.0.81 SECONDARY READ_ONLY    1.0
shard2
        16937ac5-8752-11e4-ba98-d60c364380fa 172.17.0.82 SECONDARY READ_ONLY    1.0
shard3
        19ea7137-8752-11e4-ba98-32bfdacb274f 172.17.0.83 SECONDARY READ_ONLY    1.0

よしOK。
というかmysqlfabricコマンド側に入れてくれないかねこういうの。


【2014/12/19 17:21】
と思ったらあった。

$ mysqlfabric dump servers
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid group_id        host port mode status weight
------------------------------------ -------- ----------- ---- ---- ------ ------
105087a3-8752-11e4-ba97-4277c372408b   global 172.17.0.80 3306    1      2    1.0
134469a1-8752-11e4-ba98-1a32de75db4e   shard1 172.17.0.81 3306    1      2    1.0
16937ac5-8752-11e4-ba98-d60c364380fa   shard2 172.17.0.82 3306    1      2    1.0
19ea7137-8752-11e4-ba98-32bfdacb274f   shard3 172.17.0.83 3306    1      2    1.0



マニュアルに負けたなんて。。ダメダメだ。。
http://dev.mysql.com/doc/mysql-utilities/1.5/en/fabric-util-store-commands.html

( ´-`).oO(modeとstatusが数値じゃなくて人間に読めるほうの値で出してくれればいいのに。。

2014/12/18

MySQL Fabricのファームにすぐに突っ込めるようなDockerイメージを作ってみた

今までずっと MySQL::Sandbox で試してきたけど、ふと思いついたのでDockerイメージにしてみた。
Dockerが面白くて肝心のMySQL Fabricを全然触ってなかった気がするけど、いいの。

取り合えずMySQL Fabricに必要な要素としては、
* gtid-mode= ON
* server-idはカブらないように
* Fabricノードからファームのサーバーへはフルアクセスのユーザーが必要
くらいでしょうか。

何台追加で作ってもserver-idがカブらないようにするために、server-idはIPアドレスの下2バイトから自動生成するようにmysqld_safeにパッチをしてあります(--server-id=autoで、server_id= 3バイト目 * 256 + 4バイト目に自動設定する)

( ´-`).oO(これ、今まで誰もFRとか上げなかったのかな。。

Dockerfile見るのが一番判りやすいんですが、

# cat Dockerfile
FROM yoku0825/cent66:init
MAINTAINER yoku0825
RUN yum install -y perl libaio
RUN rpm -i http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-server-5.6.22-1.linux_glibc2.5.x86_64.rpm
RUN rpm -i http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-client-5.6.22-1.linux_glibc2.5.x86_64.rpm
ADD ./mysqld_safe.patch /root/mysqld_safe.patch
RUN patch /usr/bin/mysqld_safe /root/mysqld_safe.patch
ADD ./my.cnf /etc/my.cnf
ADD ./init_mysql.sh /root/init_mysql.sh
RUN bash /root/init_mysql.sh
EXPOSE 3306
CMD /etc/init.d/mysql start && tail -f /dev/null

クライアント(MySQL Fabric用のユーザー作ったりするのに使う)とサーバーだけrpmで突っ込んで、mysqld_safeにパッチ当てて、MySQLのユーザー作成はシェルスクリプトで押し込んで、というのがこれだけで書けました。楽チン。



# docker run -d -h fabric1 --name fabric1 fabric_aware_5622
da51ddad4e26f228435b60e2b2ec6c1dc465438b640575f232691b07affaeb4d

# docker run -d -h fabric2 --name fabric2 fabric_aware_5622
fd84f396f8fbcf688db6ca689b10f1d8bccb6dcc12fac3eb0c82a2639a23168f

# docker run -d -h fabric3 --name fabric3 fabric_aware_5622
8f5bc6199cb699048194bd1aff92d6ed0e8caad406e1a222f4e908fe8478509c

# docker inspect -f "{{.Name}}, {{.Config.Hostname}}, {{.NetworkSettings.IPAddress}}" $(docker ps | grep -v "^CONTAINER" | awk '{print $1}')
/fabric3, fabric3, 172.17.0.54
/fabric2, fabric2, 172.17.0.53
/fabric1, fabric1, 172.17.0.52

docker runでドカドカ上げて、


$ mysqlfabric group create my_docker_fabric
$ mysqlfabric group add my_docker_fabric 172.17.0.52
$ mysqlfabric group add my_docker_fabric 172.17.0.53
$ mysqlfabric group add my_docker_fabric 172.17.0.54
$ mysqlfabric group promote my_docker_fabric

$ mysqlfabric group lookup_servers my_docker_fabric
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid     address    status       mode weight
------------------------------------ ----------- --------- ---------- ------
b9d7c753-86a3-11e4-b627-aa6edb94a664 172.17.0.52 SECONDARY  READ_ONLY    1.0
bd269d22-86a3-11e4-b627-e6e3a6deb92c 172.17.0.53 SECONDARY  READ_ONLY    1.0
bff11b23-86a3-11e4-b627-a6df24859725 172.17.0.54   PRIMARY READ_WRITE    1.0

これだけで簡単に誰でもMySQL Fabricを試せるようになりました。。と、いいな。
ホストネームとIPアドレスの紐付けをDockerのホスト側になんか持たせようかしら(mysqlfabricとバッキングストアはDockerのホストにいる想定)

これでtcpdumpが見やすくなる!


DockerfileはGithubに。

Docker Hubだとここ。
【2014/01/02 16:41】リポジトリ変えた
https://registry.hub.docker.com/u/yoku0825/mysql_fabric_aware/

yoku0825/cent66:fabric_aware_5622っていうタグのがそうです。

Have fun!


【2014/12/19 18:40】
このあたりのmysqlfabric group create/add を自動でやるようなスクリプトも書いた。
http://yoku0825.blogspot.jp/2014/12/dockermysql-fabric.html

2014/12/12

Mroongaの全文検索が上手くいかない気がするときの初動メモ

この記事は Groonga Advent Calendar 2014 の12日目の記事です。

最初は「Mroonga with MySQL Fabric(仮)」というタイトルを考えていたんですが、MySQL FabricはフツーのMySQLサーバーの上でマスター昇格とかよしなにやってくれるためのミドル(?)ウェアなので、Mroongaも動いて当たり前だと思ってやめました。

じゃあ別のMySQLのHA実装のXtraDB Clusterでもやろうかなと思いましたが、よく考えたらそれはもう1年半前にやっていて ( mroongaをXtraDB Clusterで冗長化できそうなメモ ) 当時はまだ"m"が小文字だったんだなーとか感慨深いですね。
PXCも5.6になりましたしwsrepもバージョン上がってますが、今でもできるのかどうかは知りません。誰か試してください。

MySQL Cluster with Mroongaは仕組み上不可能だし (ラッパーモード使っても、SQLノード上に転置索引が作成されるのでダメ) もうHAやめて、フツーに普段やっていることをメモしておこうと思います。

長かったな前置き。


基本的にMySQLしか触ったことなかった人がMroongaで全文検索…なのを想定しています。そう、俺だ。
さて、何はともあれテーブルの定義を確認したりします。


mysql56> use d1
Database changed

mysql56> CREATE TABLE t1 (num serial, val varchar(32), FULLTEXT KEY(val)) Engine= Mroonga Comment= 'Engine "InnoDB"';
Query OK, 0 rows affected (0.06 sec)

mysql56> SELECT mroonga_command('table_list')\G
*************************** 1. row ***************************
mroonga_command('table_list'): [[["id","UInt32"],["name","ShortText"],["path","ShortText"],["flags","ShortText"],["domain","ShortText"],["range","ShortText"],["default_tokenizer","ShortText"],["normalizer","ShortText"]],[259,"t1","d1.mrn.0000103","TABLE_HASH_KEY|PERSISTENT","ShortText",null,null,null],[260,"t1-val","d1.mrn.0000104","TABLE_PAT_KEY|PERSISTENT","ShortText",null,"TokenBigram","NormalizerMySQLGeneralCI"]]
1 row in set (0.00 sec)

mysql56>
mysql56> SELECT mroonga_command('table_list --output_type tsv')\G
*************************** 1. row ***************************
mroonga_command('table_list --output_type tsv'): "id"   "UInt32"
"name"  "ShortText"
"path"  "ShortText"
"flags" "ShortText"
"domain"        "ShortText"
"range" "ShortText"
"default_tokenizer"     "ShortText"
"normalizer"    "ShortText"
259
"t1"
"d1.mrn.0000103"
"TABLE_HASH_KEY|PERSISTENT"
"ShortText"



260
"t1-val"
"d1.mrn.0000104"
"TABLE_PAT_KEY|PERSISTENT"
"ShortText"

"TokenBigram"
"NormalizerMySQLGeneralCI"
1 row in set (0.02 sec)

特に何もこだわりがないときは--output_type tsvを指定しておくと、改行が多くなって見やすいのでわたしは好きです。

groongaコマンドラインクライアントから叩くときは、余計なものが何もついていない"データベース名".mrnファイルを指定します。


$ /usr/groonga/4.0.6/bin/groonga d1.mrn
> table_list --output_type tsv
0       1418374830.69561        0.00125479698181152
"id"    "UInt32"
"name"  "ShortText"
"path"  "ShortText"
"flags" "ShortText"
"domain"        "ShortText"
"range" "ShortText"
"default_tokenizer"     "ShortText"
"normalizer"    "ShortText"
259
"t1"
"d1.mrn.0000103"
"TABLE_HASH_KEY|PERSISTENT"
"ShortText"



260
"t1-val"
"d1.mrn.0000104"
"TABLE_PAT_KEY|PERSISTENT"
"ShortText"

"TokenBigram"
"NormalizerMySQLGeneralCI"
END

何が書いてあるのか詳しくは判りませんが、転置索引はt1-val("テーブル名"-"インデックス名")というオブジェクトに入っているようです。
トークナイザーはTokenBigram, ノーマライザーはNormalizerMySQLGeneralCIですね。トークナイザーやノーマライザーをTypoするとここが想定しているのと違う値になったりしますね_| ̄|○


mysql56> SELECT * FROM t1;
+-----+----------+
| num | val      |
+-----+----------+
|   1 | yoku0825 |
|   2 | yoku0826 |
+-----+----------+
2 rows in set (0.00 sec)

mysql56> SELECT mroonga_command('select t1-val --output_type tsv')\G
*************************** 1. row ***************************
mroonga_command('select t1-val --output_type tsv'): 3
[       "_id"   "UInt32"        ]       [       "_key"  "ShortText"     ]       [       "index" "t1"    ]
2       "0825"  1
3       "0826"  1
1       "YOKU"  4
1 row in set (0.02 sec)

実際にトークナイズされた結果が見られます。本番でこれをやると結構大変なので、CREATE TABLE .. LIKE, INSERT INTO .. SELECTでズレてる気がする行だけを抽出して読みます。


mysql56> ALTER TABLE t1 DROP KEY val;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql56> ALTER TABLE t1 ADD FULLTEXT KEY (val) Comment 'parser "TokenBigramIgnoreBlankSplitSymbolAlphaDigit"';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql56> SELECT mroonga_command('table_list --output_type tsv')\G
*************************** 1. row ***************************
mroonga_command('table_list --output_type tsv'): "id"   "UInt32"
"name"  "ShortText"
"path"  "ShortText"
"flags" "ShortText"
"domain"        "ShortText"
"range" "ShortText"
"default_tokenizer"     "ShortText"
"normalizer"    "ShortText"
259
"t1"
"d1.mrn.0000103"
"TABLE_HASH_KEY|PERSISTENT"
"ShortText"



260
"t1-val"
"d1.mrn.0000104"
"TABLE_PAT_KEY|PERSISTENT"
"ShortText"

"TokenBigramIgnoreBlankSplitSymbolAlphaDigit"
"NormalizerMySQLGeneralCI"
1 row in set (0.02 sec)

mysql56> SELECT mroonga_command('select t1-val --limit -1 --output_type tsv')\G
*************************** 1. row ***************************
mroonga_command('select t1-val --limit -1 --output_type tsv'): 10
[       "_id"   "UInt32"        ]       [       "_key"  "ShortText"     ]       [       "index" "t1"    ]
5       "08"    8
7       "25"    1
9       "26"    1
8       "5"     1
10      "6"     1
6       "82"    9
3       "KU"    6
2       "OK"    5
4       "U0"    7
1       "YO"    4
1 row in set (0.02 sec)

たとえばトークナイザーを変えるとどうなるんだとかいうのはこのように調べています。(Groongaの)selectコマンドは暗黙に--limit 10を押し込んでくれるので、トークンが10個以上に分かれる(たぶん、フツーは分かれる、だろう)場合は--limit -1で無制限に出力させられます。

こんな感じで調べて、なんかどうも全角の記号より後ろに詰められた文字がちゃんとトークナイズされてなくね? とかいうのに気付いたり気付かなかったりします。
http://sourceforge.jp/projects/groonga/lists/archive/dev/2014-February/002110.html


ちなみにTritonn時代にもsennaコマンドラインクライアント(たぶんgroongaコマンドラインクライアントと同じような機能を持っていたんだろうけれど)があったんですが、こっちはコマンドのドキュメントがどこにも存在しなかったので挫折しました。。(TritonnからMroongaへの移行の時に、同じようなのが見られたら少し便利だっただろうなぁ、と思いつつ)

とはいえ、「検索結果がおかしい」ことに気付くのって難しいですよね。。

2014/12/10

MariaDB 10.0のCONNECTストレージエンジンからOracle DBに接続してみる

この記事は JPOUG Advent Calendar 2014 の10日目の記事です。

MariaDB 10.0には CONNECTストレージエンジン という MS Accessでいうところのリンクテーブルなストレージエンジンが搭載されていて、リンクの方法にCSVとかXMLとかODBCとか色々あるようです。MySQLに昔から入っているFEDERATEDストレージエンジンはMySQLプロトコルしかしゃべれないのでリンク先は必ずMySQLサーバーでないといけないという制約がありますが、ODBCなら色々できるわけですよね。

というわけでさっくり試してみました。
unixODBC-develと OracleのInstant Client (とりあえず、basic, odbc, develの3つをrpmで突っ込みました)は予めインストールしてあります。

$ wget "https://downloads.mariadb.org/f/mariadb-10.0.15/source/mariadb-10.0.15.tar.gz/from/http%3A/ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb?serve" -O mariadb-10.0.15.tar.gz
$ tar xzf mariadb-10.0.15.tar.gz
$ cmake -DWITH_CONNECT_STORAGE_ENGINE=ON .
$ make
$ make install


ODBCドライバーの設定をしておきます。

$ sudo vim /etc/odbcinst.ini
..
[Oracle 12c ODBC driver]
Description     = Oracle ODBC driver for Oracle 12c
Driver          = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1

$ vim ~/.odbc.ini
[JPOUG]
Driver       = Oracle 12c ODBC driver
ServerName   = xxx.xxx.xxx.xxx:1521/HOGE
DSN          = JPOUG


ではCONNECTテーブルの作成。

mysql> CREATE TABLE connect_test Engine= Connect TABLE_TYPE=ODBC tabname='TABLE_1' CONNECTION='DSN=JPOUG;UID=xxxx;PWD=xxxx';
ERROR 1105 (HY000): [unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1' : file not found

( ゚д゚) ファッ
ライブラリーパスが通ってなさそうなパスに入れたんですね○racleさん。。


$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
$ bin/mysqladmin shutdown
$ bin/mysqld_safe &

取りあえずLD_LIBRARY_PATHに突っ込んでmysqldを再起動。


mysql> CREATE TABLE connect_test Engine= Connect TABLE_TYPE=ODBC tabname='TABLE_1' CONNECTION='DSN=JPOUG;UID=xxxx;PWD=xxxx';
Query OK, 0 rows affected (2.21 sec)

通ったー。


mysql> SELECT * FROM connect_test LIMIT 3;
+---------+-----------+--------+-----------+--------------+------------+---------------------+---------------+--------+
| USER_ID | ACTION_ID | SERIAL | REMOTE_IP | HTTP_REFERER | USER_AGENT | START_TIME          | COMPLETE_TIME | STATUS |
+---------+-----------+--------+-----------+--------------+------------+---------------------+---------------+--------+
|  277385 |       159 |      1 | NULL      | NULL         | NULL       | 2014-10-29 04:43:40 | NULL          |      1 |
+---------+-----------+--------+-----------+--------------+------------+---------------------+---------------+--------+
1 row in set (0.11 sec)


xxxx@test-db03>SELECT * FROM TABLE_1;

   USER_ID  ACTION_ID     SERIAL REMOTE_IP
---------- ---------- ---------- ---------------
HTTP_REFERER
--------------------------------------------------------------------------------
USER_AGENT
--------------------------------------------------------------------------------
START_TI COMPLETE     STATUS
-------- -------- ----------
    277385        159          1


14-10-29                   1

取り合えずテスト用のDBを間借りしただけなので何のデータも入ってないけど、同じデータが引けてはいるぽい。

こんなことして何が嬉しいかというと、MariaDBをオペレーター用(Not アプリケーション用)のプロキシとして使うことで
* Oracle側で設定するよりも柔軟にアクセス制限が出来る(かも知れない)
  * MySQL(MariaDB)はカラム単位までアクセス制限ができる
  * 接続元IPアドレスが違えば別ユーザーとして扱える
  * MariaDBならPAM Auditプラグインが入っているので、既存のLDAPと連携できるかも
* Oracle側でやる気にならなさそうな全件クエリーログとかできる
  * アプリケーション用のクエリーは別の方法を模索するとして、内部アクセスの証左にするぶんならgeneral_logとPAM Auditを使えば結構イケるんじゃね?
なあたりのユースケースが無いかなぁと思っている所存。

MySQL 5.6以降のmysqlコマンドラインクライアントでプロンプトに色を付けるcmakeオプション

この記事は MySQL Casual Advent Calendar 2014 の10日目の記事で、7日目の記事 grcでMySQLのクエリ結果に色を付ける へのアンサーエントリーです :)

MySQL 5.6.12 から、mysqlコマンドラインクライアントが使うデフォルトのreadlineが、フツーの(?) readlineからMySQLにバンドルされているreadlineに変わっています。そのため、MYSQL_PS1(や--prompt)にecho -eの出力結果を食わせてやろうとすると

$ export MYSQL_PS1=$(echo -e "\e[1;33mmysql[\d]\e[0m\n> ")
$ client/mysql -S /usr/mysql/5.6.22/data/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.22-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

1;33mmysql[(none)[0>
1;33mmysql[(none)[0>
1;33mmysql[(none)[0> ^DBye


こうなります。
や、MySQLにバンドルされているやつと、システムワイドでインストールされてるやつでどうreadlineの実装が違うのとか全く興味がないので本当にreadlineのせいかどうかと聞かれると若干困るんですが、システムワイドのreadlineを使うようにしてmakeするとちゃんとこれを解釈できるようになるのでたぶんMySQLにバンドルされているreadlineのせいです(長)


$ sudo yum install libedit-devel
$ cmake -DWITH_EDITLINE=system .
$ make mysql
$ export MYSQL_PS1=$(echo -e "\e[1;33mmysql[\d]\e[0m\n> ")
$ client/mysql -S /usr/mysql/5.6.22/data/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.22-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql[(none)]
>




なおゲスい話ではありますが、接続してるのがマスターかスレーブか、ユーザーはrootか否かとかで更に色を出しわけたりできるので、わたしはmysqlコマンドラインクライアントにパッチをするのも好きです :)

Have fun!!

2014/12/04

MySQL Fabricつらい(PerlのDBD::mysqlを対応させる旅路)

この記事は Perl Advent Calendar 2014 の4日目の記事です。

最近 MySQL Fabric という "MySQLにお手軽HA & シャーディング、しかもプロキシ不要!" なんてことを謳っているミドルウェアで遊んでいるわけですが、このMySQL Fabric、プロキシ不要な代わりに対応しているコネクターが必要になっています(ある意味、コネクターがプロキシ機能を持っている感じ)

さてこのMySQL Fabric対応コネクター、今のところの選択肢は
* Oracle公式
  * GA版
    * Connector/J
    * Connector/Python
  * 開発版(ラボ版)
    * Connector/C
* PHPコミュニティ製
  * 開発版(pre-alpha quority)
    * mysqlnd
だけです。

( ゚д゚) えっ

折角テスト環境作って、テキトーなコード書いてほげほげしようと思っても、普段使いのPerlがリストされてなくてつらい。じゃあPHPでいっかーと思った時期もあるんですが、"Sharding is the only use case supported by the plugin to date."(2014/12/4現在)だそうで、HAの検証ができない。

 迷 わ ず ラ ボ 版 の Connector/C に し ま し た (にこ

( ´-`).oO(このあたり(Connector/C使ってるあたり)は このへん に。。
( ´-`).oO(調子に乗ってそれをmysqlコマンドラインクライアントにポートしたのは このへん に。。

mysqlコマンドラインクライアントのFabric-aware化をやってて気が付いたんですが、PerlのDBD::mysqlもlibmysqlclient(= Connector/C)使ってるんですよね。ということはそれを呼び出す側を実装するのはそう難しくはないはず。(同じことで、libmysqlclientを使ってないドライバーはMySQL Fabric対応しようとすると自前でその部分をまるまる書き上げないといけないのでかなりつらいと思う。PHPとかそれでシャード機能しかまだサポートしてないし、Rubyも大変そう(どっちもlibmysqlclient使ってる実装もあったはずだけど))

という訳で雑にパッチしました。

Support MySQL Fabric in mysql_dr_connect. · 1052599 · yoku0825/DBD-mysql

ざっと言うと、
* もちろんMySQL Fabric対応のConnector/Cをインクルードしてリンクしてコンパイルする必要がある
* まだHA対応の部分しか書いてない。シャードには未対応(Cでもまだ調べてない)
* MySQL Fabric経由で接続する場合にはmysql_initとmysql_real_connectの間にmysql_optionsでMYSQL_OPT_USE_FABRICを押し込んでやる必要がある
  * ここは他のオプションとそんなに変わらないんですが
* mysql_real_connectした *後* で mysql_options{,4}でグループ名とかHAモードを指定してやらないといけない
  * 他の(既存の)オプションは全てmysql_initとmysql_real_connectの間に記述するようになってるので、ここ面倒くさい
    * 今は接続時に全てDSNから受け取って決めうちにしてる。
    * 接続後にグループとかHAモードを書き換えられるから、$dbh->{Mode} = 'ro'; とかで簡単に変えられる未来が来る気がする(まだやってない)
な感じで実装しています。

使い方はこんな感じ。まずはConnector/Cをコンパイル(URLなどは2014/12/4現在のものです。


$ wget http://downloads.mysql.com/snapshots/pb/mysql-connector-c-6.2.0-labs/mysql-connector-c-6.2.0-labs-src.tar.gz
$ tar xzf mysql-connector-c-6.2.0-labs-src.tar.gz
$ cd mysql-connector-c-6.2.0-labs-src
$ cmake -DCMAKE_INSTALL_PREFIX=/usr/mysql/connector .
$ make
$ sudo make install

他のと混じらないように、/usr/mysql/connectorの下に突っ込みました。




$ git clone https://github.com/yoku0825/DBD-mysql
$ cd DBD-mysql/
$ perl Makefile.PL
..
Can't find mysql_config. Use --mysql_config option to specify where mysql_config is located
..

む、Connector/Cにはmysql_configが入ってないから怒られた。取り敢えずyumで突っ込んで、後からMakefileを手で修正することにするか。


$ sudo yum install mysql
$ perl Makefile.PL
$ cp -ip Makefile{,.orig}
$ vim Makefile
..
$ diff -c Makefile{.orig,}
*** Makefile.orig       2014-12-04 05:29:00.748424064 +0000
--- Makefile    2014-12-04 05:33:38.050242874 +0000
***************
*** 161,167 ****
  PARENT_NAME = DBD
  DLBASE = $(BASEEXT)
  VERSION_FROM = lib/DBD/mysql.pm
! INC = -I$(DBI_INSTARCH_DIR) -I/usr/include/mysql55 -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv -fPIC  -fPIC -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DMY_PTHREAD_FASTMUTEX=1 -DDBD_MYSQL_WITH_SSL -DDBD_MYSQL_INSERT_ID_IS_GOOD -g
  OBJECT = $(O_FILES)
  LDFROM = $(OBJECT)
  LINKTYPE = dynamic
--- 161,167 ----
  PARENT_NAME = DBD
  DLBASE = $(BASEEXT)
  VERSION_FROM = lib/DBD/mysql.pm
! INC = -I$(DBI_INSTARCH_DIR) -I/usr/mysql/connector/include -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv -fPIC  -fPIC -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DMY_PTHREAD_FASTMUTEX=1 -DDBD_MYSQL_WITH_SSL -DDBD_MYSQL_INSERT_ID_IS_GOOD -g
  OBJECT = $(O_FILES)
  LDFROM = $(OBJECT)
  LINKTYPE = dynamic
***************
*** 331,340 ****
  # DBD::mysql might depend on some other libraries:
  # See ExtUtils::Liblist for details
  #
! EXTRALIBS = -L/usr/lib64/mysql -lmysqlclient -lz -lssl -lcrypto
! LDLOADLIBS = -L/usr/lib64/mysql -lmysqlclient -lpthread -lz -lm -lssl -lcrypto -ldl
  BSLOADLIBS =
! LD_RUN_PATH = /usr/lib64/mysql:/lib64:/usr/lib64


  # --- MakeMaker const_cccmd section:
--- 331,340 ----
  # DBD::mysql might depend on some other libraries:
  # See ExtUtils::Liblist for details
  #
! EXTRALIBS = -L/usr/mysql/connector/lib -lmysqlconc -lz -lssl -lcrypto
! LDLOADLIBS = -L/usr/mysql/connector/lib -lmysqlconc -lpthread -lz -lm -lssl -lcrypto -ldl
  BSLOADLIBS =
! LD_RUN_PATH = /usr/mysql/connector/lib:/lib64:/usr/lib64


  # --- MakeMaker const_cccmd section:
$ make
$ sudo make install

gccに渡される-Iオプションをさっきインストールしたディレクトリ/includeに、-Lをさっきのディレクトリ/libに、-lmysqlclientを-lmysqlconcに書き換えます。
これで、MySQL Fabric(のHA機能だけだけど)に対応したBDB::mysqlができました。

サンプルコードはこんな感じになります。


$ vim test.pl
  1 #!/usr/bin/perl
  2
  3 use strict;
  4 use warnings;
  5 use DBI;
  6 use Data::Dumper;
  7
  8 my $count;
  9 my $conn= DBI->connect("dbi:mysql::127.0.0.1;port=32275;" .
 10                        "fabric_group=my_third_fabric;" .
 11                        "fabric_real_user=msandbox;" .
 12                        "fabric_real_password=msandbox;" .
 13                        "fabric_default_mode=ro", "admin", "xxxx") or die;
 14
 15 for (my $n= 1; $n <= 1000; $n++)
 16 {
 17   my $port= $conn->selectrow_arrayref("SELECT \@\@port")->[0];
 18   $count->{$port}++;
 19 }
 20
 21 print Dumper $count;
 22
 23 exit 0;

$ perl test.pl
Using Fabric for MYSQL connection
DBD::mysql::db selectrow_arrayref warning:  at test.pl line 17.
$VAR1 = {
          '20887' => 209,
          '20889' => 193,
          '20890' => 182,
          '20886' => 236,
          '20888' => 180
        };

DBI->connectのDSN部分、portとかmysql_socketとかをセミコロン区切りで書くあそこに、
* fabric_group= MySQL Fabricのグループ名
* fabric_real_user= MySQL Fabric経由でつながる実際のMySQLのユーザー名(アプリケーションユーザーになる)
* fabric_real_password= 同、パスワード。
* fabric_default_mode= "ro"(=全ノードで分散), "rw"(=マスターのみ)のいずれか。
を書きます。本来のユーザー, パスワード(↑で"admin", "xxxx"になってるところ)には、*MySQL Fabricのユーザー名とパスワード* (mysqlfabric manage setupした時に設定したやつ)を指定します。

"Using Fabric for MYSQL connection"が出てしまうのはConnector/C側にハードコードされてるから。そのうち直るとは思いますが、探せばさっくり消せる位置にあるので消してないのは単なる手抜きです(sql-common/client.c:CLI_MYSQL_REAL_CONNECT参照)

ともあれ、DSNに書き加えてやるだけでMySQL FabricのHA機能が使えるようになったはずです。試してみましょう。


mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

$ vim test2.pl
#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Data::Dumper;

my $conn= DBI->connect("dbi:mysql::127.0.0.1;port=32275;" .
                       "fabric_group=my_third_fabric;" .
                       "fabric_real_user=msandbox;" .
                       "fabric_real_password=msandbox;" .
                       "fabric_default_mode=rw", "admin", "xml",
                       {RaiseError => 0, PrintError => 0}) or die;
$conn->{mysql_auto_reconnect}= 1;

for (my $n= 1; $n <= 1000; $n++)
{
  eval
  {
    my $port= $conn->selectrow_arrayref("SELECT \@\@port")->[0];
    $conn->do("INSERT INTO d1.t1 SET val= ?", undef, $port);
  };

  if ($@)
    {next;}
  sleep 1;
}

exit 0;

1秒に1回ずつ、d1.t1に接続先(今回は"rw"に設定しているので、常にマスターになるはず)と時刻を書き込みます。


$ perl test2.pl
Using Fabric for MYSQL connection
DBD::mysql::db selectrow_arrayref warning:  at test2.pl line 20.

mysql> SELECT * FROM d1.t1;
+-----+-------+---------------------+
| num | val   | dt                  |
+-----+-------+---------------------+
|   1 | 20886 | 2014-12-04 19:58:49 |
|   2 | 20886 | 2014-12-04 19:58:50 |
|   3 | 20886 | 2014-12-04 19:58:51 |
+-----+-------+---------------------+
3 rows in set (0.00 sec)

$ kill -9 master-mysqld master-mysqld_safe

$ mysqlfabric group lookup_servers my_third_fabric
xPassword for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
163c889f-7ba3-11e4-ae68-fa163e020fd0 127.0.0.1:20886    FAULTY READ_WRITE    1.0
21482fb7-7ba3-11e4-ae68-fa163e020fd0 127.0.0.1:20887 SECONDARY  READ_ONLY    1.0
21e48205-7ba3-11e4-ae68-fa163e020fd0 127.0.0.1:20888 SECONDARY  READ_ONLY    1.0
22a941a2-7ba3-11e4-ae68-fa163e020fd0 127.0.0.1:20889 SECONDARY  READ_ONLY    1.0
2345a5b7-7ba3-11e4-ae68-fa163e020fd0 127.0.0.1:20890   PRIMARY READ_WRITE    1.0

切り替わりで20890ポート(=slave4)が新しいマスターになったっぽいです。


$ ./s4
mysql> SELECT * FROM d1.t1;
+-----+-------+---------------------+
| num | val   | dt                  |
+-----+-------+---------------------+
|   1 | 20886 | 2014-12-04 19:58:49 |
|   2 | 20886 | 2014-12-04 19:58:50 |
|   3 | 20886 | 2014-12-04 19:58:51 |
..
|  41 | 20886 | 2014-12-04 19:59:29 |
|  42 | 20886 | 2014-12-04 19:59:30 |
|  43 | 20886 | 2014-12-04 19:59:31 |
|  44 | 20890 | 2014-12-04 19:59:38 |
|  45 | 20890 | 2014-12-04 19:59:39 |
|  46 | 20890 | 2014-12-04 19:59:40 |
..
| 112 | 20890 | 2014-12-04 20:00:46 |
| 113 | 20890 | 2014-12-04 20:00:47 |
| 114 | 20890 | 2014-12-04 20:00:48 |
+-----+-------+---------------------+
114 rows in set (0.00 sec)

切り替わりました!! 今回は7秒くらいで切り替わってるんですねー。

ということで、MySQLの最新機能をPerlのDBD::mysqlで遊ぶ紹介でした。とっても簡単に遊べるので、みなさんも是非DBD::mysqlにパッチしてみてください。

Have fun!! :)

2014/12/03

MySQL Fabricつらい(mysql_select_dbがサポートされてない)

昨日パケットキャプチャを取って調べてたときに気付いた 、mysql_select_dbが効かない件をぴゃーっと雑に調べてみた。

sql-common/client.cのmysql_select_dbを見ると、

4493 int STDCALL
4494 mysql_select_db(MYSQL *mysql, const char *db)
4495 {
4496 int error;
4497 DBUG_ENTER("mysql_select_db");
4498 DBUG_PRINT("enter",("db: '%s'",db));
4499 DISABLE_FOR_FABRIC(1);
4500
4501 if ((error=simple_command(mysql,COM_INIT_DB, (const uchar*) db,
4502 (ulong) strlen(db),0)))
4503 DBUG_RETURN(error);
4504 my_free(mysql->db);
4505 mysql->db=my_strdup(key_memory_MYSQL,
4506 db,MYF(MY_WME));
4507 DBUG_RETURN(0);
4508 }

既に「いかにもサポートされてなさそうなふいんき(略)」
念のためDISABLE_FOR_FABRICを探してみると include/sql_common.hの中にあって、

242 /* Standard code to disable some features for fabric connections */
243
244 #define DISABLE_FOR_FABRIC(ERRVAL) \
245 if (mysql->fabric_context || mysql->options.use_fabric) \
246 { \
247 set_mysql_error(mysql, CR_NOT_SUPPORTED_WITH_FABRIC, unknown_sqlstate); \
248 DBUG_RETURN(ERRVAL); \
249 }

常にCR_NOT_SUPPORTED_WITH_FABRICが返ると。

確かに指向的にステートレスっぽいもんねMySQL Fabric。とはいえカレントデータベースが決め打てないってことは、replicate-do-dbとかしてると死ねるねぇ。。

過去、現在、未来、全宇宙に存在する全てのクソクエリーを、生まれる前に自分の手でカジュアルに消し去ること(仮)

この記事は MySQL Casual Advent Calendar 2014 の3日目の記事です。

クソクエリーについての名言 がつい先週生まれたばかりですが、みなさま如何お過ごしでしょうか。そういえば今年は Kuso-query As A Code みたいな話もさせてもらいました。

過去、現在、未来、全宇宙に存在する全てのクソクエリーを、生まれる前に自分の手でカジュアルに消し去るため(仮)に、MySQL 5.7.5-labsのQuery Rewrite Plugin の記事では触れるだけだったオレオレrewriteプラグインを書いてみました。君のクエリにレボ☆リューション! (仮)

どういう動作をさせるかというと、


mysql57> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql57> SELECT (1);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql57> SELECT ((1));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql57> SELECT (((1)));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Your query is f**king!!' at line 1

mysql57> SELECT ((1)), ((2));
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

mysql57> SELECT (((1)), ((2)));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Your query is f**king!!' at line 1

mysql57> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                   |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'SELECT (((1)), ((2)))' rewritten to 'Your query is f**king!!' by plugin: rewrite_test.                                                                             |
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Your query is f**king!!' at line 1 |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

シンプルな話、クソっぽいクエリー(今実装してあるのは、かっこが3つ以上ネストしているもの)が来た時に強制的にクエリーを上書きします。そしてこの文字列はSQLとして成立していないので、 *必ず* シンタックスエラーになります。
パーサーのレイヤーで書き換えているので、スローログとかもこの通り。


$ tail slow.log
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 0.000212  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use d1;
SET timestamp=1417408435;
SET SESSION long_query_time= 0;
# Time: 2014-12-01T04:33:56.545809Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 0.000088  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1417408436;
Your query is f**king!!;

これでもう2度と、派生テーブル同士を相関サブクエリーで結合したものをUNION DISTINCTで結合するとかいうクソクエリーに二度とお目にかかることはありません。もうちょっと真面目に書けば、テーブルを4つ以上JOINしてるとか、CREATE TABLEの中のflgxなんてカラム名に反応させてリライトさせることも可能です。

さて、ではこんなオレオレぷらぎんの作り方を解説します。こっちが本題だよ。
ビルドはt2.smallにamzn-ami-hvm-2014.09.1.x86_64-ebs (ami-b66ed3de)のAMIでやっています。t2.microだとメモリー食いきられてmakeできなかった。。URLは2014/12/01現在のものです。

まず、何はなくともlabs.mysql.comから"MySQL Optimizer/InnoDB/Replication" のソースコードを落としてこないとなんだけど、なんかどうも2ヶ月くらい前からファイルの終盤で"Connection reset by peer"を食らうようになっちゃってます。なんだろうこれ。wgetががんばってリトライしてくれるので、それほど困ってませんが。


$ wget http://downloads.mysql.com/snapshots/pb/mysql-5.7.5-labs-preview/mysql-5.7.5-labs-preview.tar.gz
$ tar xzf mysql-5.7.5-labs-preview.tar.gz
$ cd mysql-5.7.5-labs-preview
$ sudo yum install -y cmake gcc gcc-c++ ncurses-devel

Write Yourself a Query Rewrite Plugin: Part 1 | MySQL Server Blog から読み取るに、プラグインそのもののソースと"plug.in"ファイル、CMakeLists.txtを放り込んでやればいいらしい。


$ cd plugin/
$ ll rewrite_example
total 12
-rw-r--r-- 1 ec2-user ec2-user  835 Sep 24 05:06 CMakeLists.txt
-rw-r--r-- 1 ec2-user ec2-user  278 Sep 24 05:06 plug.in
-rw-r--r-- 1 ec2-user ec2-user 2697 Sep 24 05:06 rewrite_example.cc

$ cp -r rewrite_example mysql_casual

$ cd mysql_casual/

というわけで公式のrewrite_exampleをスケルトンにして実装していく。まずはCMakeLists.txtでこれは難しくない。i_sぷらぎんとかと同じで元になるソースの名前とモジュールの名前を決めてやるだけ。


$ vim CMakeLists.txt
..
MYSQL_ADD_PLUGIN(do_not_allow_kuso_query mysql_casual.cc
  MODULE_ONLY MODULE_OUTPUT_NAME "mysql_casual")

"plug.in"も同じ感じだった。ダイナミックリンクするときのファイル名と、スタティックリンクする時のファイル名を指定する? (i_sぷらぎんの時には書かなかったなこれ)


$ vim plug.in
MYSQL_PLUGIN(do_not_allow_kuso_query, [Be extinct all of kuso-query, before those are born.]),
                                      [Example query rewrite plugin by yoku0825.]
MYSQL_PLUGIN_DYNAMIC(do_not_allow_kuso_query, [mysql_casual.la])
MYSQL_PLUGIN_STATIC(do_not_allow_kuso_query, [mysql_casual.a])

で、核になるリライトぷらぎん本体。Query rewrite用にAPIが切ってあるので、それを呼ぶ感じで実装していく。やっぱりrewrite_example.ccをリネームしてそこを直してみるのが早い。


$ mv -i rewrite_example.cc mysql_casual.cc
$ vim mysql_casual.cc
..
static st_mysql_rewrite_pre_parse rewrite_example_descriptor= {
  MYSQL_REWRITE_PRE_PARSE_INTERFACE_VERSION,    /* interface version          */
  your_query_is_fxxking_dude,                   /* rewrite raw query function */
  free_rewritten_query,                         /* free allocated query       */
};..

先頭からQuery Rewrite用APIのバージョン, クエリーのリライトに使う実際の関数, 実行後にfreeするための関数。
先頭と最後はいじらなくていいので、真ん中だけそれっぽい名前にいじる(もちろん名前は変えなくてもいい)


$ vim mysql_casual.cc
..
mysql_declare_plugin(rewrite_example)
{
  MYSQL_REWRITE_PRE_PARSE_PLUGIN,
  &rewrite_example_descriptor,
  "do_not_allow_kuso_kuery",
  "yoku0825",
  "Making your f**king query to be syntax error :)",
  PLUGIN_LICENSE_GPL,
  rewrite_plugin_init,
  NULL,
  0x0001,                                       /* version 0.0.1      */
  NULL,                                         /* status variables   */
  NULL,                                         /* system variables   */
  NULL,                                         /* config options     */
  0,                                            /* flqgs              */
}
mysql_declare_plugin_end;

i_sぷらぎんとか書いているとおなじみの、mysql_declare_plugin構造体。説明文とかAUTHORをちょろっといじる。


$ vim mysql_casual.cc
..
static int your_query_is_fxxking_dude(Mysql_rewrite_pre_parse_param *param)
{
  unsigned depth= 0, max_depth= 0;

  for (unsigned i= 0; i < param->query_length; i++)
  {
    if (param->query[i] == '(')
    {
      depth++;
      if (depth > max_depth)
        max_depth= depth;
    }
    else if (param->query[i] == ')')
      depth--;
  }

  if (max_depth > 2)
  {
    param->rewritten_query= strdup("Your query is f**king!!");
    param->rewritten_query_length= strlen("Your query is f**king!!");
    param->flags|= FLAG_REWRITE_PLUGIN_QUERY_REWRITTEN;
  }
  else
  {
    param->rewritten_query= new char[param->query_length + 1];
    param->rewritten_query_length= param->query_length;
    strncpy(param->rewritten_query, param->query, param->query_length + 1);
  }

  return 0;
}
..

オリジナルではrewrite_lower関数になっているやつ(書き換えの本体)をいじくります。Mysql_rewrite_pre_parse_paramの構造体については include/mysql/plugin_query_rewrite.h に定義があるので、そこを見ればなんとなくわかるかと。param->queryにもともとのクエリーが入っていて、param->rewritten_queryに変換後のクエリーが入る感じですね。カッコのネストの深さを数えて、一定以上だったら書き換え、そうでなければそのまま通すような関数にしました。


$ cmake -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/tmp/my_boost
$ make
$ sudo make install

長いのでお茶でも淹れてきましょうかね。。


$ cd /usr/local/mysql
$ sudo useradd mysql
$ sudo ./bin/mysql_install_db --user=mysql --datadir=./data --basedir=./ --insecure
$ sudo chown -R mysql. /usr/local/mysql/data
$ sudo ./bin/mysqld_safe &
$ bin/mysql -uroot

5.7.5からmysql_install_dbはbinの下に移動になったんですよね。あと、basedirを与えないとエラーになって怒る。--insecureはデフォルトのランダムパスワードを設定しないという待望のオプションです。


mysql> INSTALL PLUGIN do_not_allow_kuso_query SONAME 'mysql_casual.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT (1);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT ((1));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT (((1)));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Your query is f**king!!' at line 1

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                   |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'SELECT (((1)))' rewritten to 'Your query is f**king!!' by plugin: do_not_allow_kuso_query.                                                                         |
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Your query is f**king!!' at line 1 |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

はい、出来上がりです! とてもカジュアルでしたね。明日からきっと雨後のたけのこのようににょきにょきとクエリーリライトぷらぎんが現れることでしょう。

寒い日が続くようですが、みなさまお風邪など召しませんように。

明日は @karupanerura さんです!

2014/12/02

MySQL Fabricつらい(FabricサーバーがMySQLプロトコルのポートでしゃべってる内容を見てみる)

tcpdump&Wiresharkでさくっと。
お手製MySQL Fabric対応mysqlコマンドラインクライアント からMySQL FabricのMySQLプロトコルをしゃべる口に接続して、ちょこちょことステートメントを叩いてみる。


$ client/mysql -P 32275 -uadmin -pxxxx --protocol=tcp -h 127.0.0.1 --fabric-group=my_second_fabric --fabric-real-user=msandbox --fabric-real-password=msandbox
mysql> \F
Current FABRIC_OPT_DEFAULT_MODE is ro

mysql> show tables;
ERROR 1046 (3D000): No database selected

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| tpcc               |
+--------------------+
5 rows in set (0.07 sec)

mysql> SELECT @@port;
+--------+
| @@port |
+--------+
|  14725 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT @@port;
+--------+
| @@port |
+--------+
|  14722 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT @@port;
+--------+
| @@port |
+--------+
|  14725 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT @@port;
+--------+
| @@port |
+--------+
|  14724 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT @@port;
+--------+
| @@port |
+--------+
|  14723 |
+--------+
1 row in set (0.02 sec)

mysql> SELECT @@port;
+--------+
| @@port |
+--------+
|  14724 |
+--------+
1 row in set (0.00 sec)

mysql> \F rw
FABRIC_OPT_DEFAULT_MODE sets to rw

mysql> create database if not exists d1;
Query OK, 1 row affected (0.01 sec)

こんなのをキャプチャしてみた結果が、コレ(クライアントとFabricの32275ポートをMySQLプロトコルとしてデコードさせてフィルターした)



ものの見事にログイン(グリーティング, 認証, 認証応答)とストアドプロシージャ(dump.fubric_nodes, group.lookup_groups, dump.servers) しか叩いてない。その後はRequist QUITまで沈黙。

ということは一度結果さえキャッシュしてしまえば、


$ mysqlfabric manage stop
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

Success (empty result set)


$ mysqlfabric group lookup_servers my_second_fabric
Password for admin:
[Errno 111] Connection refused

mysql> \F
Current FABRIC_OPT_DEFAULT_MODE is ro

mysql> SELECT @@port;
+--------+
| @@port |
+--------+
|  14724 |
+--------+
1 row in set (0.03 sec)

mysql> \F rw
FABRIC_OPT_DEFAULT_MODE sets to rw

mysql> SELECT @@port;
+--------+
| @@port |
+--------+
|  14723 |
+--------+
1 row in set (0.01 sec)

mysql> create table d1.t1 (num int, val varchar(32));
Query OK, 0 rows affected (0.19 sec)

$ client/mysql -P 32275 -uadmin -pxxxx --protocol=tcp -h 127.0.0.1 --fabric-group=my_second_fabric --fabric-real-user=msandbox --fabric-real-password=msandbox d1
mysql: [Warning] Using a password on the command line interface can be insecure.
Using Fabric for MYSQL connection
ERROR 2064 (HY000): Fabric error: Error connecting to Fabric host: Can't connect to MySQL server on '127.0.0.1' (111) (2003)

この通りクエリーは通る(もちろん、新しくコマンドラインクライアントを立ち上げようとしても32275が空いてないのでそれ以上の接続はできないけど)
TTLとかも関係してくる話なんだろうなぁ。。

余談だけど、このお手製クライアントからuse(つまり、mysql_select_db())を呼んでもことごとくエラーになることに気が付いた。MySQL Fabricの制約なのかConnector/Cがまだ対応してないのか、俺のパッチが何か考慮漏れてるのかはまだ不明。。


mysql> use d1
ERROR:

mysql>

取り敢えず、今日はこれまで。

2014/12/01

pt-query-digest --type tcpdumpに3306以外のポートのダンプを食わせる方法

ハマったのでメモ。

サンドボックスな環境でMySQLのポートを64055にしていたら、pt-query-digest --type tcpdumpでそのまま食ってくれなかった。

結論、--portとかじゃなく、--watch-serverで指定するんだった。


$ sudo tcpdump -i any -p /tmp/test.cap -c 100
$ tcpdump -r /tmp/test.cap -n -x -q -tttt | pt-query-digest --type tcpdump
reading from file /tmp/test.cap, link-type LINUX_SLL (Linux cooked)

# No events processed.

あっれぇ。。
取り敢えずまずはPTDEBUG=1にして様子を見てみる。


$ tcpdump -r /tmp/test.cap -n -x -q -tttt | PTDEBUG=1 pt-query-digest --type tcpdump
reading from file /tmp/test.cap, link-type LINUX_SLL (Linux cooked)
# /usr/bin/perl 5.010001
# Linux dev-personal-04 2.6.32-279.19.1.el6.x86_64 #1 SMP Wed Dec 19 07:05:20 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
# Arguments: _[--type]_ _[tcpdump]_
# OptionParser:1522 29673 Option rule: This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
..
# Pipeline:11539 29673 Pipeline restarting early after MySQLProtocolParser
# Pipeline:11527 29673 Pipeline process prep
# Pipeline:11527 29673 Pipeline process input
# Pipeline:11527 29673 Pipeline process TcpdumpParser
# TcpdumpParser:3658 29673 packet: $VAR1 = {
#   ack => 3530724024,
#   complete => 1,
#   data => '',
#   data_len => 0,
#   dgram_len => 40,
#   dst_host => '127.0.0.1',
#   dst_port => '42872',
#   fin => 1,
#   ip_hlen => 5,
#   rst => 0,
#   seq => 2831704995,
#   src_host => '127.0.0.1',
#   src_port => '64055',
#   syn => 0,
#   tcp_hlen => 5,
#   ts => '2014-12-01 16:40:11.599600'
# };
#
# Pipeline:11527 29673 Pipeline process MySQLProtocolParser
# MySQLProtocolParser:3916 29673 Packet is not to or from a MySQL server
# pt_query_digest:13442 29673 No more events, input EOF
..

どばー。
取り敢えず、MySQLProtocolParserの中で"Packet is not to or from a MySQL server"って言われてる。3916行目あたりにダイブ。


3894 my $src_host = "$packet->{src_host}:$packet->{src_port}";
3895 my $dst_host = "$packet->{dst_host}:$packet->{dst_port}";
..
3905 my $packet_from;
3906 my $client;
3907 if ( $src_host =~ m/:$self->{port}$/ ) {
3908 $packet_from = 'server';
3909 $client = $dst_host;
3910 }
3911 elsif ( $dst_host =~ m/:$self->{port}$/ ) {
3912 $packet_from = 'client';
3913 $client = $src_host;
3914 }
3915 else {
3916 PTDEBUG && _d('Packet is not to or from a MySQL server');
3917 return $self->{null_event};
3918 }

パケットの127.0.0.1:64055が$self->{port}を含んでいないのがいけないらしい。$self->{port}は何になってるかというと


$ tcpdump -r /tmp/test.cap -n -x -q -tttt | perl -d /usr/bin/pt-query-digest --type tcpdump
reading from file /tmp/test.cap, link-type LINUX_SLL (Linux cooked)

Loading DB routines from perl5db.pl version 1.32
Editor support available.

Enter h or `h h' for help, or `man perldebug' for more help.

main::(/usr/bin/pt-query-digest:65):
65:     package Percona::Toolkit;
  DB<1> c 3916
MySQLProtocolParser::parse_event(/usr/bin/pt-query-digest:3916):
3916:         PTDEBUG && _d('Packet is not to or from a MySQL server');
  DB<2> p Dumper $self
$VAR1 = bless( {
  fake_thread_id => '4294967296',
  null_event => undef,
..
  }, 'OptionParser' ),
  port => '3306',
  server => undef,
  sessions => {},
  version => '41'
}, 'MySQLProtocolParser' );

3306。オプションで--port 64055とかDSN形式でP=64055とか渡してやってもずっと3306。むぅ。デフォルトで3306になるのはMySQLProtocolParser::newでそうなってるからで、


3870 sub new {
3871 my ( $class, %args ) = @_;
3872
3873 my $self = {
3874 server => $args{server},
3875 port => $args{port} || '3306',
3876 version => '41', # MySQL proto version; not used yet
3877 sessions => {},
3878 o => $args{o},
3879 fake_thread_id => 2**32, # see _make_event()
3880 null_event => $args{null_event},
3881 };
3882 PTDEBUG && $self->{server} && _d('Watching only server', $self->{server});
3883 return bless $self, $class;
3884 }

これがどこから呼ばれてるかというとmainの中のここだった。


13393 my ($server, $port);
13394 if ( my $watch_server = $o->get('watch-server') ) {
13395 # This should match all combinations of HOST and PORT except
13396 # "host-name.port" because "host.mysql" could be either
13397 # host "host" and port "mysql" or just host "host.mysql"
13398 # (e.g. if someone added "127.1 host.mysql" to etc/hosts).
13399 # So host-name* requires a colon between it and a port.
13400 ($server, $port) = $watch_server
13401 =~ m/^((?:\d+\.\d+\.\d+\.\d+|[\w\.\-]+\w))(?:[\:\.](\S+))?/;
13402 PTDEBUG && _d('Watch server', $server, 'port', $port);
13403 }
13404
13405 foreach my $module ( @$type ) {
13406 my $parser;
13407 eval {
13408 $parser = $module->new(
13409 server => $server,
13410 port => $port,
13411 o => $o,
13412 );
13413 };

--portとかじゃなくて、--watch-serverから取ってたのか。。


$ tcpdump -r /tmp/test.cap -n -x -q -tttt | /usr/bin/pt-query-digest --watch-server 127.0.0.1.64055 --type tcpdump
reading from file /tmp/test.cap, link-type LINUX_SLL (Linux cooked)

# 410ms user time, 30ms system time, 25.84M rss, 212.79M vsz
# Current date: Mon Dec  1 17:44:12 2014
# Hostname: xxxx
# Files: STDIN
# Overall: 8 total, 6 unique, 0.35 QPS, 0.02x concurrency ________________
# Time range: 2014-12-01 16:39:48.680810 to 16:40:11.597656
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          399ms       0   150ms    50ms   148ms    48ms    65ms
# Rows affecte           3       0       1    0.38    0.99    0.48       0
# Query size           220      16      36   27.50   34.95    6.66   30.22
# Warning coun           0       0       0       0       0       0       0
..

無事食えた。確かにマニュアルにもフツーに書いてあった。。
http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html#cmdoption-pt-query-digest--watch-server