java不连接sql报错,java.sql.SQLException：在连接缓存中找到无效或失效连接

I am using spring framework 3.2 with hibernate 4 , I get the above exception when sending a request after a long idle time on the local server ( apache-tomcat v7.0 ) and the database is located on remote server. After hours of search I came to that the problem comes from the connection pool. I tried number of connection pools but didn't find the satisfying solution. bellow is the current datasource on my spring-data file

destroy-method="close">

MinLimit:70

MaxLimit:200

InitialLimit:20

ConnectionWaitTimeout:120

InactivityTimeout:180

ValidateConnection:true

please advise.

解决方案

You will get the "Invalid or Stale Connection" error when you have a connection in the connection pool which is no longer connected to the Database actively. Below are few scenarios which can lead to this

Connection is manually aborted from the database by a dba. For example, if

the connection was killed using "ALTER SYSTEM KILL SESSION"

When a connection exists in the connection pool without being used for a

long time and is disconnected due to the timeouts enforced by the

database (idle_time)

A database restart

A network event has caused

the connection to drop, probably because the network has become

unavailable or a firewall has dropped a connection which has been

open for too long.

If you are setting the InactivityTimeout, then you have to make sure that it's less that the IDLE_TIME enforced by the database. You can get the IDLE_TIME with the below query

select * from dba_profiles dp, dba_users du

where dp.profile = du.profile and du.username ='YOUR_JDBC_USER_NAME';

When you use connectionCacheProperties, always make sure that you set the PropertyCheckInterval property to something less than the timeouts. The default value is 900 seconds which means that the cache daemon thread will only run every 15 minutes and enforce the timeouts. So you would always want to set this to a value lower than your timeout properties.

I would always make sure that I use 0 as the MinLimit.

Rewriting you config file a little bit would make it :

0

200

1

120

180

true

150

You might also get a "Invalid or Stale Connection Error" when your network is actually broken at the time when you try to validate an old connection obtained from the pool.