【轉】MySQL連線逾時斷開的問題

標籤：bcp   attr   repo   bat   error   eid   arraylist   something   auto   

 

這遍文章轉過來做個筆記，時不時看看。

轉:http://blog.csdn.net/nethibernate/article/details/6658855

Exception如下：

 

[html] view plaincopy  

1. org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08S01  
2. org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was43200 milliseconds ago.The last packet sent successfully to the server was 43200 milliseconds ago, which 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 ‘autoReconnect=true‘ to avoid this problem.  
3. org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session  
4. org.hibernate.exception.JDBCConnectionException: Could not execute JDBC batch update  
5. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.  
6. org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08003  
7. org.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed. Connection was implicitly closed due to underlying exception/error:  
8.   
9. ** BEGIN NESTED EXCEPTION **  
10. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException  

先說一下發生這個Exception的大致原因：

 

MySQL的配置中，有一個叫做“wait_timeout"的參數，這個參數大致的意思是這樣：當一個用戶端串連到MySQL資料庫後，如果用戶端不自己斷開，也不做任何操作，MySQL資料庫會將這個串連保留"wait_timeout"這麼長時間（單位是s，預設是28800s，也就是8小時），超過這個時間之後，MySQL資料庫為了節省資源，就會在資料庫端斷開這個串連；當然，在此過程中，如果用戶端在這個串連上有任意的操作，MySQL資料庫都會重新開始計算這個時間。

這麼看來，發生上面Exception的原因就是因為我的伺服器和MySQL資料庫的串連超過了”wait_timeout"時間，MySQL伺服器端將其斷開了，但是我的程式再次使用這個串連時沒有做任何判斷，所以就掛了。

那這個問題怎麼解決呢？

在想解決方案的過程中，我發現了幾個讓我不著頭緒的問題：

第一個問題：我們的伺服器曾經在設計的過程中考慮過這個事情，所以伺服器的主線程有一個定時的check機制，每隔半小時會發送一個"select 1"到資料庫來保證串連是活動的，為什麼這個check機制不起作用了呢？

第二個問題：從上面的Exception中可以得到這麼一個資訊：

[java] view plaincopy  

1. The last packet sent successfully to the server was 43200 milliseconds ago, which is longer than the server configured value of ‘wait_timeout‘.  

這個資訊說的很明白，最後一個成功發到Server的包是43200毫秒之前。但是43200毫秒才43.2秒，也就是說我們的伺服器43.2秒之前才和MySQL伺服器通過信，怎麼會發生超過”wait_timeout“的問題呢？而且MySQL資料庫的配置也確實是28800秒（8小時），這又是神馬情況呢？

 

在網上google了n長時間，倒是有不少關於這個問題的討論，但是一直沒有找到讓我覺得有效方法，只能自己結合google到結果來慢慢琢磨了。

首先，MySQL資料庫那邊的解決方案很單一，就是延長”wait_timeout“的數值。我看有的人直接就延長到一年了，也有人說這個值最大也就是21天，即使值設的再大，MySQL也就只識別21天（這個我沒有具體去MySQL的文檔中去查）。但是這是一個治標不治本的方法，即使可以一年，也還是會有斷的時候，伺服器可是要7x24小時線上的呀。

既然MySQL資料庫那邊沒什麼好方法，接下來就只能從程式這邊來搞了。

先說說程式這邊的大致結構吧：兩個線程，一個線程負責查詢和上面說到的check機制，另一個線程負責定時更新資料庫，使用hibernate，配置很簡單，都是最基本的，沒有任何關於串連池和緩衝的配置，就像下面這樣：

[html] view plaincopy  

1. <session-factory>  
2.     <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>  
3.     <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>  
4.     <property name="hibernate.connection.useUnicode">true</property>  
5.     <property name="hibernate.connection.characterEncoding">UTF-8</property>  
6.     <property name="hibernate.show_sql">true</property>  
7.     <!-- 以下就全是mapping了，省略 -->  
8. </session-factory>  

程式中更新的過程大致是這樣： [java] view plaincopy  

