<div dir="ltr">The jboss documentation describes how to setup the validation check for Oracle with nice examples:<div><a href="https://access.redhat.com/documentation/en-US/JBoss_Enterprise_Application_Platform/6/html/Administration_and_Configuration_Guide/Example_Oracle_Datasource.html">https://access.redhat.com/documentation/en-US/JBoss_Enterprise_Application_Platform/6/html/Administration_and_Configuration_Guide/Example_Oracle_Datasource.html</a><br></div><div> <br></div><div>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: <a href="http://www.itprogrammingtutorials.com/2014/java/jboss/connection-pool-jboss-wildfly/">http://www.itprogrammingtutorials.com/2014/java/jboss/connection-pool-jboss-wildfly/</a></div><div><br></div><div>Rick</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Sat, Jul 16, 2016 at 9:35 AM, Ricardo Chu <span dir="ltr"><<a href="mailto:rick@chucrew.net" target="_blank">rick@chucrew.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">The jboss documentation describes how to setup the validation check for Oracle with nice examples:<div><a href="https://access.redhat.com/documentation/en-US/JBoss_Enterprise_Application_Platform/6/html/Administration_and_Configuration_Guide/Example_Oracle_Datasource.html" target="_blank">https://access.redhat.com/documentation/en-US/JBoss_Enterprise_Application_Platform/6/html/Administration_and_Configuration_Guide/Example_Oracle_Datasource.html</a><br></div><div> <br></div><div>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: <a href="http://www.itprogrammingtutorials.com/2014/java/jboss/connection-pool-jboss-wildfly/" target="_blank">http://www.itprogrammingtutorials.com/2014/java/jboss/connection-pool-jboss-wildfly/</a></div><div><br></div><div>Rick</div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Jul 15, 2016 at 4:51 AM, Stian Thorgersen <span dir="ltr"><<a href="mailto:sthorger@redhat.com" target="_blank">sthorger@redhat.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">See <a href="http://stackoverflow.com/questions/31455450/auto-recover-connections-in-wildfly-8-2-for-oracle-without-using-validate-on-mat" target="_blank">http://stackoverflow.com/questions/31455450/auto-recover-connections-in-wildfly-8-2-for-oracle-without-using-validate-on-mat</a></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div>On 14 July 2016 at 15:27, Thomas Barcia <span dir="ltr"><<a href="mailto:TBarcia@wfscorp.com" target="_blank">TBarcia@wfscorp.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div>
<div lang="EN-US" link="blue" vlink="purple">
<div>
<p class="MsoNormal">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?<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Thanks.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">This is from my standalone-ha.xml:<u></u><u></u></p>
<p class="MsoNormal"><datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true"><u></u><u></u></p>
<p class="MsoNormal"> <connection-url>jdbc:oracle:thin:@<servername>:<port>:<databasename></connection-url><u></u><u></u></p>
<p class="MsoNormal"> <driver>oracle</driver><u></u><u></u></p>
<p class="MsoNormal"> <pool><u></u><u></u></p>
<p class="MsoNormal"> <min-pool-size>1</min-pool-size><u></u><u></u></p>
<p class="MsoNormal"> <max-pool-size>5</max-pool-size><u></u><u></u></p>
<p class="MsoNormal"> <prefill>true</prefill><u></u><u></u></p>
<p class="MsoNormal"> </pool><u></u><u></u></p>
<p class="MsoNormal"> <security><u></u><u></u></p>
<p class="MsoNormal"> <user-name><name></user-name><u></u><u></u></p>
<p class="MsoNormal"> <password><password></password><u></u><u></u></p>
<p class="MsoNormal"> </security><u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"> </datasource><u></u><u></u></p>
<p class="MsoNormal"> <drivers><u></u><u></u></p>
<p class="MsoNormal"> <driver name="h2" module="com.h2database.h2"><u></u><u></u></p>
<p class="MsoNormal"> <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class><u></u><u></u></p>
<p class="MsoNormal"> </driver><u></u><u></u></p>
<p class="MsoNormal"> <driver name="oracle" module="com.oracle"><u></u><u></u></p>
<p class="MsoNormal"> <driver-class>oracle.jdbc.driver.OracleDriver</driver-class><u></u><u></u></p>
<p class="MsoNormal"> </driver><u></u><u></u></p>
<p class="MsoNormal"> </drivers><u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">And this is the error:<u></u><u></u></p>
<p class="MsoNormal">2016-07-14 00:13:09,460 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Timer-2) SQL Error: 0, SQLState: null<u></u><u></u></p>
<p class="MsoNormal">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<u></u><u></u></p>
<p class="MsoNormal">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<u></u><u></u></p>
<p class="MsoNormal"> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)<u></u><u></u></p>
<p class="MsoNormal"> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)<u></u><u></u></p>
<p class="MsoNormal"> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1700)<u></u><u></u></p>
<p class="MsoNormal"> at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:48)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.connections.jpa.JpaKeycloakTransaction.begin(JpaKeycloakTransaction.java:39)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.services.DefaultKeycloakTransactionManager.enlist(DefaultKeycloakTransactionManager.java:41)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:70)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:54)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.services.DefaultKeycloakSession.getProvider(DefaultKeycloakSession.java:101)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.models.jpa.JpaRealmProviderFactory.create(JpaRealmProviderFactory.java:51)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.models.jpa.JpaRealmProviderFactory.create(JpaRealmProviderFactory.java:33)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.services.DefaultKeycloakSession.getProvider(DefaultKeycloakSession.java:101)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.models.cache.infinispan.RealmCacheSession.getDelegate(RealmCacheSession.java:161)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.models.cache.infinispan.RealmCacheSession.getRealms(RealmCacheSession.java:424)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.services.scheduled.ClearExpiredUserSessions.run(ClearExpiredUserSessions.java:33)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunner$1.call(ClusterAwareScheduledTaskRunner.java:53)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunner$1.call(ClusterAwareScheduledTaskRunner.java:49)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.cluster.infinispan.InfinispanClusterProvider.executeIfNotExecuted(InfinispanClusterProvider.java:90)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunner.runTask(ClusterAwareScheduledTaskRunner.java:49)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.services.scheduled.ScheduledTaskRunner.run(ScheduledTaskRunner.java:44)<u></u><u></u></p>
<p class="MsoNormal"> at org.keycloak.timer.basic.BasicTimerProvider$1.run(BasicTimerProvider.java:51)<u></u><u></u></p>
<p class="MsoNormal"> at java.util.TimerThread.mainLoop(Timer.java:555)<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><b><span style="font-family:"Times New Roman","serif";color:#365f91">Thomas Barcia<u></u><u></u></span></b></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Book Antiqua","serif";color:#a6a6a6">Unix Administrator<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Book Antiqua","serif";color:#a6a6a6"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Book Antiqua","serif";color:#a6a6a6">World Fuel Services Corporation<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Book Antiqua","serif";color:#a6a6a6">9800 NW 41st Street|Miami, FL 33178<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Book Antiqua","serif";color:#a6a6a6">office: <a href="tel:305.351.4910" value="+13053514910" target="_blank">305.351.4910</a>|email:
</span><span style="font-size:8.0pt;font-family:"Book Antiqua","serif""><a href="mailto:tbarcia@wfscorp.com" target="_blank"><span style="color:blue">tbarcia@wfscorp.com</span></a></span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New Roman","serif";color:#1f497d"><img border="0" width="69" height="40" src="cid:image001.png@01D1DDB1.39F60770" alt="Description: Description: wfs"></span><span style="font-size:12.0pt;color:#1f497d"><u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<p>*** This communication has been sent from World Fuel Services <br>
Corporation or its subsidiaries or its affiliates for the intended recipient <br>
only and may contain proprietary, confidential or privileged information. <br>
If you are not the intended recipient, any review, disclosure, copying, <br>
use, or distribution of the information included in this communication <br>
and any attachments is strictly prohibited. If you have received this <br>
communication in error, please notify us immediately by replying to this <br>
communication and delete the communication, including any <br>
attachments, from your computer. Electronic communications sent to or <br>
from World Fuel Services Corporation or its subsidiaries or its affiliates <br>
may be monitored for quality assurance and compliance purposes.***</p>
<p></p></div>
<br></div></div>_______________________________________________<br>
keycloak-user mailing list<br>
<a href="mailto:keycloak-user@lists.jboss.org" target="_blank">keycloak-user@lists.jboss.org</a><br>
<a href="https://lists.jboss.org/mailman/listinfo/keycloak-user" rel="noreferrer" target="_blank">https://lists.jboss.org/mailman/listinfo/keycloak-user</a><br></blockquote></div><br></div>
<br>_______________________________________________<br>
keycloak-user mailing list<br>
<a href="mailto:keycloak-user@lists.jboss.org" target="_blank">keycloak-user@lists.jboss.org</a><br>
<a href="https://lists.jboss.org/mailman/listinfo/keycloak-user" rel="noreferrer" target="_blank">https://lists.jboss.org/mailman/listinfo/keycloak-user</a><br></blockquote></div><br></div>
</div></div></blockquote></div><br></div>