Time Slicing job is failing with ORA-00001 on bad data

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

 Time Slicing job is failing few times per day the error - ORA-00001: unique constraint (NIKU.ODF_SSL_5000000_U1) violated, full error message from bg-ca.log:

 

ERROR 2021-05-18 07:07:43,848 [Dispatch Time Slicing : <server> (tenant=clarity)] niku.blobcrack (EBB60D0F-FAF6-4451-9751-A20E9941DD5D:Time Slicing) Error while inserting slices.

com.niku.union.persistence.PersistenceException: 
SQL error code: 1
Error message: ORA-00001: unique constraint (NIKU.ODF_SSL_5000000_U1) violated

Executed:
 INSERT INTO ODF_SSL_5000000
             ( SLICE_REQUEST_ID, PRJ_OBJECT_ID, START_DATE, FINISH_DATE, SLICE, CREATED_DATE 
              ) 
             VALUES (?, ?, ?, ?, ?, ? ) 

 Note the time slice request table ODF_SSL_5000000 may vary.

Environment

Release : Any

Cause

This can be caused by a bad data in one of the time slice records. At this time we do not have information on how the corruption happens. 

Resolution

1. Run this on your database (note we are using the exact slice request table). If in doubt on the table, compare the error message to the slice request table in PRJ_BLB_SLICEREQUESTS table in the database
   

   select * from ODF_SSL_5000000

2. Look for any bad data, i.e. Nulls. This is the most common issue with the data. Example on how a bad record would look:
   
3. If you notice any unexpected null records, open the TSV in UI
4. Manually update the slice to reset it for this record and recreate the BLOB

 

Note that the impact of this issue will be low, as the Timeslicing job will process correctly all the other records but the corrupted one