MySQLユーザのためのMySQLプロトコル入門#3

MySQLユーザのためのMySQLプロトコル入門#3

みなさん連休はどうでしたか?私はというものずーっと家に引きこもってcloudbitでsshアクセスできるようにしたりして遊んでいました。
今日の記事ではMySQL Serverに対してコマンドを発行して結果を取得していきます。

基本的にコマンドの実行の流れを最小で追ってみよう、という趣旨なので細かい分岐やエラーハンドリングなどは省いていますのであしからず。

Select Queryを投げてみよう

Queryの実行はMySQLサーバーに対してCOM_QUERYコマンドを送信すると結果が帰ってきます。

大まかなシーケンスとしてはdev.mysql.comに記載の通りとなっています。図でいうと一番左の分岐です。Select Queryを実行するだけですので、ProtocolText::Resultsetが帰ってくることが期待できそうですね。

簡単な図にするとこんな感じの順番でデータがやってきます。

Screen Shot 2014-11-06 at 4.28.13 PM

説明はほどほどにしておいて、まずはMySQLに適当なデータセットをつっこんでおきます。

ngrepでパケットを確認しつつ、テーブルの中身をしてみましょう。

selectを実行してみます

ngrepの結果は全て乗せると長くなってしまうので今回の興味がある部分だけ抜粋してみます。

そのままでもなんとなく読めちゃいそうな結果ですね。

COM_QUERY

それではまずはClientから投げるMySQL Packetを分解してみます。

先頭4byteはMySQL Packetのheaderなのでpayloadと分割すると下記のようになります。

dev.mysql.comのCOM_QUERYの解説によるとCOM_QUERYは
1byteの0x03が識別子で残りはstring[EOF]のqueryとなります。string[EOF]はpayloadの残り全部が文字列であるという意味になります。

com_query

それでは早速COM_QUERYのMySQL Packetをつくる関数を作ってみましょう。

このコードはPlayGroundで試せます、があまりにもシンプル過ぎるので特にいうことないですね。

それでは、引き続き結果セットをParseしていきましょう

COM_QUERY Response

COM_QUERYのResponseでは結果として取りうる値が数パターンあります。

MySQL Packetの1byte目によって下記の通りに分岐します。SELECTの場合はLength Encoded Integerの値が帰ります。

はて、Length Encoded Integerとは何でしょうか?

Length Encoded Integer

説明していませんでしたがMySQL protocolでの値表現はLittle Endianを使っています。
固定長の値表現だけでは時に効率的ではない事もありますので、効率性をあげたり、表現力を上げる為にMySQL ProtocolではLength Encoded Integerという表現がよく使われます。

Length Encoded integerの1から9byteに可変し、1byte目の値に応じでbyte, int8 int16, int24, int64の表現ができます。
ざっくり言うと250以下の値は1byteだけで表現でき、それ以上の場合は値の範囲に応じたサイズ分のデータがくっつく、というシンプルな構造です。

[1byte prefix]([n bytes if larger > 250])?

興味深い点としては1byte目をうまくerror packetに合わせてるところですね。それではlength encoded integer(lenenc int)を読み込む関数を作ってみましょう。

基本的にはprefix + 実データ形式となるのでそのままparseできます。GoのbinaryパッケージにはUint24というのはないので
そこだけ自前で読み込んでいますがparseは簡単ですね。

ついでに、文字列を表現するLength Encoded Stringというのもあるのでこの関数も作っときます。

見て分かる通りデータ部分のprefixにLength Encoded Integerが付いているだけです。

これらの関数はPlayGroundで確認できるようにしてありますのでいじってみると理解しやすいかと思います。

Length Encoded Integerの解説はこれくらいにしてクエリ結果の続きの解説です。

COM_QUERY Responseの続き

Select Queryの結果の場合はだいたいLength Encoded Integerでカラム数が帰ってくる、というところまで説明しました。
今度はカラム定義から結果セット部分をparseしていきます。

これを手で分割していくと下記の通りとなります。少し長いですが、カラム定義と結果セットの返却とEOFパケットの3パターンだけなのでたんたんといけます

それではカラム定義部分から解説していきましょう。

結果セットのカラム定義

カラム定義のパケットはColumnDefinitionといって、データ定義部分を引用すると:

とあるようにLength Encoded Stringでテーブル名やカラム名と型定義、そして型の詳細についてが入っています。

CharacterSetColumnTypeの定義はこのリンクを確認して下さい。

EOF Packetが来たらColumn Definition部分は終了です。続いて結果セットに行きましょう。

結果セット

結果セットのパケットはResultsetRowといい、結果セットのカラムの数だけLength Encoded Stringが帰ってきます。

今回、2つのカラムがあるというのがCOM_QUERYの最初のResponseで帰ってきているので1行には2つのカラムが入っています。
とりあえず1行だけ手でparseしてみましょう。

Length Encoded Stringがカラム数分連続しているだけなのでParseは単純ですね。文字列の1とpetrucciという値が送らてきていました。
ResultsetRowもColmunDefinitionと同様にEOF Packetがかえってくるまで繰り返して終了です。

通しでCOM_QUERYをプログラムから実行してみる

前回の記事のプログラムに今日の部分を追加してみたのがこんな感じです。
MySQL Packetは読み飛ばしやすいのでクライアントの実装にトライアンドエラーでやれると思います。

ちょっと雑ですがSelect Queryの実行から結果セットの取得までひと通り出来ましたね!

次回に向けて

この三回の連載でMySQLプロトコルの基礎的な部分は終わったのであとは興味があれば自分で実装ができると思います。

MySQLプロトコルを理解していればMySQLのネットワーク的な振る舞いが想像しやすくなりますし、色々なhackもできるので食わず嫌いせずに是非一度試してみてください。

次回のMySQLプロトコル入門の記事ではプロトコルから一旦離れBinlog Formatの解説に入ります。
それでは Happy Hacking

課題になりそうなのがあったら広く浅くジャンル問わず解決していくスタイル。最近もっぱらUnity使うので言語はC#メインですが以前はPHP, Go, Cをよく書いていたなんでもやさん。 利用ツール: Unity, Photoshop, ClipStudio, Blender, ZBrush, houdini, AfterEffects, FilterForge, SubstanceDesigner, Illustrator, Maya, Cubaseなどなど