Re: [keycloak-user] Keycloak unable to open JDBC connection

The jboss documentation describes how to setup the validation check for Oracle with nice examples: https://access.redhat.com/documentation/en-US/JBoss_Enterprise_Applicatio... Another option is to use the Wildfly administrator pages to setup the datasource. This option will create a new datasource that includes the validation check. This tutorial describes what this looks like: http://www.itprogrammingtutorials.com/2014/java/jboss/connection-pool-jbo... Rick On Fri, Jul 15, 2016 at 4:51 AM, Stian Thorgersen <sthorger(a)redhat.com&gt; wrote: > See > http://stackoverflow.com/questions/31455450/auto-recover-connections-in-w... > > On 14 July 2016 at 15:27, Thomas Barcia <TBarcia(a)wfscorp.com&gt; wrote: > >> 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(a)wfscorp.com >> >> [image: 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.*** >> >> >> _______________________________________________ >> keycloak-user mailing list >> keycloak-user(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/keycloak-user >> > > > _______________________________________________ > keycloak-user mailing list > keycloak-user(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/keycloak-user >