I have a grails2 based application which is using tomcat jdbc pool, recently I have been getting into problem where all the connections in the pool get used up and I start getting:-
org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-8443-exec-38] Timeout: Pool empty. Unable to fetch a connection in 10 seconds, none available[size:100; busy:100; idle:0; lastwait:10000].; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-8443-exec-38] Timeout: Pool empty. Unable to fetch a connection in 10 seconds, none available[size:100; busy:100; idle:0; lastwait:10000].
I have a few query that requires heavy join and some stored proc that executes for about 2 - 3 minutes, for it i am manually get the connection from the datasource bean :-
currentConnection = dataSource.connection
sqlInstance = new Sql(currentConnection)
sqlInstance.execute(query)
sqlInstance.close()
I've logged the total active connection in stdout and i see that the no. of active connection keeps on rising and rising and it never drops, it then gets to 100 which is the total active connection allowed and then i start getting issue of poolexhaustauion, can anyone give me an idea, what i might be missing or where the connection might be leaking. here is my connection detail :-
dataSource {
pooled = true
driverClassName = "com.mysql.jdbc.Driver"
url="jdbc:mysql://something:3306/something?zeroDateTimeBehavior=convertToNull&autoReconnect=true&relaxAutoCommit=true"
username="#####"
password='#$#$$$$$$$'
dbCreate = "update"
properties {
initialSize=5
maxActive=100
minIdle=5
maxIdle=25
maxWait = 10000
maxAge = 10 * 60000
timeBetweenEvictionRunsMillis=5000
minEvictableIdleTimeMillis=60000
validationQuery="SELECT 1"
validationInterval=15000
testWhileIdle=true
testOnBorrow=true
testOnReturn=true
removeAbandoned=true
removeAbandonedTimeout=400
logAbandoned=true
jdbcInterceptors = "ConnectionState"
defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
}
}