MySQL中interactive_timeout和wait_timeout的差別。本站提示廣大學習愛好者：（MySQL中interactive_timeout和wait_timeout的差別）文章只能為提供參考，不一定能成為您想要的結果。以下是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的值。
驗證：
只修正全局變量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)
發明，WAIT_TIMEOUT的值曾經變成10了。
但經由過程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後，再次履行查詢，報以下毛病：
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參數。
2. 關於非交互式銜接，相似於jdbc銜接，wait_timeout的值繼續自辦事器端全局變量wait_timeout。
關於交互式銜接，相似於mysql客戶單銜接，wait_timeout的值繼續自辦事器端全局變量interactive_timeout。
3. 斷定一個銜接的余暇時光，可經由過程show processlist輸入中Sleep狀況的時光
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的差別，願望對年夜家有所贊助，假如年夜家有任何疑問請給我留言，小編會實時答復年夜家的。在此也異常感激年夜家對網站的支撐！
