[keycloak-user] Oracle Database Connection Issues

Stian Thorgersen sthorger at redhat.com
Fri Oct 28 02:55:14 EDT 2016


I don't think there's anything that has changed on our end that is causing
this issue and since it's happening only in your prod environment maybe
there's some network issue or db configuration issues that is causing this.
Try a Google search there's quite a lot of hints around this type of issue.

On 27 October 2016 at 20:58, Thomas Barcia <TBarcia at wfscorp.com> wrote:

> 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.ClusterAwareScheduledTaskRunne
> r$1.call(ClusterAwareScheduledTaskRunner.java:53)
>         at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunne
> r$1.call(ClusterAwareScheduledTaskRunner.java:49)
>         at org.keycloak.cluster.infinispan.InfinispanClusterProvider.
> executeIfNotExecuted(InfinispanClusterProvider.java:90)
>         at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunne
> r.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.doQueryAndInitializeNonLazyCol
> lections(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.***
>
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user
>


More information about the keycloak-user mailing list