關於MySQL的wait_timeout連線逾時問題報錯解決方案

標籤：報錯   變數   失效   move   max   tail   標準   before   串連數   

　bug回顧 ：

  想必大家在用MySQL時都會遇到連線逾時的問題，如所示：

### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 47,795,922 milliseconds ago.  The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of ‘wait_timeout‘. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property ‘autoReconnect=true‘ to avoid this problem.; SQL []; The last packet successfully received from the server was 47,795,922 milliseconds ago.  The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of ‘wait_timeout‘. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property ‘autoReconnect=true‘ to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 47,795,922 milliseconds ago.  The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of ‘wait_timeout‘. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property ‘autoReconnect=true‘ to avoid this problem.

 

 大概意思是當前的connection所進行過的最新請求是在52,587秒之前，這個時間是大於服務所配置的wait_timeout時間的。

 

原因分析：

MySQL串連時，伺服器預設的“wait_timeout”是8小時，也就是說一個connection空閑超過8個小時，Mysql將自動斷開該connection。connections如果空閑超過8小時，Mysql將其斷開，而DBCP串連池並不知道該connection已經失效，如果這時有Client請求connection，DBCP將該失效的Connection提供給Client，將會造成異常。 

 

mysql分析：

開啟MySQL的控制台，運行:show variables like ‘%timeout%’，查看和連線時間有關的MySQL系統變數，得到如下結果： 

    其中wait_timeout就是負責逾時控制的變數，其時間為長度為28800s，就是8個小時，那麼就是說MySQL的服務會在操作間隔8小時後斷開，需要再次重連。也有使用者在URL中使用jdbc.url=jdbc:mysql://localhost:3306/nd?autoReconnect=true來使得串連自動回復，當然了，這是可以的，不過是MySQL4及其以下版本適用。MySQL5中已經無效了，必須調整系統變數來控制了。MySQL5手冊中對兩個變數有如下的說明： 

    interactive_timeout：伺服器關閉互動式串連前等待活動的秒數。互動式用戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的用戶端。又見wait_timeout 
    wait_timeout:伺服器關閉非互動串連之前等待活動的秒數。線上程啟動時，根據全域wait_timeout值或全域interactive_timeout值初始化會話wait_timeout值，取決於用戶端類型(由mysql_real_connect()的串連選項CLIENT_INTERACTIVE定義)，又見interactive_timeout 
    如此看來，兩個變數是共同控制的，那麼都必須對他們進行修改了。繼續深入這兩個變數wait_timeout的取值範圍是1-2147483(Windows)，1-31536000(linux)，interactive_time取值隨wait_timeout變動，它們的預設值都是28800。 
    MySQL的系統變數由設定檔控制，當設定檔中不配置時，系統使用預設值，這個28800就是預設值。要修改就只能在設定檔裡修改。Windows下在%MySQL HOME%/bin下有mysql.ini設定檔，開啟後在如下位置添加兩個變數，賦值。（這裡修改為388000） 

 

解決方式：

1. 增加 MySQL 的 wait_timeout 屬性的值 （不推薦）

修改mysql安裝目錄下的設定檔 my.ini檔案（如果沒有此檔案，複製“my-default.ini”檔案，產生“複件 my-default.ini”檔案。將“複件 my-default.ini”檔案重新命名成“my.ini” ），在檔案中設定：

wait_timeout=31536000  interactive_timeout=31536000  

這兩個參數的預設值是8小時(60*60*8=28800)。 注意： 1.wait_timeout的最大值只允許2147483 （24天左右）

也可以使用mysql命令對這兩個屬性進行修改

 

 

2. 減少串連池內串連的生存周期

減少串連池內串連的生存周期，使之小於上一項中所設定的wait_timeout 的值。 

修改 c3p0 的設定檔，在 Spring 的設定檔中設定：

 <bean id="dataSource"  class="com.mchange.v2.c3p0.ComboPooledDataSource">          <property name="maxIdleTime"value="1800"/>      <!--other properties -->   </bean>

 

3. 定期使用串連池內的串連定期使用串連池內的串連，使得它們不會因為閑置逾時而被 MySQL 斷開。 修改 c3p0 的設定檔,在 Spring 的設定檔中設定：

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">      <property name="preferredTestQuery" value="SELECT 1"/>      <property name="idleConnectionTestPeriod" value="18000"/>      <property name="testConnectionOnCheckout" value="true"/>  </bean>

 

附上dbcp和c3p0的標準配置

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://192.168.40.10:3336/XXX" /> <property name="username" value="" /> <property name="password" value="" /> <property name="maxWait" value="20000"></property> <property name="validationQuery" value="SELECT 1"></property> <property name="testWhileIdle" value="true"></property> <property name="testOnBorrow" value="true"></property> <property name="timeBetweenEvictionRunsMillis" value="3600000"></property> <property name="numTestsPerEvictionRun" value="50"></property> <property name="minEvictableIdleTimeMillis" value="120000"></property> <property name="removeAbandoned" value="true"/> <property name="removeAbandonedTimeout" value="6000000"/></bean>

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">   　　<property name="driverClass"><value>oracle.jdbc.driver.OracleDriver</value></property>   　　<property name="jdbcUrl"><value>jdbc:oracle:thin:@localhost:1521:Test</value></property>   　　<property name="user"><value>Kay</value></property>   　　<property name="password"><value>root</value></property>   　　<!--串連池中保留的最小串連數。-->   　　<property name="minPoolSize" value="10" />   　　<!--串連池中保留的最大串連數。Default: 15 -->   　　<property name="maxPoolSize" value="100" />   　　<!--最大空閑時間,1800秒內未使用則串連被丟棄。若為0則永不丟棄。Default: 0 -->   　　<property name="maxIdleTime" value="1800" />   　　<!--當串連池中的串連耗盡的時候c3p0一次同時擷取的串連數。Default: 3 -->   　　<property name="acquireIncrement" value="3" />   　　<property name="maxStatements" value="1000" />   　　<property name="initialPoolSize" value="10" />   　　<!--每60秒檢查所有串連池中的空閑串連。Default: 0 -->   　　<property name="idleConnectionTestPeriod" value="60" />   　　<!--定義在從資料庫擷取新串連失敗後重複嘗試的次數。Default: 30 -->   　　<property name="acquireRetryAttempts" value="30" />   　　<property name="breakAfterAcquireFailure" value="true" />   　　<property name="testConnectionOnCheckout" value="false" />   </bean>   

 

 

參考：

https://my.oschina.net/guanzhenxing/blog/213364

http://sarin.iteye.com/blog/580311/

http://blog.csdn.net/wangfayinn/article/details/24623575

關於MySQL的wait_timeout連線逾時問題報錯解決方案