Advanced Authentication Admin console cache refresh error

Products

• CA Strong Authentication
• CA Advanced Authentication
• CA Risk Authentication

Issue/Introduction

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:?]

        
        

Environment

Release : 9.1

Component : Admin Console

Cause

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 .

Resolution

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'  )