エラーメッセージ
5GBもの大規模なデータベース移行の最中や、1,000万行に及ぶ重いJOINクエリを実行している最中に、突然すべてが停止することがあります。データが表示される代わりに、次のような苛立たしいメッセージが表示されます。
ERROR 2013 (HY000): Lost connection to MySQL server during query
このエラーは、よくある「MySQL server has gone away」というメッセージとは異なります。後者は通常、最初に接続を試みたときに発生しますが、エラー2013はクエリがすでに実行されている最中に発生します。サーバーは処理を開始したものの、結果が画面に届く前にパイプラインが切断されてしまった状態です。
クエリ実行中に接続が切れる理由
この中断は、通常いくつかのボトルネックによって引き起こされます。その多くは、ネットワークやデータパケットのサイズに対してサーバーの待機時間が限界に達することに関係しています。
- ネットワークのボトルネック： サーバーが大きなデータチャンクを送信しようとしている間に、
net_read_timeoutまたはnet_write_timeoutの制限時間に達してしまう。 - パケットサイズ超過： クエリ結果や挿入しようとしている行が
max_allowed_packetの制限を超えている。 - ファイアウォールの干渉： 複雑なクエリの計算に10分かかり、その間最初の1バイトも送信されない場合、ネットワーク機器やソフトウェアが「アイドル状態」のTCP接続を強制終了することがあります。
- 物理的な不安定さ： 不安定なVPNや遅延の大きいWi-Fi接続により、TCPスタックがリセットされることがあります。
ステップバイステップの解決策
ステップ1：タイムアウト変数の値を増やす
最も頻繁な原因は、サーバーがクライアントをあまりに早く諦めてしまうことです。低速な接続を介して大量の結果セットを取得する場合、デフォルトの30秒という猶予は十分ではありません。これらのバッファを拡張する必要があります。
設定ファイル（通常、Linuxでは /etc/mysql/my.cnf、Windowsでは my.ini）を探し、[mysqld] セクションを見つけます。以下の値を追加または更新してください。
[mysqld]
# クライアントがデータを送信するのをより長く待機する (デフォルトは30秒)
net_read_timeout = 120
# クライアントがデータを受け取るのをより長く待機する (デフォルトは60秒)
net_write_timeout = 300
# より大きなデータパケットを許可する (BLOBや長い文字列に有効)
max_allowed_packet = 256M
# 長時間の操作のために接続を維持する
connect_timeout = 60
変更を保存したら、サービスを再起動して適用します。
# Linuxシステム
sudo systemctl restart mysql
# Dockerコンテナ
docker restart <container_name>
ステップ2：再起動せずに設定を変更する
再起動が許されない本番環境の場合は、MySQLターミナルで以下のコマンドを実行します。これらは即座に反映されますが、サーバーが再起動すると設定は消えてしまいます。
SET GLOBAL net_read_timeout = 120;
SET GLOBAL net_write_timeout = 300;
SET GLOBAL max_allowed_packet = 268435456; -- 256MBをバイト単位で指定
ステップ3：クライアントツールを確認する
サーバー側には問題がなくても、GUIツール自体に内部タイマーが設定されている場合があります。MySQL WorkbenchやDBeaverを使用している場合、ツール側がサーバーとの接続を切断している可能性があります。
- MySQL Workbench： Edit -> Preferences -> SQL Editor に移動します。DBMS connection read timeout interval を探し、600に設定されている場合は1200に倍増させてみてください。
- コマンドライン： 大規模なSQLダンプをインポートする場合は、コマンドで直接パケット制限を指定します。
mysql --max_allowed_packet=256M -u root -p my_database < backup.sql
### ステップ4：TCP接続を維持する
非常に長いクエリでは、数分間データが移動しないため、ファイアウォールが接続を終了したと判断することがあります。設定に `enable-tcp-keepalive` を追加することで、OSに接続を維持させることができます。これにより、小さな「ハートビート」パケットが送信され、ネットワークのタイムアウトを防ぎます。
## 変更内容を確認する方法
変更が反映されたかどうかを推測で判断しないでください。MySQLシェルにログインし、次のコマンドで現在アクティブな変数を確認します。
SHOW VARIABLES LIKE '%timeout%'; SHOW VARIABLES LIKE 'max_allowed_packet';
`net_write_timeout` が新しい値（例：300）を反映していることを確認してください。再度クエリを実行し、完了すればタイムアウト設定が原因だったことになります。
## 事前の最適化
タイムアウトを増やすことでエラーは止まりますが、それは遅いクエリに対する一時的な修正に過ぎないことが多いです。根本的な問題を解決するために、以下の戦略を試してください。
- **データの分割（チャンク化）：** 一度に1,000,000行をエクスポートするのではなく、`LIMIT 0, 50000` を使用して小さなバッチでデータを処理します。
- **EXPLAINの実行：** クエリの前に `EXPLAIN` を使用してください。「Full Table Scan」が表示される場合、インデックスを1つ追加するだけで実行時間を10分から10ミリ秒に短縮できる可能性があります。
- **SSHトンネルの使用：** リモートサーバーに接続している場合は、SSHトンネル経由でクエリを実行します。これにより、MySQLポートへの直接接続よりも安定した接続が確保できることがよくあります。