MySQL中interactive_timeout和wait_timeout的區(qū)別
更新時間：2016年10月20日 13:45:30 作者：iVictor
這篇文章主要介紹了MySQL中interactive_timeout和wait_timeout的區(qū)別,非常不錯具有參考借鑒價值，需要的朋友可以參考下
在用mysql客戶端對數(shù)據(jù)庫進行操作時，打開終端窗口，如果一段時間沒有操作，再次操作時，常常會報如下錯誤：
ERROR 2013 (HY000): Lost connection to MySQL server during query ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect...
這個報錯信息就意味著當前的連接已經(jīng)斷開，需要重新建立連接。
那么，連接的時長是如何確認的？
其實，這個與interactive_timeout和wait_timeout的設置有關。
首先，看看官方文檔對于這兩個參數(shù)的定義
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.
根據(jù)上述定義，兩者的區(qū)別顯而易見
1> interactive_timeout針對交互式連接，wait_timeout針對非交互式連接。所謂的交互式連接，即在mysql_real_connect()函數(shù)中使用了CLIENT_INTERACTIVE選項。
說得直白一點，通過mysql客戶端連接數(shù)據(jù)庫是交互式連接，通過jdbc連接數(shù)據(jù)庫是非交互式連接。
2> 在連接啟動的時候，根據(jù)連接的類型，來確認會話變量wait_timeout的值是繼承于全局變量wait_timeout，還是interactive_timeout。
下面來測試一下，確認如下問題
1. 控制連接最大空閑時長的是哪個參數(shù)。
2. 會話變量wait_timeout的繼承問題
Q1：控制連接最大空閑時長的是哪個參數(shù)
A1：wait_timeout
驗證
只修改wait_timeout參數(shù)
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后再執(zhí)行
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后再執(zhí)行操作，連接已經(jīng)斷開。
只修改interactive_timeout參數(shù)
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后執(zhí)行
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的值。
驗證：
只修改全局變量interactive_timeout的值
mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800 |
| WAIT_TIMEOUT | 28800 |
+---------------------+----------------+
2 rows in set (0.13 sec)
mysql> set global INTERACTIVE_TIMEOUT=10;
Query OK, 0 rows affected (0.00 sec)
mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 10 |
| WAIT_TIMEOUT | 28800 |
+---------------------+----------------+
2 rows in set (0.00 sec)
開啟另外一個mysql客戶端，查看會話變量的值
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 | 10 |
+---------------------+----------------+
2 rows in set (0.00 sec)
發(fā)現(xiàn)，WAIT_TIMEOUT的值已經(jīng)變?yōu)?0了。
但通過jdbc測試，wait_timeout的值依舊是28800
public class Jdbc_test {
@SuppressWarnings("static-access")
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:mysql://192.168.244.10:3306/test";
String user = "root";
String password = "123456";
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
String sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out
.println(rs.getString(1)+": "+rs.getString(2));
}
}
}
結果輸出如下：
INTERACTIVE_TIMEOUT: 10
WAIT_TIMEOUT: 28800
只修改全局變量wait_timeout的值
mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wa
it_timeout');+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800 |
| WAIT_TIMEOUT | 28800 |
+---------------------+----------------+
2 rows in set (0.17 sec)
mysql> set global WAIT_TIMEOUT=20;
Query OK, 0 rows affected (0.07 sec)
mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wa
it_timeout');+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800 |
| WAIT_TIMEOUT | 20 |
+---------------------+----------------+
2 rows in set (0.00 sec)
開啟另外一個mysql客戶端，查看會話變量的值
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)
WAIT_TIMEOUT的值依舊是28800.
查看jdbc的結果
public class Jdbc_test {
@SuppressWarnings("static-access")
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:mysql://192.168.244.10:3306/test";
String user = "root";
String password = "123456";
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
String sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out
.println(rs.getString(1)+": "+rs.getString(2));
}
Thread.currentThread().sleep(21000);
sql = "select 1 from dual";
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out
.println(rs.getInt(1));
}
}
}
查看jdbc的結果
INTERACTIVE_TIMEOUT: 28800 WAIT_TIMEOUT: 20
同時，新增了一段程序，等待20s后，再次執(zhí)行查詢，報如下錯誤：
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 12 ms ago. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422) at com.victor_01.Jdbc_test.main(Jdbc_test.java:29) Caused by: java.net.SocketException: Software caused connection abort: recv failed at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.socketRead(Unknown Source) at java.net.SocketInputStream.read(Unknown Source) at java.net.SocketInputStream.read(Unknown Source) at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113) at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160) at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2452) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2906) ... 8 more
總結
1. 控制連接最大空閑時長的wait_timeout參數(shù)。
2. 對于非交互式連接，類似于jdbc連接，wait_timeout的值繼承自服務器端全局變量wait_timeout。
對于交互式連接，類似于mysql客戶單連接，wait_timeout的值繼承自服務器端全局變量interactive_timeout。
3. 判斷一個連接的空閑時間，可通過show processlist輸出中Sleep狀態(tài)的時間
mysql> show processlist; +----+------+----------------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+----------------------+------+---------+------+-------+------------------+ | 2 | root | localhost | NULL | Query | 0 | init | show processlist | | 6 | repl | 192.168.244.20:44641 | NULL | Sleep | 1154 | | NULL | +----+------+----------------------+------+---------+------+-------+------------------+ 2 rows in set (0.03 sec)
以上所述是小編給大家介紹的MySQL中interactive_timeout和wait_timeout的區(qū)別，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！
相關文章
MySQL關系型數(shù)據(jù)庫事務的ACID特性與實現(xiàn)
這篇文章主要介紹了MySQL關系型數(shù)據(jù)庫事務的ACID特性與實現(xiàn)，ACID?是為保證事務transaction是正確可靠的，具備原子性、一致性、隔離性、持久性等特性2022-07-07
下面小編就為大家?guī)硪黄獙ySql經(jīng)常使用語句的全面總結(必看篇)。小編覺的挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
MySQL使用innobackupex備份連接服務器失敗的解決方法
這篇文章主要為大家詳細介紹了MySQL使用innobackupex備份連接服務器失敗的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-02-02
這篇文章主要介紹了MySql連接不上問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-06-06
MySQL如何快速的創(chuàng)建千萬級測試數(shù)據(jù)
這篇文章主要給大家介紹了關于MySQL如何快速的創(chuàng)建千萬級測試數(shù)據(jù)的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-05-05
本文將手把手帶您了解mysql時間溢出原理、實戰(zhàn)影響與全面解決方案,所有代碼均通過dblens for mysql數(shù)據(jù)庫工具驗證,推薦使用該工具進行可視化數(shù)據(jù)庫管理和開發(fā),感興趣的小伙伴跟著小編一起來看看吧2025-03-03
詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化
這篇文章主要介紹了MySQL中的數(shù)據(jù)類型和schema優(yōu)化的相關資料，幫助大家更好的理解和學習MySQL的知識，感興趣的朋友可以了解下2020-10-10
MySQL表LEFT JOIN左連接與RIGHT JOIN右連接的實例教程
這篇文章主要介紹了MySQL表LEFT JOIN左連接與RIGHT JOIN右連接的實例教程,表連接操作是MySQL入門學習中的基礎知識,需要的朋友可以參考下2015-12-12
這篇文章主要介紹了大廠面試必問題之MySQL大文本如何進行處理的相關資料,面試者需要展示對MySQL索引機制的深入理解,并提出合適的索引類型和創(chuàng)建策略,需要的朋友可以參考下2025-01-01
MySQL DATEDIFF函數(shù)獲取兩個日期的時間間隔的方法
這篇文章主要介紹了MySQL DATEDIFF函數(shù)獲取兩個日期的時間間隔的方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-01-01