Database Error noticed in arcotadmin.log after refreshing the cache from global and master adminconsole.
2022-06-15 12:03:27,063 HKT : [https-jsse-nio2-0.0.0.0-8443-exec-5] : ERROR : cache.db.CacheRefreshService : Internal error: Unable to retrieve (system or scopeall) cache refresh events.
2022-06-15 12:03:27,064 HKT : [https-jsse-nio2-0.0.0.0-8443-exec-5] : ERROR : web.cache.CacheRefreshManager : [ARCOTORGADMIN] : [XXXXXX-XXXXXSECURESITE] : [51] : [Arcot Admin Console] : [Admin Console] :[N/A] : Database error
com.arcot.common.api.ArcotCommonException: Database error
at com.arcot.common.cache.db.CacheRefreshService.retrieveInProgresSystemScopeAllEvent(CacheRefreshService.java:260) ~[arcot-common-2.3.jar:?]
at com.arcot.common.cache.CacheRefreshUtil.isCacheRefreshAllowed(CacheRefreshUtil.java:29) ~[arcot-common-2.3.jar:?]
at com.arcot.adminconsole.admin.web.cache.CacheRefreshManager.refreshCache(CacheRefreshManager.java:339) [admin-resourcepack-2.0.jar:?]
at com.arcot.adminconsole.admin.web.cache.CacheRefreshConfigurationController.onSubmit(CacheRefreshConfigurationController.java:41) [admin-resourcepack-2.0.jar:?]
at org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:272) [spring-webmvc-3.1.0.RELEASE.jar:3.1.0.RELEASE]
Caused by: java.sql.SQLException: Error: executeQueryForObject returned too many results.
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:124) ~[ibatis-2.3.4.726.jar:?]
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518) ~[ibatis-2.3.4.726.jar:?]
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493) ~[ibatis-2.3.4.726.jar:?]
Release : 9.1
Component : Admin Console
The issue was with table (ARPFCMNCACHEREFRESHSTATUS), when the cache refresh got triggered the following sql query was executed .
SELECT REQUESTID, INSTANCEID, TIMESTAMP, EVENTTYPE FROM ARPFCMNCACHEREFRESHEVENT WHERE EVENTTYPE NOT IN ('SCOPE', 'SELECTED_ORG') AND REQUESTID IN ( SELECT REQUESTID FROM ARPFCMNCACHEREFRESHSTATUS WHERE STATUS = 'IN_PROGRESS' )
This query was able to fetch out more than one records, then it resulted in the error which was seen in the admin UI logs .
To fix this , update the records 'IN_PROGRESS' to 'SUCCESS'.
SELECT REQUESTID, INSTANCEID, TIMESTAMP, EVENTTYPE FROM ARPFCMNCACHEREFRESHEVENT WHERE EVENTTYPE NOT IN ('SCOPE', 'SELECTED_ORG') AND REQUESTID IN ( SELECT REQUESTID FROM ARPFCMNCACHEREFRESHSTATUS WHERE STATUS = 'IN_PROGRESS' to 'SUCCESS' )