Throwable occurred: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 50,123,505 milliseconds ago. The last packet sent successfully to the server was 50,123,505 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.
這主要是由兩個原因引起來的:
1.mysql 會自動關(guān)閉長時間不用的connection,一個連接如果處于sleep狀態(tài)達(dá)到mysql的參數(shù)wait_timeout指定的時間(默認(rèn)為8小時),就是自動關(guān)閉這個連接
2.common pool中沒有指定相應(yīng)的連接檢查參數(shù)
解決辦法:從common pool的配置參數(shù)來解決:
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName">
<value>${db.driver}</value>
</property>
<property name="url">
<value>${db.url}</value>
</property>
<property name="username">
<value>${db.user}</value>
</property>
<property name="password">
<value>${db.password}</value>
</property>
<property name="maxActive">
<value>100</value>
</property>
<property name="maxIdle">
<value>50</value>
</property>
<property name="maxWait">
<value>10000</value>
</property>
<property name="timeBetweenEvictionRunsMillis">
<value>3600000</value><!--1 hours-->
</property>
<!--
<property name="minEvictableIdleTimeMillis">
<value>20000</value>
</property>
-->
<property name="testWhileIdle">
<value>true</value>
</property>
<property name="validationQuery">
<value>select 1 from dual</value>
</property>
</bean>
使用上述的三個紅色的參數(shù),就可以避免這個問題.這三個參數(shù)的意義:
timeBetweenEvictionRunsMillis:啟動connection校驗定時器,定時器運行時間間隔就是timeBetweenEvictionRunsMillis的值.默認(rèn)為-1,表示不啟動定時器,這里設(shè)定為1小時,只要小于mysql的wait_timeout就可以了
testWhileIdle: true,表示檢查idle的connection,false為不檢查
validationQuery:用于檢查connection的sql語句.
這只是一種方法,另外的幾種方法:
timeBetweenEvictionRunsMillis+minEvictableIdleTimeMillis:這種方式不檢查Connection的有效性,而是檢查連接的空閑時間,大于minEvictableIdleTimeMillis就清除.
<property name="timeBetweenEvictionRunsMillis">
<value>3600000</value><!--1 hours-->
</property>
<property name="minEvictableIdleTimeMillis">
<value>120000</value><!--connection的空閑時間大于這個值,就直接被關(guān)閉,并從連接池中刪除-->
</property>
如果不喜歡用定時器,也可以配置testOnBorrow+validationQuery參數(shù):每次從連接池取參數(shù)都會校驗連接的有效性.實際上這種方式性能會比定時器差些.
<property name="testOnBorrow">
<value>true</value>
</property>
<property name="validationQuery">
<value>select 1 from dual</value>
</property>
另外,也可以用testOnReturn+validationQuery,不過未必會解決問題:這表示每次使用完連接,歸還連接池的時候檢查連接的有效性,這有可能導(dǎo)致使用一次無效的連接,最好不要用.
上面的幾種方法可以合并使用,只是檢查的點多了,未必是好事
另外,也可以使用Abandoned的那幾個參數(shù),來刪除連接池中的連接.也能達(dá)到效果.我沒測試.