[keycloak-user] Keycloak unable to open JDBC connection

[Thomas Barcia]

I have Keycloak 1.9.8-Final running against an Oracle database and it appears that when the connections are unused for a period of time (usually overnight) Keycloak is unable to open a JDBC connection to the database.  I spoke with the DBAs and the database is not closing the connections.  According to the DBAs I need to enable connection validation but I'm not a programmer and can't find a good example of how it's done.  Can anyone provide some help with this?  Am I on the right track in resolving the issue?

Thanks.

This is from my standalone-ha.xml:
<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
                 <connection-url>jdbc:oracle:thin:@<servername>:<port>:<databasename></connection-url>
                  <driver>oracle</driver>
                 <pool>
                   <min-pool-size>1</min-pool-size>
                   <max-pool-size>5</max-pool-size>
                   <prefill>true</prefill>
                  </pool>
                  <security>
                   <user-name><name></user-name>
                   <password><password></password>
                 </security>

                </datasource>
                <drivers>
                    <driver name="h2" module="com.h2database.h2">
                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                    </driver>
                    <driver name="oracle" module="com.oracle">
                        <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
                    </driver>
                </drivers>

And this is the error:
2016-07-14 00:13:09,460 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Timer-2) SQL Error: 0, SQLState: null
2016-07-14 00:13:09,461 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Timer-2) javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/KeycloakDS
2016-07-14 00:13:09,462 ERROR [org.keycloak.services] (Timer-2) KC-SERVICES0089: Failed to run scheduled task ClearExpiredUserSessions: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1700)
        at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:48)
        at org.keycloak.connections.jpa.JpaKeycloakTransaction.begin(JpaKeycloakTransaction.java:39)
        at org.keycloak.services.DefaultKeycloakTransactionManager.enlist(DefaultKeycloakTransactionManager.java:41)
        at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:70)
        at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:54)
        at org.keycloak.services.DefaultKeycloakSession.getProvider(DefaultKeycloakSession.java:101)
        at org.keycloak.models.jpa.JpaRealmProviderFactory.create(JpaRealmProviderFactory.java:51)
        at org.keycloak.models.jpa.JpaRealmProviderFactory.create(JpaRealmProviderFactory.java:33)
        at org.keycloak.services.DefaultKeycloakSession.getProvider(DefaultKeycloakSession.java:101)
        at org.keycloak.models.cache.infinispan.RealmCacheSession.getDelegate(RealmCacheSession.java:161)
        at org.keycloak.models.cache.infinispan.RealmCacheSession.getRealms(RealmCacheSession.java:424)
        at org.keycloak.services.scheduled.ClearExpiredUserSessions.run(ClearExpiredUserSessions.java:33)
        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)

Thomas  Barcia
Unix Administrator

World Fuel Services Corporation
9800 NW 41st Street|Miami, FL 33178
office: 305.351.4910|email: tbarcia at wfscorp.com<mailto:tbarcia at wfscorp.com>
[Description: Description: wfs]

*** 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.***

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/keycloak-user/attachments/20160714/9bd01817/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 4236 bytes
Desc: image001.png
Url : http://lists.jboss.org/pipermail/keycloak-user/attachments/20160714/9bd01817/attachment-0001.png