1. session = org.hibernate.SessionFactory.openSession();  
2. transaction = session.beginTransaction();  
3. session.update(something);  
4. transaction.commit();  
5. session.close();  

在這裡，所有關於資料庫Connection的串連和關閉都在Hibernate中，因此，不去挖掘Hibernate的源碼是不可能了。

 

在挖掘Hibernate源碼之前，必須明確目標：挖掘什嗎？

其實我的目標很明確，既然中斷連線是MySQL資料庫做的，那麼相對於我們程式這邊的問題就是我們在使用完串連之後沒有調用Connection.close()，才會保留一個長串連在那裡。那麼，Hibernate是什麼時候開啟這個串連，又什麼時候調用Connection.close()的呢？

接下來就是Hibernate的源碼挖掘中。。。

枯燥的過程就不說了，說說挖掘出的東西：

Hibernate（忘了說了，我們用的Hibernate版本是3.3.2）在上面的那種配置之下，會有一個預設的串連池，名字叫：DriverManagerConnectionProvider；這是一個極其簡單的串連池，預設會在池中保留20個串連，這些串連不是一開始Hibernate初始化時就建立好的，而是在你需要使用串連時建立出來，使用完之後才加入到池中的。這裡有一個叫closeConnection(Connection conn)的方法，這個方法很NB，它直接將傳入的串連不做任何處理，放到池中。而這個類內部的串連池實際是一個ArrayList，每次取得時候remove掉ArrayList的第一個串連，用完後直接用add方法加入到ArrayList的最後。

我們的程式更新時，Hibernate會通過DriverManagerConnectionProvider得到一個串連Connection，在使用完之後，調用session.close()時，Hibernate會調用DriverManagerConnectionProvider的closeConnection方法（就是上面說的那個NB方法），這個時候，該串連會直接放到DriverManagerConnectionProvider的ArrayList中，從始至終也沒有地方去調用Connection的close方法。

說到這裡，問題就很明顯了。

第一，我們的那個”select 1“的check機制和我們伺服器程式中更新的邏輯是兩個線程，check機制工作時，它會向DriverManagerConnectionProvider擷取一個串連，而此時更新邏輯工作時，它會向DriverManagerConnectionProvider擷取另外一個串連，兩個邏輯工作完之後都會將自己獲得的串連放回DriverManagerConnectionProvider的池中，而且是放到那個池的末尾。這樣，check機制再想check這兩個串連就需要運氣了，因為更新邏輯更新完之後就把串連放回池中了，而更新邏輯是定時的，check機制也是定時的，兩個定時機制如果總是能錯開，那麼check機制check的永遠都是兩個中的一個串連，另外一個就麻煩了。這也就是為什麼check機制不好使的原因。

第二，關於Exception資訊中那個43200毫秒的問題也就能說明白了，check機制check的總是一個串連，而另外一個到期的串連被更新線程拿跑了，並且在check機制之後沒多久就有更新發生，43200毫秒恐怕就是它們之間的間隔吧。

到這裡問題分析清楚了，怎麼解決呢？

最容易想到的方案，也是網上說的最多的方案，就是延長MySQL端”wait_timeout“的時間。我說了，治標不治本，我覺得不爽，不用。

第二個看到最多的就是用”autoReconnect = true"這個方案，鬱悶的是MySQL 5之後的資料庫把這個功能給去了，說會有副作用（也沒具體說有啥副作用，我也懶得查），我們用的Hibernate 3.3.2這個版本也沒有autoReconnect這個功能了。

第三個說的最多的就是使用c3p0池了，況且Hibernate官網的文檔中也提到，預設的那個串連池非常的屎，僅供測試使用，推薦使用c3p0（讓我鬱悶的是我連c3p0的官網都沒找到，只在sourceForge上有個項目首頁）。好吧，我就決定用c3p0來搞定這個問題。

用c3p0解決這個Exception問題：（點擊這裡進入我參考的部落格，要FQ哦，親！）

首先很明了，只要是池它就肯定有這個問題，除非在放入池之前就把串連關閉，那池還頂個屁用。所以我參考的部落格裡說到，最好的方式就是在擷取串連時check一下，看看該串連是否還有效，即該Connection是否已經被MySQL資料庫那邊給關了，如果關了就重連一個。因此，按照這個思路，我修正了Hibernate的設定檔，問題得到瞭解決：

 

