[keycloak-user] Oracle Database Connection Issues

Stian Thorgersen sthorger at redhat.com
Fri Oct 28 03:52:19 EDT 2016 

Oracle as well?

On 28 October 2016 at 09:18, Thomas Darimont <thomas.darimont at googlemail.com
> wrote:

> Hello,
>
> this is interesting - I just saw the same exception yesterday in one of
> our clusters with 2.2.1.Final and users couldn't log in to Keycloak
> anymore. Only restart of the Keycloak (Docker Container) helped.
>
> Still trying to find out what happend....
>
> Cheers,
> Thomas
>
> 2016-10-28 8:55 GMT+02:00 Stian Thorgersen <sthorger at redhat.com>:
>
>> 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.JDBCCo
>> nnectionException:
>> > 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.ClusterAwareScheduledTaskRun
>> ne
>> > r$1.call(ClusterAwareScheduledTaskRunner.java:53)
>> >         at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRun
>> ne
>> > r$1.call(ClusterAwareScheduledTaskRunner.java:49)
>> >         at org.keycloak.cluster.infinispan.InfinispanClusterProvider.
>> > executeIfNotExecuted(InfinispanClusterProvider.java:90)
>> >         at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRun
>> ne
>> > 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.StandardSQLExceptionConvert
>> er.
>> > convert(StandardSQLExceptionConverter.java:42)
>> >         at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(
>> > SqlExceptionHelper.java:109)
>> >         at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$
>> > StatementPreparationTemplate.prepareStatement(StatementPrepa
>> rerImpl.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.prepareStateme
>> nt(
>> > WrappedConnection.java:454)
>> >         at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.
>> > doPrepare(StatementPreparerImpl.java:146)
>> >         at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$
>> > StatementPreparationTemplate.prepareStatement(StatementPrepa
>> rerImpl.java:
>> > 172)
>> >         ... 28 more
>> >
>> >
>> >
>> > Here's the datasource config:
>> >                 <datasource jndi-name="java:jboss/datasour
>> ces/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
>> >
>> _______________________________________________
>> 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