I’m trying to find the reason why keycloak does not reconnect to the database after more than 5 minutes of database unavailability. If the unavailability of the keycloak database is less than 5 minutes, the application starts working properly. Could someone help me to solve it please.
Keycloak version 19.0.3 wildfly.
Database connection configuration
<subsystem xmlns="urn:jboss:domain:datasources:7.0">
<datasources>
<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
<connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
<driver>h2</driver>
<security>
<user-name>sa</user-name>
<password>sa</password>
</security>
</datasource>
<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
<connection-url>jdbc:oracle:thin:@ora:1531:KCLOAK</connection-url>
<driver>oracle</driver>
<pool>
<min-pool-size>10</min-pool-size>
<max-pool-size>20</max-pool-size>
<prefill>true</prefill>
<use-strict-min>true</use-strict-min>
</pool>
<security>
<user-name>keycloakd2</user-name>
<credential-reference store="keycloak" alias="pwd::oracle_pass"/>
</security>
<validation>
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
<validate-on-match>false</validate-on-match>
<background-validation>true</background-validation>
<background-validation-millis>20000</background-validation-millis>
</validation>
<timeout>
<blocking-timeout-millis>30000</blocking-timeout-millis>
<allocation-retry>480</allocation-retry>
<allocation-retry-wait-millis>30000</allocation-retry-wait-millis>
</timeout>
</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>
</datasources>
</subsystem>
These are the errors that appear.
2023-01-19 10:41:16,014 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (Timer-2) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: IJ031084: Unable to create connection
at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:364)
at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:371)
at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:287)
at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.createConnectionEventListener(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:1328)
at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:505)
at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getTransactionNewConnection(AbstractPool.java:722)
at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:618)
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:660)
at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:440)
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:789)
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:151)
at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:64)
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:35)
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:106)
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:136)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:50)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2082)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1990)
at org.hibernate.loader.Loader.scroll(Loader.java:2863)
at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:574)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:447)
at org.hibernate.engine.query.spi.HQLQueryPlan.performScroll(HQLQueryPlan.java:354)
at org.hibernate.internal.SessionImpl.scroll(SessionImpl.java:1658)
at org.hibernate.query.internal.AbstractProducedQuery.doScroll(AbstractProducedQuery.java:1537)
at org.hibernate.query.internal.AbstractProducedQuery.scroll(AbstractProducedQuery.java:1523)
at org.hibernate.query.internal.AbstractProducedQuery.stream(AbstractProducedQuery.java:1547)
at org.hibernate.query.Query.getResultStream(Query.java:1107)
at org.keycloak.models.jpa.JpaRealmProvider.getRealms(JpaRealmProvider.java:144)
at org.keycloak.models.jpa.JpaRealmProvider.getRealmsStream(JpaRealmProvider.java:140)
at org.keycloak.models.cache.infinispan.RealmCacheSession.getRealmsStream(RealmCacheSession.java:484)
at org.keycloak.models.sessions.infinispan.InfinispanUserSessionProvider.removeAllExpired(InfinispanUserSessionProvider.java:601)
at org.keycloak.services.scheduled.ClearExpiredUserSessions.run(ClearExpiredUserSessions.java:39)
at org.keycloak.services.scheduled.ScheduledTaskRunner.runTask(ScheduledTaskRunner.java:73)
at org.keycloak.services.scheduled.ScheduledTaskRunner.run(ScheduledTaskRunner.java:54)
at org.keycloak.timer.basic.BasicTimerProvider$1.run(BasicTimerProvider.java:53)
at java.util.TimerThread.mainLoop(Timer.java:555)
at java.util.TimerThread.run(Timer.java:505)
Caused by: java.sql.SQLRecoverableException: ORA-01033: ORACLE initialization or shutdown in progress
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:558)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:553)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:779)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:770)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOSESSKEY(T4CTTIoauthenticate.java:710)
at oracle.jdbc.driver.T4CConnection.authenticateWithPassword(T4CConnection.java:1488)
at oracle.jdbc.driver.T4CConnection.authenticateUserForLogon(T4CConnection.java:1454)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:666)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1041)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:89)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:732)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:648)
at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:335)
... 41 more
2023-01-19 10:49:44,489 DEBUG [org.jgroups.protocols.UNICAST3] (thread-21734,ejb,kc2:server-one) kc2:server-one: closing expired connection for kc1:server-one (120335 ms old) in send_table
2023-01-19 10:49:44,489 DEBUG [org.jgroups.protocols.UNICAST3] (thread-21734,ejb,kc2:server-one) kc2:server-one: closing expired connection for kc1:server-one (120335 ms old) in recv_table
2023-01-19 10:54:43,508 DEBUG [org.jgroups.protocols.UNICAST3] (thread-21744,ejb,kc2:server-one) kc2:server-one: closing expired connection for kc1:server-one (120065 ms old) in send_table
2023-01-19 10:54:43,534 DEBUG [org.jgroups.protocols.UNICAST3] (thread-21744,ejb,kc2:server-one) kc2:server-one: closing expired connection for kc1:server-one (120065 ms old) in recv_table
2023-01-19 10:59:43,577 DEBUG [org.jgroups.protocols.UNICAST3] (thread-21734,ejb,kc2:server-one) kc2:server-one: closing expired connection for kc1:server-one (120224 ms old) in send_table
2023-01-19 10:59:43,577 DEBUG [org.jgroups.protocols.UNICAST3] (thread-21734,ejb,kc2:server-one) kc2:server-one: closing expired connection for kc1:server-one (120224 ms old) in recv_table
2023-01-19 11:04:44,716 DEBUG [org.jgroups.protocols.UNICAST3] (thread-21746,ejb,kc2:server-one) kc2:server-one: closing expired connection for kc1:server-one (120302 ms old) in send_table
2023-01-19 11:04:44,716 DEBUG [org.jgroups.protocols.UNICAST3] (thread-21746,ejb,kc2:server-one) kc2:server-one: closing expired connection for kc1:server-one (120302 ms old) in recv_table
2023-01-19 11:08:45,144 DEBUG [org.jgroups.protocols.UNICAST3] (thread-21747,ejb,kc2:server-one) kc2:server-one: removing expired connection for kc1:server-one (240500 ms old) from send_table
Database was turned on at 2023-01-19 10:41:04. I waited until 2023-01-19 11:34:18 to reconnect, but the application still did not work. It was only a reboot that helped. What could have caused the application not to work after turn on the database?