Connection to database is closed in Virtual Service Catalog Devtest 10.3

Products

• CA Application Test
• Service Virtualization
• CA Continuous Application Insight (PathFinder)

Issue/Introduction

After Running the Datasource updater Utility ,  started VScatalog ,  it runs for 20- 30 mins and then looses Database connections.  We are seeing the below error in the vscatalog log. 


21:50:12,090 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Timer-2) SQL Error: 0, SQLState: 08003 
21:50:12,090 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Timer-2) No operations allowed after connection closed. 
ClearExpiredUserSessions:  (Timer-2) KC-SERVICE: Failed to run scheduled task ClearExpiredUserSessions: javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: could not prepare statement at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492) at org.keycloak.models.jpa.JpaRealmProvider.getRealms(JpaRealmProvider.java:99) at org.keycloak.models.cache.infinispan.RealmCacheSession.getRealms(RealmCacheSession.java:447) at org.keycloak.services.scheduled.ClearExpiredUserSessions.run(ClearExpiredUserSessions.java:35) at org.keycloak.services.scheduled.ScheduledTaskRunner.runTask(ScheduledTaskRunner.java:61) at org.keycloak.services.scheduled.ScheduledTaskRunner.run(ScheduledTaskRunner.java:45) at org.keycloak.timer.basic.BasicTimerProvider$1.run(BasicTimerProvider.java:51) at java.util.TimerThread.mainLoop(Timer.java:555) at java.util.TimerThread.run(Timer.java:505) Caused by: org.hibernate.exception.JDBCConnectionException: could not prepare statement at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1934) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1903) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1881) at org.hibernate.loader.Loader.doQuery(Loader.java:925) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342) at org.hibernate.loader.Loader.doList(Loader.java:2622) at org.hibernate.loader.Loader.doList(Loader.java:2605) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2434) at org.hibernate.loader.Loader.list(Loader.java:2429) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.SessionImpl.list(SessionImpl.j (message truncated)Dat

Cause

DB Validation will be done for every 10 secs. Looks like there were idle connection and we had to flush it out. 

Environment

DEVTEST 10.3

Resolution

If there are any IDLE database connections  we are closing these by adding the below properties. 

In the standalone.xml file 10.3GA\vscatalog\IdentityAccessManager\standalone\configuration we made the following changes 

Under mysql driver.. add, 

<pool> 
<min-pool-size>5</min-pool-size> 
<max-pool-size>10</max-pool-size> 
<flush-strategy>IdleConnections</flush-strategy> 
</pool> 
Min pool size is 5 and Max is 10 and will flush out any idle connection 

Then, after Security tab. 

<validation> 
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"></exception-sorter> 
<validate-on-match>false</validate-on-match> 
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql> 
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"></valid-connection-checker> 
<use-fast-fail>true</use-fast-fail> 
<background-validation>true</background-validation> 
<background-validation-millis>10000</background-validation-millis> 
</validation> 


DB Validation will be done for every 10 secs.