Good morning.
I have the following problem:
I have created a User Store that points to an Oracle database that has the same schema as wso2 is 6.1. 0, and the users are listed correctly, but from one moment to another, like after 4 hours, the connection is lost and the user option is left thinking to show the users, to solve it, I have to restart the wso2 is and it is solved, or the other option that is faster is to pass the User store created Disable and then Enable, in this way the connection of this user store is refreshed and the incident is solved, but this is not the right thing to do.
WSO2 IS 6.1.0
User Store Manager Class: org.wso2.carbon.user.core.jdbc.UniqueIDJDBCUserStoreManager
oracle.jdbc.driver.OracleDriver
Error: TID: [-1234] [] [2024-03-14 08:56:00,711] [830f4183-5d47-4928-87ce-8148de7363dc] ERROR {org.wso2.carbon.identity.user.store.count.UserStoreCountService} - Error while getting user count from user store domain : CGR org.wso2.carbon.identity.user.store.count.exception.UserStoreCounterException: Error occurred while retrieving user count at org.wso2.carbon.identity.user.store.count.UserStoreCountService.getUserCountWithClaims(UserStoreCountService.java:212) at org.wso2.carbon.identity.user.store.count.UserStoreCountService.countUsers(UserStoreCountService.java:58) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:212) at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:117) at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40) at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:110) at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:180) at org.apache.axis2.transport.local.LocalTransportReceiver.processMessage(LocalTransportReceiver.java:170) at org.apache.axis2.transport.local.LocalTransportReceiver.processMessage(LocalTransportReceiver.java:82) at org.wso2.carbon.core.transports.local.CarbonLocalTransportSender.finalizeSendWithToAddress(CarbonLocalTransportSender.java:45) at org.apache.axis2.transport.local.LocalTransportSender.invoke(LocalTransportSender.java:77) at org.apache.axis2.engine.AxisEngine.send(AxisEngine.java:442) at org.apache.axis2.description.OutInAxisOperationClient.send(OutInAxisOperation.java:449) at org.apache.axis2.description.OutInAxisOperationClient.executeImpl(OutInAxisOperation.java:235) at org.apache.axis2.client.OperationClient.execute(OperationClient.java:149) at org.wso2.carbon.identity.user.store.count.stub.UserStoreCountServiceStub.countUsers(UserStoreCountServiceStub.java:1980) at org.wso2.carbon.user.mgt.ui.UserStoreCountClient.countUsers(UserStoreCountClient.java:75) at org.apache.jsp.user.user_002dmgt_jsp._jspService(user_002dmgt_jsp.java:409) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:466) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:379) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:327) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.wso2.carbon.ui.JspServlet.service(JspServlet.java:207) at org.wso2.carbon.ui.TilesJspServlet.service(TilesJspServlet.java:80) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.eclipse.equinox.http.helper.ContextPathServletAdaptor.service(ContextPathServletAdaptor.java:37) at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:128) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:68) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.wso2.carbon.tomcat.ext.servlet.DelegationServlet.service(DelegationServlet.java:68) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:661) at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:537) at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:480) at org.eclipse.equinox.http.servlet.internal.RequestDispatcherAdaptor.include(RequestDispatcherAdaptor.java:37) at org.eclipse.equinox.http.helper.ContextPathServletAdaptor$RequestDispatcherAdaptor.include(ContextPathServletAdaptor.java:369) at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:994) at org.apache.jasper.runtime.PageContextImpl.include(PageContextImpl.java:485) at jdk.internal.reflect.GeneratedMethodAccessor169.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.apache.tiles.jsp.context.JspUtil.doInclude(JspUtil.java:87) at org.apache.tiles.jsp.context.JspTilesRequestContext.include(JspTilesRequestContext.java:88) at org.apache.tiles.jsp.context.JspTilesRequestContext.dispatch(JspTilesRequestContext.java:82) at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:465) at org.apache.tiles.jsp.taglib.InsertAttributeTag.render(InsertAttributeTag.java:140) at org.apache.tiles.jsp.taglib.InsertAttributeTag.render(InsertAttributeTag.java:117) at org.apache.tiles.jsp.taglib.RenderTagSupport.execute(RenderTagSupport.java:171) at org.apache.tiles.jsp.taglib.RoleSecurityTagSupport.doEndTag(RoleSecurityTagSupport.java:75) at org.apache.tiles.jsp.taglib.ContainerTagSupport.doEndTag(ContainerTagSupport.java:80) at org.apache.jsp.admin.layout.template_jsp._jspx_meth_tiles_005finsertAttribute_005f7(template_jsp.java:781) at org.apache.jsp.admin.layout.template_jsp._jspService(template_jsp.java:444) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:466) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:379) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:327) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.wso2.carbon.ui.JspServlet.service(JspServlet.java:207) at org.wso2.carbon.ui.TilesJspServlet.service(TilesJspServlet.java:80) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.eclipse.equinox.http.helper.ContextPathServletAdaptor.service(ContextPathServletAdaptor.java:37) at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:128) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:68) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.wso2.carbon.tomcat.ext.servlet.DelegationServlet.service(DelegationServlet.java:68) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:661) at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:425) at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:357) at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:294) at org.eclipse.equinox.http.servlet.internal.RequestDispatcherAdaptor.forward(RequestDispatcherAdaptor.java:30) at org.eclipse.equinox.http.helper.ContextPathServletAdaptor$RequestDispatcherAdaptor.forward(ContextPathServletAdaptor.java:362) at org.apache.tiles.servlet.context.ServletTilesRequestContext.forward(ServletTilesRequestContext.java:198) at org.apache.tiles.servlet.context.ServletTilesRequestContext.dispatch(ServletTilesRequestContext.java:185) at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:419) at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:370) at org.wso2.carbon.ui.action.ActionHelper.render(ActionHelper.java:52) at org.wso2.carbon.ui.TilesJspServlet.service(TilesJspServlet.java:101) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.eclipse.equinox.http.helper.ContextPathServletAdaptor.service(ContextPathServletAdaptor.java:37) at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:128) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:68) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.wso2.carbon.tomcat.ext.servlet.DelegationServlet.service(DelegationServlet.java:68) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.owasp.csrfguard.CsrfGuardFilter.doFilter(CsrfGuardFilter.java:88) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.catalina.filters.HttpHeaderSecurityFilter.doFilter(HttpHeaderSecurityFilter.java:129) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.wso2.carbon.tomcat.ext.filter.CharacterSetFilter.doFilter(CharacterSetFilter.java:65) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.catalina.filters.HttpHeaderSecurityFilter.doFilter(HttpHeaderSecurityFilter.java:129) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) at org.wso2.carbon.identity.context.rewrite.valve.TenantContextRewriteValve.invoke(TenantContextRewriteValve.java:119) at org.wso2.carbon.identity.context.rewrite.valve.OrganizationContextRewriteValve.invoke(OrganizationContextRewriteValve.java:115) at org.wso2.carbon.tomcat.ext.valves.SameSiteCookieValve.invoke(SameSiteCookieValve.java:38) at org.wso2.carbon.identity.cors.valve.CORSValve.invoke(CORSValve.java:83) at org.wso2.carbon.identity.authz.valve.AuthorizationValve.invoke(AuthorizationValve.java:167) at org.wso2.carbon.identity.auth.valve.AuthenticationValve.invoke(AuthenticationValve.java:118) at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:114) at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:49) at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:75) at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:152) at org.wso2.carbon.extension.identity.x509Certificate.valve.X509CertificateAuthenticationValve.invoke(X509CertificateAuthenticationValve.java:59) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:670) at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:63) at org.wso2.carbon.tomcat.ext.valves.RequestEncodingValve.invoke(RequestEncodingValve.java:49) at org.wso2.carbon.tomcat.ext.valves.RequestCorrelationIdValve.invoke(RequestCorrelationIdValve.java:137) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:928) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1794) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: org.wso2.carbon.user.core.UserStoreException: Error while executing the SQL SELECT COUNT(UM_USER_NAME) AS RESULT FROM UM_USER WHERE UM_USER_NAME LIKE ? AND UM_TENANT_ID = ? at org.wso2.carbon.user.core.common.AbstractUserStoreManager.callSecure(AbstractUserStoreManager.java:258) at org.wso2.carbon.user.core.common.AbstractUserStoreManager.getUserCountWithClaims(AbstractUserStoreManager.java:10174) at org.wso2.carbon.identity.user.store.count.UserStoreCountService.getUserCountWithClaims(UserStoreCountService.java:204) ... 146 more Caused by: java.security.PrivilegedActionException: java.lang.reflect.InvocationTargetException at java.base/java.security.AccessController.doPrivileged(Native Method) at org.wso2.carbon.user.core.common.AbstractUserStoreManager.callSecure(AbstractUserStoreManager.java:239) ... 148 more Caused by: java.lang.reflect.InvocationTargetException at jdk.internal.reflect.GeneratedMethodAccessor230.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.wso2.carbon.user.core.common.AbstractUserStoreManager$2.run(AbstractUserStoreManager.java:242) ... 150 more Caused by: org.wso2.carbon.user.core.UserStoreException: Error while executing the SQL SELECT COUNT(UM_USER_NAME) AS RESULT FROM UM_USER WHERE UM_USER_NAME LIKE ? AND UM_TENANT_ID = ? at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.doCountUsersWithClaims(JDBCUserStoreManager.java:3976) at org.wso2.carbon.user.core.common.AbstractUserStoreManager.countUsersWithClaims(AbstractUserStoreManager.java:6558) at org.wso2.carbon.user.core.common.AbstractUserStoreManager.getCountUsers(AbstractUserStoreManager.java:10583) at org.wso2.carbon.user.core.common.AbstractUserStoreManager.getUserCountWithClaims(AbstractUserStoreManager.java:10213) ... 154 more Caused by: java.sql.SQLRecoverableException: Closed Connection at oracle.jdbc.driver.PhysicalConnection.setAutoCommit(PhysicalConnection.java:2067) at jdk.internal.reflect.GeneratedMethodAccessor49.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:131) at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109) at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79) at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109) at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) at com.sun.proxy.$Proxy53.setAutoCommit(Unknown Source) at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.getDBConnection(JDBCUserStoreManager.java:1306) at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.doCountUsersWithClaims(JDBCUserStoreManager.java:3946)
It seems you're experiencing an issue with WSO2 Identity Server (IS) 6.1.0 where the connection to a user store backed by an Oracle database gets lost after a certain period of inactivity, requiring a restart or a disable/enable cycle of the user store to refresh the connection.
Check the database connection pool settings in WSO2 IS. Specifically, look for settings related to idle timeouts, max lifetime, and validation queries.
To check current idle timeouts,