《MYSQL數(shù)據(jù)庫MySQL中interactive_timeout和wait_timeout的區(qū)別》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫MySQL中interactive_timeout和wait_timeout的區(qū)別，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。
在用mysql客戶端對(duì)數(shù)據(jù)庫進(jìn)行操作時(shí),打開終端窗口,如果一段時(shí)間沒有操作,再次操作時(shí),常常會(huì)報(bào)如下錯(cuò)誤：MYSQL學(xué)習(xí)
ERROR 2013 (HY000): Lost connection to MySQL server during query ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect...
這個(gè)報(bào)錯(cuò)信息就意味著當(dāng)前的連接已經(jīng)斷開,需要重新建立連接.MYSQL學(xué)習(xí)
那么,連接的時(shí)長(zhǎng)是如何確認(rèn)的?MYSQL學(xué)習(xí)
其實(shí),這個(gè)與interactive_timeout和wait_timeout的設(shè)置有關(guān).MYSQL學(xué)習(xí)
首先,看看官方文檔對(duì)于這兩個(gè)參數(shù)的定義MYSQL學(xué)習(xí)
interactive_timeoutMYSQL學(xué)習(xí)
默認(rèn)是28800,單位秒,即8個(gè)小時(shí)MYSQL學(xué)習(xí)
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_timeoutMYSQL學(xué)習(xí)
默認(rèn)同樣是28800sMYSQL學(xué)習(xí)
The number of seconds the server waits for activity on a noninteractive connection before closing it.MYSQL學(xué)習(xí)
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.
MYSQL學(xué)習(xí)
根據(jù)上述定義,兩者的區(qū)別顯而易見MYSQL學(xué)習(xí)
1> interactive_timeout針對(duì)交互式連接,wait_timeout針對(duì)非交互式連接.所謂的交互式連接,即在mysql_real_connect()函數(shù)中使用了CLIENT_INTERACTIVE選項(xiàng).MYSQL學(xué)習(xí)
說得直白一點(diǎn),通過mysql客戶端連接數(shù)據(jù)庫是交互式連接,通過jdbc連接數(shù)據(jù)庫是非交互式連接.MYSQL學(xué)習(xí)
2> 在連接啟動(dòng)的時(shí)候,根據(jù)連接的類型,來確認(rèn)會(huì)話變量wait_timeout的值是繼承于全局變量wait_timeout,還是interactive_timeout.MYSQL學(xué)習(xí)
下面來測(cè)試一下,確認(rèn)如下問題MYSQL學(xué)習(xí)
1. 控制連接最大空閑時(shí)長(zhǎng)的是哪個(gè)參數(shù).MYSQL學(xué)習(xí)
2. 會(huì)話變量wait_timeout的繼承問題MYSQL學(xué)習(xí)
Q1：控制連接最大空閑時(shí)長(zhǎng)的是哪個(gè)參數(shù)MYSQL學(xué)習(xí)
A1：wait_timeoutMYSQL學(xué)習(xí)
驗(yàn)證MYSQL學(xué)習(xí)
只修改wait_timeout參數(shù)MYSQL學(xué)習(xí)
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)斷開.MYSQL學(xué)習(xí)
只修改interactive_timeout參數(shù)MYSQL學(xué)習(xí)
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：會(huì)話變量wait_timeout的繼承問題MYSQL學(xué)習(xí)
A2：如果是交互式連接,則繼承全局變量interactive_timeout的值,如果是非交互式連接,則繼承全局變量wait_timeout的值.MYSQL學(xué)習(xí)
驗(yàn)證：MYSQL學(xué)習(xí)
只修改全局變量interactive_timeout的值
MYSQL學(xué)習(xí)
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)
開啟另外一個(gè)mysql客戶端,查看會(huì)話變量的值MYSQL學(xué)習(xí)
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了.MYSQL學(xué)習(xí)
但通過jdbc測(cè)試,wait_timeout的值依舊是28800MYSQL學(xué)習(xí)
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));
}
}
}
結(jié)果輸出如下：MYSQL學(xué)習(xí)
INTERACTIVE_TIMEOUT: 10
WAIT_TIMEOUT: 28800
MYSQL學(xué)習(xí)
只修改全局變量wait_timeout的值MYSQL學(xué)習(xí)
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)
開啟另外一個(gè)mysql客戶端,查看會(huì)話變量的值MYSQL學(xué)習(xí)
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.MYSQL學(xué)習(xí)
查看jdbc的結(jié)果MYSQL學(xué)習(xí)
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的結(jié)果MYSQL學(xué)習(xí)
INTERACTIVE_TIMEOUT: 28800 WAIT_TIMEOUT: 20
同時(shí),新增了一段程序,等待20s后,再次執(zhí)行查詢,報(bào)如下錯(cuò)誤：MYSQL學(xué)習(xí)
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureMYSQL學(xué)習(xí)
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
總結(jié)MYSQL學(xué)習(xí)
1. 控制連接最大空閑時(shí)長(zhǎng)的wait_timeout參數(shù).MYSQL學(xué)習(xí)
2. 對(duì)于非交互式連接,類似于jdbc連接,wait_timeout的值繼承自服務(wù)器端全局變量wait_timeout.MYSQL學(xué)習(xí)
對(duì)于交互式連接,類似于mysql客戶單連接,wait_timeout的值繼承自服務(wù)器端全局變量interactive_timeout.MYSQL學(xué)習(xí)
3. 判斷一個(gè)連接的空閑時(shí)間,可通過show processlist輸出中Sleep狀態(tài)的時(shí)間MYSQL學(xué)習(xí)
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ū)別,希望對(duì)大家有所贊助,如果大家有任何疑問請(qǐng)給我留言,小編會(huì)及時(shí)回復(fù)大家的.在此也非常感謝大家對(duì)維易PHP網(wǎng)站的支持!MYSQL學(xué)習(xí)
維易PHP培訓(xùn)學(xué)院每天發(fā)布《MYSQL數(shù)據(jù)庫MySQL中interactive_timeout和wait_timeout的區(qū)別》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。
轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/9843.html
