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 會自動關閉長時間不用的connection,一個連接如果處于sleep狀態達到mysql的參數wait_timeout指定的時間(默認為8小時),就是自動關閉這個連接
2.common pool中沒有指定相應的連接檢查參數
解決辦法:從common pool的配置參數來解決:
<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>
使用上述的三個紅色的參數,就可以避免這個問題.這三個參數的意義:
timeBetweenEvictionRunsMillis:啟動connection校驗定時器,定時器運行時間間隔就是timeBetweenEvictionRunsMillis的值.默認為-1,表示不啟動定時器,這里設定為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的空閑時間大于這個值,就直接被關閉,并從連接池中刪除-->
</property>
如果不喜歡用定時器,也可以配置testOnBorrow+validationQuery參數:每次從連接池取參數都會校驗連接的有效性.實際上這種方式性能會比定時器差些.
<property name="testOnBorrow">
<value>true</value>
</property>
<property name="validationQuery">
<value>select 1 from dual</value>
</property>
另外,也可以用testOnReturn+validationQuery,不過未必會解決問題:這表示每次使用完連接,歸還連接池的時候檢查連接的有效性,這有可能導致使用一次無效的連接,最好不要用.
上面的幾種方法可以合并使用,只是檢查的點多了,未必是好事
另外,也可以使用Abandoned的那幾個參數,來刪除連接池中的連接.也能達到效果.我沒測試.