Connection to database is closed in Virtual Service Catalog Devtest 10.3
Article ID: 129652
Updated On:
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
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.
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.
Feedback
Yes
No
Powered by
