こんにちは、ヌーラボの中村です。BacklogのGitチームで開発やメンテナンス、その他諸々をやっています。本記事ではMySQLクライアントのバージョンアップの際に出くわしたおもしろい挙動を解説します。
目次
概要
MySQL 5.7サーバーへの接続について、MySQL5系クライアントからMySQL8系クライアントへのバージョンアップを検証していたところ、特定のクエリだけ実行結果が0件になる現象が発生しました。（あるPerlのプログラムで5系のクライアントをサポートしていない環境があり、やむを得ずアップグレードを実施しました）
インターネットで検索すると、「クエリに空白を入れる」「改行を追加する」などおまじないのような解決方法が散見されましたが、そんなはずはないと思い調査を進めました。
調査したところ、原因は以下の3つが合わさったことによるものでした。
- MySQL 5.7ではクエリキャッシュが有効で、MySQL8.0では無効であること
- MySQL 5.7.5からEOF パケットが非推奨になり、レスポンスのパケット形式が変更されたこと
- クエリキャッシュが有効なとき、同じパケットが返却されること
この問題はキャッシュを無効にするか、クライアントの想定するパケットの形式を指定することで解消できます。
再現手順
手軽にバージョンを変えて実行するために、シンプルな再現コードをJavaで作成しました。
以下の手順でMySQLサーバーをDockerで用意し、テストコードをgradleで実行することで簡単に再現できます。
環境
- DB: MySQL 5.7.42 (DockerHubでの5系の最新バージョン)
- クライアント: MySQL5.1 connector (5.1.38以前のもの), MySQL8.0 connector (最新)
DBの起動とテストデータの用意
docker run --rm --name mysql-test -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=sa -e MYSQL_DATABASE=test mysql:5.7.42 --query_cache_type=1 docker exec -it mysql-test mysql -h 127.0.0.1 -psa -e "CREATE TABLE IF NOT EXISTS test.table1 (id INT, name VARCHAR(10));INSERT INTO test.table1 VALUES (1, 'hoge'), (2, 'fuga')"
テストコード
Java用のgradleプロジェクトを作成し、以下のファイルを追加します。
src/main/java/org/example/Example.java
package org.example;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Properties;
public class Example {
// SELECT * FROM table1 WHERE id=2; を実行する。
// 実行時引数があるときはMySQL8系ドライバを、ないときは5系のドライバを使用する。
public static void main(String[] args) throws SQLException {
Driver driver;
if (args.length > 0) {
driver = new com.mysql.cj.jdbc.Driver();
} else {
driver = new com.mysql.jdbc.Driver();
}
executeQuery(driver);
}
static void executeQuery(Driver driver) throws SQLException {
var info = new Properties();
info.put("user", "root");
info.put("password", "sa");
try (
var con = driver.connect("jdbc:mysql://localhost:3306/test?useSSL=false", info);
var stmt = con.createStatement()
) {
System.out.println("Connect with: " + con.getMetaData().getDriverVersion());
stmt.execute("SET NAMES 'utf8'");
var rs = stmt.executeQuery("SELECT * FROM table1 WHERE id=2;");
int size = 0;
while (rs.next()) {
size++;
System.out.println(Arrays.asList(rs.getInt("id"), rs.getString("name")));
}
System.out.println("Result size is " + size);
}
}
}
build.gradle
plugins {
id 'java'
}
group 'org.example'
version '1.0-SNAPSHOT'
repositories {
mavenCentral()
}
dependencies {
implementation 'mysql:mysql-connector-java:5.1.38'
implementation 'com.mysql:mysql-connector-j:8.0.33'
}
task example(type: JavaExec) {
main = "org.example.Example"
classpath = sourceSets.main.runtimeClasspath
}
実行手順
MySQL5系クライアント→8系クライアントの順に接続するテストの実行手順を示します。
# MySQL5系クライアントで実行 → データが取得できる $ ./gradlew -q example Connect with: mysql-connector-java-5.1.38 ( Revision: fe541c166cec739c74cc727c5da96c1028b4834a ) [2, fuga] Result size is 1 # MySQL8系クライアントで実行 → 例外が発生する $ ./gradlew -q example --args 80 Connect with: mysql-connector-j-8.0.33 (Revision: 7d6b0800528b6b25c68b52dc10d6c1c8429c100c) Exception in thread "main" java.sql.SQLException: Index 5 out of bounds for length 5 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) at org.example.Example.executeQuery(Example.java:29) at org.example.Example.main(Example.java:16) ……
ケースを変える前に、いちどクエリキャッシュをクリアします。
docker exec -it mysql-test mysql -h 127.0.0.1 -psa -e "RESET QUERY CACHE;"
MySQL8系クライアント→5系クライアントの順に接続するテストの実行手順を示します。
# MySQL8系クライアントで実行 → データが取得できる $ ./gradlew -q example --args 80 Connect with: mysql-connector-j-8.0.33 (Revision: 7d6b0800528b6b25c68b52dc10d6c1c8429c100c) [2, fuga] Result size is 1 # MySQL5系クライアントで実行 → データが0件になる $ ./gradlew -q example Connect with: mysql-connector-java-5.1.38 ( Revision: fe541c166cec739c74cc727c5da96c1028b4834a ) Result size is 0
テストを実行する順序によって結果が変わります。
実行結果のまとめ
- 「MySQL5系クライアント」→「MySQL8系クライアント」のとき、例外が発生する
- 「MySQL8系クライアント」→「MySQL5系クライアント」のとき、取得件数が0件になる
レコードを1件取得するだけの、同じSQL文を投げているだけなのに結果が変わってしまいました。
次の章で、このとき何が起こっているのかを見ていきます。
何が起こっていたのか
パケットを見る
Wiresharkで実際にどんなパケットが流れているのか確認しました。レコードを1件取得するSELECT文のレスポンスのパケットを示します。
まずは「MySQL5系クライアント」→「MySQL8系クライアント」の順で接続しクエリを実行したときのパケットです。
MySQL5系クライアント
MySQL8系クライアント
クライアントのバージョンを変えているのに、データ部が全く同じになっていることがわかります。また、Wiresharkには警告が表示されています。
続いて、「MySQL8系クライアント」→「MySQL5系クライアント」の順で接続しクエリを実行したときのパケットも示します。
MySQL8系クライアント
MySQL5系クライアント
こちらではレスポンスが少し短くなっていますが、データ部が全く同じになっていることがわかります。また、Wiresharkにエラーが表示されています。
これらの結果から以下の事がわかります。
- クエリキャッシュが効いているとき、クライアントを変えてもレスポンスのデータ部はバイトレベルで全く同じ
- 5系クライアントから始めたときと8系クライアントから始めたときとでデータ部は異なる
- クライアントのバージョンとレスポンスのデータ形式がズレたとき、WiresharkのDissector（プロトコルのデータ部のパーサー）で警告やエラーが発生する
ここではWiresharkのDissectorでエラーが発生していますが、テストコードや検証時のプログラムでも、想定していないデータを受け取ったことによる例外や変な挙動を引き起こしていたと考えられます。
他の言語でも同様の検証をしましたが、言語やライブラリによってはエラーの発生の仕方が異なったり、またはエラーが発生しなかったりしました。この挙動は実装依存のようです。
異なっていた場所
データ部が全く同じなら一体どこが違うのか？というのも見てみました。結論から言うとLogin Request時のパケットが異なるようです。
MySQL5系クライアントのLogin Request
MySQL8系クライアントのLogin Request
これらを見比べると、「Client Capabilities」と「Extended Client Capabilities」の値が異なっています。それぞれの内容を見比べたところ、「Deprecated EOF」に差分があり、これがこの挙動の違いに関わっていると思われます。
MySQL5系クライアントのときの「Extended Client Capabilities」
MySQL8系クライアントのときの「Extended Client Capabilities」
MySQLのレスポンス形式
MySQLは5.7.5からレスポンスの形式が変わりました。従来のEOF Packetが使用されなくなり、代わりにOK Packetが使用されるようになりました。これによりfield packetとrow packetの区切りのEOF Packetがなくなったり、最後のパケットがEOF PacketからOK Packetになっていたりといった違いがあります。また、設定の切り替えは上記の差分にもあった Capability フラグのCLIENT_DEPRECATE_EOFで制御されます
これらの件については公式ドキュメントの「EOF_Packet」と「OK_Packet」、そして「Capabilities Flags」の項目に記載がありました。
対処法
いくつかの言語のMySQLのドライバーを見たところ、簡単にCLIENT_DEPRECATE_EOFを制御できるものは見当たりませんでした。素直にMySQLサーバーの設定でクエリキャッシュをオフにしておくか、もしくはクライアントプログラム側でクエリキャッシュを無効化しておくのが無難でしょう。
まとめ
MySQLサーバーの5.7.5以降はクエリキャッシュを有効にでき、かつCLIENT_DEPRECATE_EOFを解釈できるため、CLIENT_DEPRECATE_EOFの値が異なるクライアントから並行してアクセスしたとき、想定していない形式のレスポンスを受け取ってしまうことがあることがわかりました。これにより、クライアントの実装によっては思わぬ挙動をしてしまうようです。
バージョンの混在によって発生する問題はいつも難しくやっかいなものですが、再現ができると調査の第一歩となり理解を深めやすくなります。今回はシンプルなテストコードで再現できたため、何度もやり直して詳しく確認することができました。MySQLの返すパケットや、Capabilityフラグなどをよく見たのは初めてだったため、興味深く勉強になりました。
参考情報
現在弊社では各種ポジション絶賛採用中です。少しでも興味が湧いた方はぜひ下記採用ページをご覧ください。
