欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL中interactive_timeout和wait_timeout的區(qū)別

 更新時(shí)間：2016年10月20日 13:45:30   作者：iVictor  
這篇文章主要介紹了MySQL中interactive_timeout和wait_timeout的區(qū)別,非常不錯(cuò)具有參考借鑒價(jià)值，需要的朋友可以參考下

在用mysql客戶端對數(shù)據(jù)庫進(jìn)行操作時(shí)，打開終端窗口，如果一段時(shí)間沒有操作，再次操作時(shí)，常常會報(bào)如下錯(cuò)誤：

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)斷開，需要重新建立連接。

那么，連接的時(shí)長是如何確認(rèn)的？

其實(shí)，這個(gè)與interactive_timeout和wait_timeout的設(shè)置有關(guān)。

首先，看看官方文檔對于這兩個(gè)參數(shù)的定義

interactive_timeout

默認(rèn)是28800，單位秒，即8個(gè)小時(shí)

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

默認(rèn)同樣是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選項(xiàng)。

說得直白一點(diǎn)，通過mysql客戶端連接數(shù)據(jù)庫是交互式連接，通過jdbc連接數(shù)據(jù)庫是非交互式連接。

2> 在連接啟動的時(shí)候，根據(jù)連接的類型，來確認(rèn)會話變量wait_timeout的值是繼承于全局變量wait_timeout，還是interactive_timeout。

下面來測試一下，確認(rèn)如下問題

1. 控制連接最大空閑時(shí)長的是哪個(gè)參數(shù)。

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

Q1：控制連接最大空閑時(shí)長的是哪個(gè)參數(shù)

A1：wait_timeout

驗(yàn)證

只修改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的值。

驗(yàn)證：

只修改全局變量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)

開啟另外一個(gè)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));
}
}
}

結(jié)果輸出如下：

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)

開啟另外一個(gè)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的結(jié)果

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é)果

INTERACTIVE_TIMEOUT: 28800
WAIT_TIMEOUT: 20

同時(shí)，新增了一段程序，等待20s后，再次執(zhí)行查詢，報(bào)如下錯(cuò)誤：

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

總結(jié)

1. 控制連接最大空閑時(shí)長的wait_timeout參數(shù)。

2. 對于非交互式連接，類似于jdbc連接，wait_timeout的值繼承自服務(wù)器端全局變量wait_timeout。

對于交互式連接，類似于mysql客戶單連接，wait_timeout的值繼承自服務(wù)器端全局變量interactive_timeout。

3. 判斷一個(gè)連接的空閑時(shí)間，可通過show processlist輸出中Sleep狀態(tài)的時(shí)間

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ū)別，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時(shí)回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！

相關(guān)文章

  • 分享MySQL?主從延遲與讀寫分離的七種解決方案

    分享MySQL?主從延遲與讀寫分離的七種解決方案

    這篇文章主要介紹了分享MySQL?主從延遲與讀寫分離的七種解決方案，常見的解決方式是分庫分表，每次讀寫都是操作主庫的一個(gè)分表，從庫只用來做數(shù)據(jù)備份。當(dāng)主庫發(fā)生故障時(shí)，主從切換，保證集群的高可用性，下面詳細(xì)的相關(guān)資料介紹，需要的小伙伴可以參考一下
    2022-03-03
  • mysql聚簇索引的頁分裂原理實(shí)例分析

    mysql聚簇索引的頁分裂原理實(shí)例分析

    這篇文章主要介紹了mysql聚簇索引的頁分裂原理,結(jié)合實(shí)例形式分析了mysql聚簇索引的頁分裂原理與操作注意事項(xiàng),需要的朋友可以參考下
    2019-07-07
  • Mysql中日期和時(shí)間函數(shù)應(yīng)用不用求人

    Mysql中日期和時(shí)間函數(shù)應(yīng)用不用求人

    Mysql中日期和時(shí)間函數(shù)應(yīng)用不用求人,學(xué)習(xí)mysql的朋友可以參考下。
    2010-11-11

    • 最新評論