Last Updated on 2021年7月4日 by かんりにん
お客さんのシステムのMySQL DBサーバーにて、バックアップ用にmysqldumpのジョブをcronで実行していたところ
ある日を境に“Lost connection to MySQL server during query (2013)”というエラーが出るようになってしまった。
その後、ずっとエラー終了するようになってしまったので、原因を探っていたら、mysqlのタイムアウト周りの設定を見直すと良さそうだったので、いろいろと調べてみることに。
参考：お世話になっております！
MySQL 5.6 リファレンスマニュアル / 5.1.4 サーバーシステム変数
MySQL 4.1 リファレンスマニュアル / 6.1.5. システム変数
Q: MySQL Backup fails with mysqldump: Error 2013: Lost connection to MySQL server during query
Contents
▼timeoutの設定値を確認
まず”Lost connection to～”の原因と要因になっていそうな、システム変数のタイムアウト設定を見てみることに。
mysql> show variables like "%%timeout%%"; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | +----------------------------+----------+ 10 rows in set (0.00 sec)
と、こんな感じ。
このうち今回のmysqldumpのタイムアウトに関連しそうな”net_write_timeout”は
デフォルトでは60秒になっているので、この値を拡張してみる。
▼net_write_timeoutの設定変更
net_write_timeoutの設定はグローバル変数、セッション変数(ローカル変数)の2種類。
mysqlコンソールで設定する場合はそれぞれの変数を指定可能。
mysqlコマンドからの実行やmy.cnfでの設定の場合はグローバル変数での指定となる様子。
– mysqlコンソール上から変更する場合
こちらは上記のコマンドラインからの実行をコンソール上で適用する場合。
・グローバル変数で指定しした場合はログアウト後もプロセスが起動している間は有効
・セッション変数で指定した場合はコンソールログインをしている間だけ有効
という違いがあるので、作業時に把握しておく。またグローバル変数であっても、my.cnfで設定を指定していない限り、mysqldを再起動したらリセットされる。
1. グローバル変数として指定する場合
▼書式
mysql> set @@global.net_write_timeout=<seconds>;
または
mysql> set global net_write_timeout=<seconds>;
この場合はコンソールからquitした後も有効。
▼実行例と設定された状態の確認
– 設定状態を確認
mysql> show variables like "net_write_timeout"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | net_write_timeout | 60 | +-------------------+-------+ 1 row in set (0.00 sec)
– 設定値を変更
60秒→120秒へ変更。
mysql> set @@global.net_write_timeout=120; Query OK, 0 rows affected (0.00 sec)
– 指定した値が適用されているか確認
確認するときは、グローバル変数を指定する。
mysql> SELECT @@global.net_write_timeout; +----------------------------+ | @@global.net_write_timeout | +----------------------------+ | 120 | +----------------------------+ 1 row in set (0.00 sec)
– いったんmysqlコンソールを抜けた後、再度確認
mysql> SELECT @@global.net_write_timeout; +----------------------------+ | @@global.net_write_timeout | +----------------------------+ | 120 | +----------------------------+ 1 row in set (0.00 sec)
→OK！
ただし、セッション変数には反映されない。
mysql> SELECT @@session.net_write_timeout; +-----------------------------+ | @@session.net_write_timeout | +-----------------------------+ | 60 | +-----------------------------+ 1 row in set (0.00 sec)
これは、アクティブセッション上でグローバル変数を変更してもセッション変数には反映されないためで、ログアウト後に再度ログインしなおした段階にグローバル変数が反映される。
– 一度ログアウトして、再度mysqlコンソールにログインしてみると…
mysql> SELECT @@session.net_write_timeout; +-----------------------------+ | @@session.net_write_timeout | +-----------------------------+ | 120 | +-----------------------------+ 1 row in set (0.00 sec)
→反映されていた！
つまりmysqlサーバー全体のコントロールとしては有効だが、コンソールでの処理では変更が反映されない。
アクティブセッションでの設定値の変更をしたい場合は、セッション変数の変更が必要。
2. セッション変数として指定する場合
こちらはグローバル変数から値を変更したい場合に。
ログアウトすると設定はリセットされる。
▼書式
mysql> set @@session.net_write_timeout=<seconds>;
または
mysql> set @@local.net_write_timeout=<seconds>;
同様に、local変数でも同じセッション変数を変更可能。
mysql> set @@local.net_write_timeout=<seconds>;
または
mysql> set local net_write_timeout=<seconds>;
▼実行例と設定された状態の確認
– 設定値を変更
グローバル変数との比較のため180秒に指定してみる。
mysql> set @@session.net_write_timeout=180; Query OK, 0 rows affected (0.00 sec)
– 指定した値が適用されているか確認
mysql> SELECT @@session.net_write_timeout; +-----------------------------+ | @@session.net_write_timeout | +-----------------------------+ | 180 | +-----------------------------+ 1 row in set (0.00 sec)
– ただし、グローバル変数には反映されない。
mysql> SELECT @@global.net_write_timeout; +----------------------------+ | @@global.net_write_timeout | +----------------------------+ | 120 | +----------------------------+ 1 row in set (0.00 sec)
おまけ：local.net_read_timeoutとsession.net_read_timeoutは同じ変数。ログアウトするとリセットされる。
3. 変数を指定しない場合はどうなる？
この場合はセッション変数として適用される。
ためしに、”set @@net_write_timeout=;”として実行してみると…
– 変数の指定なしでSET文を実行
今度は240秒
mysql> set @@net_write_timeout=240; Query OK, 0 rows affected (0.00 sec)
– さらに変数の指定なしで設定値を確認してみると…
mysql> SELECT @@net_write_timeout; +---------------------+ | @@net_write_timeout | +---------------------+ | 240 | +---------------------+ 1 row in set (0.00 sec)
→適用されている。
– セッション変数を指定して確認してみると…
mysql> SELECT @@session.net_write_timeout; +-----------------------------+ | @@session.net_write_timeout | +-----------------------------+ | 240 | -----------------------------+ 1 row in set (0.00 sec)
→180秒に指定していセッション変数が、240秒に反映されている。
– セッション変数としてSETされたので、グローバル変数は当然変更なし。
mysql> SELECT @@global.net_write_timeout; +----------------------------+ | @@global.net_write_timeout | +----------------------------+ | 120 | +----------------------------+ 1 row in set (0.00 sec)
– コマンドラインから実行する場合
当然ながら、グローバル変数が変更される。
運用中にmysqlプロセスを停止せずに、すべてのスキーマに動的に適用する場合はこちら。
こちらもmysqldを再起動すると元に戻るものの、一時的な対処としてシェル上から変更したい場合は有効。
# mysql -u root -p -e "set global net_write_timeout=&amp;amp;lt;seconds&amp;amp;gt;;"
dumpのジョブをcronで実行する際に一時的に値を変更したい(と同時に、処理終了後に元に戻したい)場合はこちらが都合がよいかも。
▼実行例と設定された状態の確認
– グローバル変数
mysql> SELECT @@global.net_write_timeout; +---------------------------+ | @@global.net_write_timeout | +---------------------------+ | 180 | +---------------------------+ 1 row in set (0.00 sec)
– セッション変数
mysql> SELECT @@session.net_write_timeout; +----------------------------+ | @@session.net_write_timeout | +----------------------------+ | 180 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@local.net_write_timeout; +--------------------------+ | @@local.net_write_timeout | +--------------------------+ | 180 | +--------------------------+ 1 row in set (0.00 sec) mysql>
→すべての変数で同一の設定値が適用されている。
– my.cnfで値を指定する場合
mysqldセクションで設定を適用。恒久的に設定を適用する場合。
[mysqld] net_write_timeout = 180
– 設定値の確認はこちら。
mysql> SELECT @@global.net_write_timeout;
設定値を変える際の注意事項：
グローバル変数で設定値を変更したのに反映されていない？？
実は、今回タイムアウトの変数を試していてハマッたのはここ。
mysql> SELECT @@net_write_timeout; +---------------------+ | @@net_write_timeout | +---------------------+ | 60 | +---------------------+ 1 row in set (0.00 sec)
→これは、変数の種類を指定しないでSELECTを実行した場合は、セッション変数の値が出力されているため。
つまり“SELECT @@session.net_write_timeout”の実行結果が出力されているだけだった。
まとめ：
- net_write_timeoutの変数はグローバル変数とセッション変数の2種類。
- ログインしていない状態でタイムアウトの値を変更したい場合はグローバル変数を用いる。
- 設定値を変更した際は、変更した変数を指定して確認すること。
- セッション変数をSETしてしない状態では、グローバル変数の設定値が適用される。
- グローバル変数をSETした後でも、セッション変数はグローバル変数の変更前の値が継承される。セッション変数にグローバル変数が適用されるのは、次回のログインの時。
- セッション変数をSETした場合は、そちらが優先される(グローバル変数の上書きではなく、別パラメータとして優先的に適用)。ログアウトするとリセットされる。
- タイムアウトの値を恒久的に変更する場合はmy.cnfできちんと設定すること。ただしmysqldの再起動が必須になるので、サービス運用中のDBサーバーだと厳しいかな～
- 無駄にタイムアウトの値を伸ばすのは、特別な理由がない限り避けたほうがいいので、なんだかんだで作業の際の一時的な変更に留めておくのが、運用上は無難。
と、mysqldumpのタイムアウトの修正が目的だった割にセッション周りの設定について、当初の目的から脱線してしまったが、だいたい仕様と動きが把握できたので、これはこれでOKかな。
