Advanced Authentication Admin console cache refresh error

Products

CA Strong Authentication CA Advanced Authentication CA Risk Authentication CA Advanced Authentication - Risk Authentication (RiskMinder / RiskFort) CA Advanced Authentication - Strong Authentication (AuthMinder / WebFort)

Issue/Introduction

Database Error noticed in arcotadmin.log after refreshing the cache from global and master admin console.

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 : [XXXXXXXXXXXX] : [XXXXXX-XXXXXXXXXXX] : [51] : [XXXXX XXXXX 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:?]

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

Environment

Release : Advanced Authentication 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' )