Overview

While troubleshooting Community Manager Reports (CMR), you find that an ETL (Extract, Transform, Load) process is not being completed. In the Social Business Software (SBS) logs, you see the following error message:

Unique constraint (ANALYTICSDB.JVDW_USERREL_UK_IDX) violated.

Solution

This happens when the process has generated a duplicate value in a restricted field. The error can commonly be found when a program attempts to insert a duplicate row in a table.

You should first confirm the issue and then modify the database accordingly.

Confirm the issue

In the Admin Console, navigate to System > Settings > Analytics > Data Load > ETL History to view the status and validate the error messages being logged.

1. Add the logging overrides to get detailed logs for analytics in CMR.
   1. From the admin console, navigate to Admin Console > System > Management > Logging Management.
   2. Input each of the following in the Logger Name field, select Debug from the level drop-down menu and click Apply Override:
      • com.jivesoftware.community.analytics.chart
      • com.jivesoftware.community.analytics.task
      • com.jivesoftware.community.analytics.etl

      

2. Run the ETL process manually to tail and collect the SBS logs.
3. Collect the Social Business Software (SBS) logs.
   1. From the same Logging page, click on Log Viewer tab.
   2. Select SBS from the Log File drop-down menu.

      

4. An error message similar to the following appears in the logs:

   unique constraint (ANALYTICSDB.JVDW_USERREL_UK_IDX) violated

Modify the database

To resolve the issue, you will need to identify the restricted field that the duplicate value is being inserted into by manually analyzing the database. The error message should provide an indication of the restricted field and the database table on which the action is being performed. Please note that Jive Support doesn't have access to your database as an On-premise customer. Below are some sample queries that you may utilize to start the investigation.

1. Take a backup of your entire database with pg_dump commands or by copying the table with another name. In Postgres, the tables can be backed up with the following commands:

   create table jivedw_container_bck (like jivedw_container);
   insert into jivedw_container_bck (select * from jivedw_container);
   create table jivedw_container_stage_bck (like jivedw_container_stage);
   insert into jivedw_container_stage_bck (select * from jivedw_container_stage);

2. Use SQL queries to find the duplicate data in the Analytics database.
   Important note: These are sample queries and the values may vary for different instances. Modify the values according to the database that you are working on.

   SELECT * FROM (SELECT userID, relatedUserID, graphID, COUNT(*) as indexCount from jiveUserRel GROUP BY userID, relatedUserID, graphID ORDER BY indexCount DESC) counts WHERE rownum <= 5 ORDER BY rownum;
   
   select count (*) FROM jiveUserRel WHERE relationshipid NOT IN (SELECT MAX(relationshipid) FROM jiveUserRel GROUP BY userID,relatedUserID,relnshipTypeID,state,retirementDate HAVING MAX(relationshipid) IS NOT NULL);

3. Once the bad data has been identified, remove it from the database, and run the following maintenance queries:

   truncate jivedw_user_rel;
   truncate jivedw_user_rel_stage;
   UPDATE jivedw_etl_task SET lastprocmodificationdate=0 WHERE
   classname='com.jivesoftware.community.analytics.etl.impl.UserRelationshipETL';
   

4. If you want to rollback, then drop the tables and rename the backup ones to the original names:
   

   drop table jivedw_container;
   drop table jivedw_container_stage;
   alter table jivedw_container_bck rename to jivedw_container;
   alter table jivedw_container_stage_bck rename to jivedw_container_stage;

5. (If step 4 wasn't performed) If the backups are no longer necessary at the end of the process, drop the backup tables using the following queries:
   

   drop table jivedw_container_bck;
   drop table jivedw_container_stage_bck;