MySQL中interactive_timeout和wait_timeout的區別

在用mysql客戶端對資料庫進行操作時，打開終端視窗，如果一段時間沒有操作，再次操作時，常常會報如下錯誤：

ERROR 2013 (HY000): Lost connection to MySQL server during query

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...

這個報錯信息就意味著當前的連接已經斷開，需要重新建立連接。

那麼，連接的時長是如何確認的？

其實，這個與interactive_timeout和wait_timeout的設置有關。

首先，看看官方文檔對於這兩個參數的定義

interactive_timeout

預設是28800，單位秒，即8個小時

The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.

wait_timeout

預設同樣是28800s

The number of seconds the server waits for activity on a noninteractive connection before closing it.

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

根據上述定義，兩者的區別顯而易見

1> interactive_timeout針對互動式連接，wait_timeout針對非互動式連接。所謂的互動式連接，即在mysql_real_connect()函數中使用了CLIENT_INTERACTIVE選項。

     說得直白一點，通過mysql客戶端連接資料庫是互動式連接，通過jdbc連接資料庫是非互動式連接。 

2> 在連接啟動的時候，根據連接的類型，來確認會話變數wait_timeout的值是繼承於全局變數wait_timeout，還是interactive_timeout。

下麵來測試一下，確認如下問題

1. 控制連接最大空閑時長的是哪個參數。

2. 會話變數wait_timeout的繼承問題

Q1：控制連接最大空閑時長的是哪個參數

A1：wait_timeout

驗證

只修改wait_timeout參數

mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800          |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.03 sec)

mysql> set session WAIT_TIMEOUT=10;
Query OK, 0 rows affected (0.00 sec)
-------等待10s後再執行
mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
ERROR 2013 (HY000): Lost connection to MySQL server during query

可以看到，等待10s後再執行操作，連接已經斷開。

只修改interactive_timeout參數

mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800          |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.06 sec)

mysql> set session INTERACTIVE_TIMEOUT=10;
Query OK, 0 rows affected (0.00 sec) ----------等待10s後執行
mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 10             |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.06 sec)

Q2：會話變數wait_timeout的繼承問題

A2：如果是互動式連接，則繼承全局變數interactive_timeout的值，如果是非互動式連接，則繼承全局變數wait_timeout的值。