[html] view plaincopy  

1. <session-factory>  
2.     <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>  
3.     <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>  
4.     <property name="hibernate.connection.useUnicode">true</property>  
5.     <property name="hibernate.connection.characterEncoding">UTF-8</property>  
6.     <property name="hibernate.show_sql">true</property>  
7.     <!-- c3p0在我們使用的Hibernate版本中內建，不用下載，直接使用 -->  
8.     <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>  
9.     <property name="hibernate.c3p0.min_size">5</property>  
10.     <property name="hibernate.c3p0.max_size">20</property>  
11.     <property name="hibernate.c3p0.timeout">1800</property>  
12.     <property name="hibernate.c3p0.max_statements">50</property>  
13.     <property name="hibernate.c3p0.maxIdelTime">1800</property> 
14.     <!-- 下面這句很重要，後面有解釋 -->  
15.     <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>  
16.     <!-- 以下就全是mapping了，省略 -->  
17. </session-factory>  

 

上面配置中最重要的就是hibernate.c3p0.testConnectionOnCheckout這個屬性，它保證了我們前面說的每次取出串連時會檢查該串連是否被關閉了。不過這個屬性會對效能有一些損耗，引用我參考的部落格上得話：程式能用是第一，之後才是它的效能（又不是不能容忍）。

當然，c3p0內建類似於select 1這樣的check機制，但是就像我說的，除非你將check機制的間隔時間把握的非常好，否則，問題是沒有解決的。

好了，至此，困擾我的問題解決完了。希望上面的這些整理可以為我以後碰到類似的問題留個思路，也可以為正在被此問題困擾的人提供一絲協助。

 

2、使用dbcp資料來源

 

由於mysql的預設最大空閑時間8小時，所以只要把minEvictableIdleTimeMillis設定小於此值即可。例如配置每十分鐘檢查超過空閑一個小時的串連

<property name="minEvictableIdleTimeMillis"><value>3600000</value></property><property name="timeBetweenEvictionRunsMillis"><value>600000</value></property>

 

3、proxool 資料來源

資料庫連接池proxool，它有兩個屬性：一個是test-before-use，還有一個是test-after-use，這兩個屬性就是在使用前和使用後都要進行對串連的檢查，如果串連無效就扔掉再建立一個新的串連

4、

testConnectionOnCheckout： 因效能消耗大請只在需要的時候使用它。如果設為true那麼在每個connection提交的時候都 將校正其有效性。建議使用 idleConnectionTestPeriod或automaticTestTable 
等方法來提升串連測試的效能。預設為false；

 

（1）idleConnectionTestPeriod

當資料庫重啟後或者由於某種原因進程被殺掉後，C3P0不會自動重新初始化資料庫連接池，當新的請求需要訪問資料庫的時候，此時會報錯誤(因為串連失效)，同時重新整理資料庫連接池，丟棄掉已經失效的串連，當第二個請求到來時恢複正常。

C3P0目前沒有提供當擷取已建立串連失敗後重試次數的參數，只有擷取新串連失敗後重試次數的參數(acquireRetryAttempts【預設為30】 )。

要解決此問題，可以通過設定idleConnectionTestPeriod【預設為0，表示不檢查 】參數折中解決，該參數的作用是設定系統自動檢查串連池中串連是否正常的一個頻率參數，時間單位是秒 。

 

（2）acquireIncrement

當串連池中的的串連耗盡的時候c3p0一次同時擷取的串連數，也就是說，如果使用的串連數已經達到了maxPoolSize，c3p0會立即建立新的串連。

 

（3）maxIdleTime

另外，C3P0預設不會close掉不用的串連池，而是將其回收到可用串連池中，這樣會導致，串連數越來越大，所以需要設定maxIdleTime【預設0，表示永遠不到期】，單位是秒，maxIdleTime表示idle狀態的connection能存活的最大時間。

【轉】MySQL連線逾時斷開的問題