Clarity

I am not sure why its happening. Have someone noticed this scenario ever?

Because you have done something unsupported and drastic to the database perhaps?

Do you want to explain what you did and why you did it?

There was mismatch of  data on PRJ_BLB_SLICES table. I just setup sequence to start with next value of ID of last record of PRJ_BLB_SLICES table. So nothing drastic in database.

Of course that is drastic! ;-)

The application manages sequences not the database ; the database sequences just help the application with that (the application caches values from the sequences).

There is a stored procedure that drives sequence numbers (I don't recall the name offhand) you could look at the code in that and it might give you an idea why it's behaving like this. But I'm afraid that how to fix something you have broken by direct SQL (DDL even let alone DML) isn't something I can quickly guess at. :-(

Have you tried 'reverting' your changes? (More unsupported drastic SQL I know).

Actually restarting the App might get things in sync again?

If you're on an old versions of Clarity (v13.0), this might be linked to CLRT-7177, NEGATIVE ID IN PRJ_BLB_SLICES

This occurs if the value of

SELECT PRJ_BLB_SLICES_S1.nextval FROM dual;

is greater than 2^31

In such case the conversion of this value to a java integer may result to a negative number.

This has been fixed in later versions of Clarity.

Be careful than in such case you can have index violation in table prj_blb_slices

Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (CLARITY.PRJ_BLB_SLICES_U2) violated

…

Using input:

{resource_id=5002047, unit=0, role_id=5001908, requestID=313, objectID=5029937, sliceDate=2012-09-28T00:00:00, investment_id=5002040, sliceID=-1463912204, slice=0.0}

In such case it is preferrable to restart you sequence at 1 and to rebuild all your slices

Below for Oracle

- 0. check number of records

select count(*) from prj_blb_slices;

-- 1. DROP the sequence

DROP SEQUENCE  PRJ_BLB_SLICES_S1;

-- 2. Recreate the sequence (restart at 1)

CREATE SEQUENCE PRJ_BLB_SLICES_S1 INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 500 ORDER ;

-- 3. Recompile invalid schema objects

DECLARE

  SCHEMA VARCHAR2(200);

  COMPILE_ALL BOOLEAN;

  REUSE_SETTINGS BOOLEAN;

BEGIN

  SCHEMA := 'NIKU';

  COMPILE_ALL := FALSE;

  REUSE_SETTINGS := TRUE;

  DBMS_UTILITY.COMPILE_SCHEMA(

    SCHEMA => SCHEMA,

    COMPILE_ALL => COMPILE_ALL,

    REUSE_SETTINGS => REUSE_SETTINGS

  );

END;

-- 4. Empty slices

TRUNCATE table prj_blb_slices;

-- 5. check number of records - must be 0

select count(*) from prj_blb_slices;

-- 6. Mark slices request for calculation again

Update prj_blb_slicerequests

set request_completed_date = null

where table_name ='PRJ_BLB_SLICES';

commit;

--7. Rebuild indexes on prj_blb_slices

ALTER INDEX prj_blb_slices_N1 REBUILD;

ALTER INDEX prj_blb_slices_U1 REBUILD;

ALTER INDEX prj_blb_slices_U2 REBUILD;

--8. Restart the BG and wait for the end of the timeslicing job

select count(*) from prj_blb_slices;

--Number must be greater or equal that numer at step 0

-- All the slice request should have a completion date. (last run not empty in the user interface)

Hi jeanliv, 

May I ask if your mentioned procedure is applicable also on Clarity ver. 13.3? 

I couldn't find sequence PRJ_BLB_SLICES_S1 in our DB (mentioned in your step 1.) :

My issue is 99% similar you described above, with the same error, however couldn't find negative ID. 

But I think ID is somehow mismatched (when moved DB dump form PROD to TEST system...)

Example of error:

Error message: ORA-00001: unique constraint (NIKU.PRJ_BLB_SLICES_U2) violated

INSERT INTO PRJ_BLB_SLICES ( SLICE_REQUEST_ID, PRJ_OBJECT_ID, SLICE_DATE, SLICE, CREATED_DATE )
VALUES (?, ?, ?, ?, ?)

Using input:
{slice=0.0, objectID=5714869, requestID=5001010, sliceDate=2017-05-06T00:00:00}

Thanks a lot!

Matej 

Hi matej256,

The bug CLRT-7177 should be fixed in Clarity 13.3.  Since the fix of this bug the column slice_id (and the associated sequence PRJ_BLB_SLICES_S1 have been dropped by CA).

I do not think that the problem you're encountering in linked to this particular kind of issue...., could you lease aloborate a bit more or start a new discussion thread ?

Hi Jeanliv, 

I've written my post to this thread which is exactly my case (hope):

Issues in dev environment after DB refresh 

Thanks, 

Matej