1:58 p.m.
I'm experiencing errors with Keycloak connected to an Oracle database. It was working
fine and we didn't notice the errors until after upgrading to 2.2.1.
The errors:
2016-10-26 11:35:19,502 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Timer-3)
SQL Error: 17008, SQLState: 08003
2016-10-26 11:35:19,503 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Timer-3)
Closed Connection
2016-10-26 11:35:19,504 ERROR [org.keycloak.services] (Timer-3) KC-SERVICES0089: Failed to
run scheduled task ClearExpiredEvents: 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:424)
at
org.keycloak.services.scheduled.ClearExpiredEvents.run(ClearExpiredEvents.java:34)
at
org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunner$1.call(ClusterAwareScheduledTaskRunner.java:53)
at
org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunner$1.call(ClusterAwareScheduledTaskRunner.java:49)
at
org.keycloak.cluster.infinispan.InfinispanClusterProvider.executeIfNotExecuted(InfinispanClusterProvider.java:90)
at
org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunner.runTask(ClusterAwareScheduledTaskRunner.java:49)
at
org.keycloak.services.scheduled.ScheduledTaskRunner.run(ScheduledTaskRunner.java:44)
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.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:115)
at
org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
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:1928)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1897)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1875)
at org.hibernate.loader.Loader.doQuery(Loader.java:919)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
at org.hibernate.loader.Loader.doList(Loader.java:2611)
at org.hibernate.loader.Loader.doList(Loader.java:2594)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2423)
at org.hibernate.loader.Loader.list(Loader.java:2418)
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.java:1326)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
... 11 more
Caused by: java.sql.SQLRecoverableException: Closed Connection
at
oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3587)
at
org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:778)
at
org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:764)
at
org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
at
org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
at
org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
... 28 more
Here's the datasource config:
<datasource jndi-name="java:jboss/datasources/KeycloakDS"
pool-name="KeycloakDS" enabled="true"
use-java-context="true">
<connection-url>jdbc:oracle:thin:@dbserver:1550:instance</connection-url>
<driver>oracle</driver>
<pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>200</max-pool-size>
<prefill>true</prefill>
</pool>
<security>
<user-name>KEYCLOAK</user-name>
<password> </password>
</security>
<validation>
<background-validation>true</background-validation>
<check-valid-connection-sql>select 1 from
dual</check-valid-connection-sql>
</validation>
</datasource>
The H2 datasource is still in the standalone-ha.xml as is the h2 driver but they've
never been used or modified.
As I said, this has been working perfectly but suddenly appears to be closing connections
despite having the automatic validation turned on. This only happens with the production
environment and experiences no issues in the DEV environment that has the same config
except for being connected to a different DB server. The DBAs are seeing only 5
connections in this environment but more in DEV.
Any help would be appreciated.
*** This communication has been sent from World Fuel Services
Corporation or its subsidiaries or its affiliates for the intended recipient
only and may contain proprietary, confidential or privileged information.
If you are not the intended recipient, any review, disclosure, copying,
use, or distribution of the information included in this communication
and any attachments is strictly prohibited. If you have received this
communication in error, please notify us immediately by replying to this
communication and delete the communication, including any
attachments, from your computer. Electronic communications sent to or
from World Fuel Services Corporation or its subsidiaries or its affiliates
may be monitored for quality assurance and compliance purposes.***