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.
這主要是由兩個(gè)原因引起來的:
1.mysql 會(huì)自動(dòng)關(guān)閉長(zhǎng)時(shí)間不用的connection,一個(gè)連接如果處于sleep狀態(tài)達(dá)到mysql的參數(shù)wait_timeout指定的時(shí)間(默認(rèn)為8小時(shí)),就是自動(dòng)關(guān)閉這個(gè)連接
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>
使用上述的三個(gè)紅色的參數(shù),就可以避免這個(gè)問題.這三個(gè)參數(shù)的意義:
timeBetweenEvictionRunsMillis:啟動(dòng)connection校驗(yàn)定時(shí)器,定時(shí)器運(yùn)行時(shí)間間隔就是timeBetweenEvictionRunsMillis的值.默認(rèn)為-1,表示不啟動(dòng)定時(shí)器,這里設(shè)定為1小時(shí),只要小于mysql的wait_timeout就可以了
testWhileIdle: true,表示檢查idle的connection,false為不檢查
validationQuery:用于檢查connection的sql語句.
這只是一種方法,另外的幾種方法:
timeBetweenEvictionRunsMillis+minEvictableIdleTimeMillis:這種方式不檢查Connection的有效性,而是檢查連接的空閑時(shí)間,大于minEvictableIdleTimeMillis就清除.
<property name="timeBetweenEvictionRunsMillis">
<value>3600000</value><!--1 hours-->
</property>
<property name="minEvictableIdleTimeMillis">
<value>120000</value><!--connection的空閑時(shí)間大于這個(gè)值,就直接被關(guān)閉,并從連接池中刪除-->
</property>
如果不喜歡用定時(shí)器,也可以配置testOnBorrow+validationQuery參數(shù):每次從連接池取參數(shù)都會(huì)校驗(yàn)連接的有效性.實(shí)際上這種方式性能會(huì)比定時(shí)器差些.
<property name="testOnBorrow">
<value>true</value>
</property>
<property name="validationQuery">
<value>select 1 from dual</value>
</property>
另外,也可以用testOnReturn+validationQuery,不過未必會(huì)解決問題:這表示每次使用完連接,歸還連接池的時(shí)候檢查連接的有效性,這有可能導(dǎo)致使用一次無效的連接,最好不要用.
上面的幾種方法可以合并使用,只是檢查的點(diǎn)多了,未必是好事
另外,也可以使用Abandoned的那幾個(gè)參數(shù),來刪除連接池中的連接.也能達(dá)到效果.我沒測(cè)試.