Connection to database is closed in Virtual Service Catalog Devtest 10.3

Article Id: 129652
Status: Published
Updated On: 18-03-2019 14:08
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.