Data runs show error: Exception while executing filter query RSA Identity Governance & Lifecycle 6.8 and above

[#1]

Article Number

000036811

Applies To

RSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 6.8+, 7.x
 

Issue

In RSA Identity Governance and Lifecycle, when examining the Data Runs (Admin > Monitoring > Data Runs), the following error may be reported: 

Error - com.aveksa.server.filter.FilterException: Exception while executing filter query

Image description

On examination, the aveksaServer.log will have the following error.
 

09/19/2018 15:01:08.454 ERROR (default task-40) [com.aveksa.server.filter.ObjectListFilter] Exception while executing filter query  com.aveksa.server.db.PersistenceException: select tasks0_.RUN_ID as RUN16_794_1_, tasks0_."ID" as ID1_1_, tasks0_."ID" as ID1_796_0_, tasks0_."TYPE" as TYPE2_796_0_, tasks0_."STATUS" as STATUS3_796_0_, tasks0_."SOURCE" as SOURCE4_796_0_, tasks0_."SOURCE_TYPE" as SOURCE5_796_0_, tasks0_."SOURCE_CONTAINER" as SOURCE6_796_0_, tasks0_."INPUT_DATA_REF" as INPUT7_796_0_, tasks0_."START_TIME" as START8_796_0_, tasks0_."END_TIME" as END9_796_0_, tasks0_."XMLDATA" as XMLDATA10_796_0_, tasks0_."IS_REPROCESS" as IS11_796_0_, tasks0_."USER_ID" as USER12_796_0_, tasks0_."PREVIOUS_EXECUTION_ID" as PREVIOUS13_796_0_, tasks0_."INT_1" as INT14_796_0_, tasks0_."STR_1" as STR15_796_0_, tasks0_.RUN_ID as RUN16_796_0_ from T_DATA_RUN_TASKS tasks0_ where tasks0_.RUN_ID=? 
...
Caused by: java.sql.SQLException: Invalid character encountered in
   at oracle.sql.CharacterSet.failUTFConversion(CharacterSet.java:2879)
   at oracle.sql.CharacterSetAL32UTF8.toString(CharacterSetAL32UTF8.java:186)
   at oracle.xdb.XMLType.processString(XMLType.java:3300)

Cause

The issue occurs because the XML data in the AVUSER.T_DATA_RUN_TASKS table contains data that is not able to be displayed in the UI. 

This may occur if the XML data is too large, or contains illegal characters, as a result of defect ACM-63383.

Resolution

The solution for this issue comes in two parts;

1. Upgrade the RSA Identity Governance & Lifecycle to a version where defect ACM-63383 has been fixed.
2. Identify all the rows in table T_DATA_RUN_TASKS, where the column XMLDATA contains faulty XML data.

Once identified, engage RSA Customer Support by logging a new case, and they will verify the results and provide the steps to correct the faulty XML data.

 

Upgrade RSA Identity Governance & Lifecycle to a version where defect ACM-63383 has been fixed

Defect ACM-63383 had been fixed in version 7.0.2, so we recommend that RSA Identity Governance & Lifecycle be upgraded to at least this version.

However, for lower versions we recommend that the latest patch be applied.  Download and apply the latest patch for your version as per article 000033845 - How to download patch files for RSA Identity Governance & Lifecycle from RSA Link.

 

Identify all the rows in table T_DATA_RUN_TASKS, where the column XMLDATA contains faulty XML data

There is an additional problem in that once the faulty XML data exists, it needs to be manually removed.  This is OK, because for completed Data Runs, the XML data is no longer needed.  Also, the Data Run doesn't need to be deleted, so this means only 1 table is updated.

1. Download the attached SQL script file named DATARUNTASKS_check.sql, and upload to the /home/oracle directory.
2. Login as oracle, where you should be in the /home/oracle directory.
3. Connect to the database using SQL*Plus:

sqlplus avuser/<password>

4. Run the SQL script file, to detect how many rows in table T_DATA_RUN_TASKS has data in column XMLDATA that are likely to be causing the error in the UI.

SQL> @DATARUNTASKS_check.sql

5. The results will look something like the following:

Starting T_DATA_RUN_TASKS.XMLDATA bad XML check
===============================================
ERROR DETECTED! on T_DATA_RUN_TASKS.XMLDATA XML column
Record identited by ID=237, RUN_ID=194
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
-------------------------------------------------------
ERROR DETECTED! on T_DATA_RUN_TASKS.XMLDATA XML column
Record identited by ID=3806, RUN_ID=2492
ORA-31196: XML nodes over 64K in size cannot be printed
-------------------------------------------------------
T_DATA_RUN_TASKS.XMLDATA bad XML check completed
2 corruptions detected

6. Engage RSA Customer Support by logging a new case, and pass them the output from Step 5 above, and refer them to article 000036811.  The engineer will then review the article and provide the detailed steps to correct the faulty XML data.

Workaround

There is no workaround for the faulty XML data in table T_DATA_RUN_TASKS.

The defect must be corrected, and then the faulty XML data removed for functionality to be restored.