CSV Account Data Collector (ADC) hangs after encountering a 'com.hxtt.sql.d1.a' error due to a missing column reference in RSA Identity Governance & Lifecycle

[#1]

Article Number

000036667

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle 
RSA Version/Condition: 7.0.2, 7.1.0, 7.1.1
 

Issue

In the RSA Identity Governance & Lifecycle user interface under Admin > Monitoring, the Status of a CSV Account Data Collector (ADC) collector remains Running and never completes.  When you go to the Run Details screen for that collector (Admin > Monitoring > Run #), the Status of the Collection shows as In Progress and never changes.

The only way to stop the collection is to restart the RSA Identity Governance & Lifecycle application (acm restart).

The following exception is thrown in the aveksaServer.log file ($AVEKSA_HOME/wildfly/standalone/log/aveksaServer.log):

 

06/27/2018 08:09:20.985 INFO (Thread-182) [com.aveksa.client.component.communication.ChangeListHandler] 
Got new change item method=Run ChangeItem[ID=19, type=CollectionRequest, source=49, source-name=TestingADC]  
06/27/2018 08:09:20.988 INFO (ApplyChangesRegularThread-3109) [com.aveksa.client.component.communication.ChangeListHandler] 
STARTING method=ApplyChanges subTask=Acting on a changeItem ChangeItem[ID=19, type=CollectionRequest, source=49, source-name=TestingADC]  
06/27/2018 08:09:20.988 INFO (ApplyChangesRegularThread-3109) [com.aveksa.client.component.DefaultComponentManager] DCM132: Ignoring Event: 
com.aveksa.client.component.event.CollectionRequestEvent[source=com.aveksa.client.component.communication.DefaultCommunicationManager@1815f610] 
06/27/2018 08:09:21.023 ERROR (ApplyChangesRegularThread-3109) [SystemErr] Exception in thread "ApplyChangesRegularThread-3109" 
06/27/2018 08:09:21.028 ERROR (ApplyChangesRegularThread-3109) [SystemErr] java.lang.StackOverflowError 
06/27/2018 08:09:21.030 ERROR (ApplyChangesRegularThread-3109) [SystemErr] at Qu(Unknown Source) 
06/27/2018 08:09:21.031 ERROR (ApplyChangesRegularThread-3109) [SystemErr] at com.hxtt.sql.d1.a(Unknown Source)  ... 
06/27/2018 08:09:21.895 ERROR (ApplyChangesRegularThread-3109) [SystemErr] at com.hxtt.sql.d1.a(Unknown Source) 
06/27/2018 08:09:21.895 ERROR (ApplyChangesRegularThread-3109) [SystemErr] at com.hxtt.sql.d1.a(Unknown Source)  
06/27/2018 08:09:21.897 ERROR (ApplyChangesRegularThread-3109) [com.aveksa.client.component.communication.ChangeListHandler] 
method=UncaughtException Thread=Thread[ApplyChangesRegularThread-3109,5,ChangeApplyingThreadGroup]  
java.lang.StackOverflowError  at com.hxtt.sql.d1.a(Unknown Source)  at com.hxtt.sql.d1.a(Unknown Source)  
at com.hxtt.sql.d1.a(Unknown Source)

Please refer to RSA Knowledge Base Article  000030327 -- Artifacts to gather in RSA Identity Governance & Lifecycle to find the location of the aveksaServer.log file for your specific deployment. 
 

Cause

The HXTT error:
 

com.hxtt.sql.d1.a 
 

occurs when a SQL query in the collector definition is performing string manipulation on a missing/non-existent column in the data source (CSV file). This is to be expected. However, this error also occurs when an alias column name is the same as another name in the query. This is a change in behavior from previous versions of the HXTT driver.

For example, the SQL Query below defined under User Account Mappings Data Query refers to column UserID. If this column does not exist in the CSV data source, the error will occur and the collection will hang until the RSA Identity Governance & Lifecycle application is restarted. The error will also occur because this query uses the UserID name in two places: the column name and the alias name.
 

SELECT DISTINCT 
ACCOUNTNAME AS Account_ID, 
CASE WHEN (LEN(UserID) = 8 AND ((RIGHT(UserID,1) ='a') OR RIGHT(UserID,1) ='A')) THEN LEFT(UserID,7) 
ELSE UserID END AS UserID
FROM T_MY_TEST_ACCOUNTS_USERS

The latest version of the third party HXTT driver that is integrated with RSA Identity Governance & Lifecycle for use with the CSV collectors has changed the implementation for selecting columns when the columns are condition-based.
 

Resolution

This issue is resolved in the following RSA Identity Governance & Lifecycle versions and patch levels:

• RSA Identity Governance & Lifecycle 7.1.1 P06
• RSA Identity Governance & Lifecycle 7.2.0

 The fix updates the HXTT driver.
 

Workaround

If the column name does not exist in the data source, remove it from the query or correct the column name (for example it may exist but be misspelled.)

For queries where column names and alias names are the same, the HXTT support team has advised that existing SQL queries need to be modified as follows:

EXAMPLE: 

Initial query:

In the example below the column name=UserID and the alias name=UserID:

SELECT DISTINCT 
ACCOUNTNAME AS Account_ID, 
CASE WHEN (LEN(UserID) = 8 AND ((RIGHT(UserID,1) ='a') OR RIGHT(UserID,1) ='A')) THEN LEFT(UserID,7) 
ELSE UserID END AS UserID
FROM T_MY_TEST_ACCOUNTS_USERS

Workaround options:

To resolve the issue, either change the alias name (in this example the name is changed from UserID to User_ID😞

SELECT DISTINCT 
ACCOUNTNAME AS Account_ID, 
CASE WHEN (LEN(UserID) = 8 AND ((RIGHT(UserID,1) ='a') OR RIGHT(UserID,1) ='A')) THEN LEFT(UserID,7) 
ELSE UserID END AS User_ID
FROM T_MY_TEST_ACCOUNTS_USERS

or remove the alias name as in the query example below:

SELECT DISTINCT 
ACCOUNTNAME AS Account_ID, 
CASE WHEN (LEN(UserID) = 8 AND ((RIGHT(UserID,1) ='a') OR RIGHT(UserID,1) ='A')) THEN LEFT(UserID,7) 
ELSE UserID END 
FROM T_MY_TEST_ACCOUNTS_USERS

Notes