Clarity

Expand all | Collapse all

15.2 Upgrade Fails

Jump to Best Answer
  • 1.  15.2 Upgrade Fails

    Posted 07-13-2017 09:37 AM
    Hi  

    We are curretly upgrading from 14.2 to 15.2. install.sh script fails with the below error.  Its because "51762" ID is already available in CMN_CAPTIONS table and install script is trying to insert a row with the same ID.   I assumed it uses CMN_CAPTIONS_S1 sequence and executed CMN_ID_SP Procedure to make sure the nextval is the max(id)+1 and re-ran the install script. Still it failed with the same error.  Can someone please help?

         BEGIN      -- CREATE A FLAG IN THE CMN_OPTIONS TO ENABLE/DISABLE CURRENCY CONVERSION IN HIERARCHY PAGE       INSERT INTO CMN_OPTIONS (ID,OPTION_CODE,IS_ACTIVE,IS_SYSTEM,CREATED_DATE,CREATED_BY,LAST_UPDATED_DATE,LAST_UPDATED_BY) VALUES (51762,'CURRENCY_CONVERSION_REQUIRED',1,1,SYSDATE,1,SYSDATE,1);        INSERT INTO CMN_OPTION_VALUES (ID,OPTION_ID,COMPONENT_ID,USER_ID,VALUE,CREATED_DATE,CREATED_BY,LAST_UPDATED_DATE,LAST_UPDATED_BY) VALUES (51542,51762,NULL,NULL,'1',SYSDATE,1,SYSDATE,1);      END;   com.ca.clarity.jdbc.oraclebase.ddc: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (NIKU.CMN_OPTIONS_PK) violated  ORA-06512: at line 3

     

    Nika_Hadzhikidi

    SumanPramanik



  • 2.  Re: 15.2 Upgrade Fails

    Posted 07-13-2017 09:46 AM
    Its because "51762" ID is already available in CMN_OPTIONS table and install script is trying to insert a row with the same ID.   I assumed it uses CMN_OPTIONS_S1 sequence and executed CMN_ID_SP Procedure to make sure the nextval is the max(id)+1 and re-ran the install script. Still it failed with the same error.


  • 3.  Re: 15.2 Upgrade Fails
    Best Answer

    Posted 07-13-2017 09:50 AM

    Hi Augustin,

     

     

    This is a very rare bug I had previously before with another customer and validated a solution. Basically as you said it tried to insert a new option with same ID as a one existing before.

     

    Workaround:

     

    1. Backup tables CMN_OPTIONS and CMN_OPTION_VALUES
    2. Run the 2 stored procedures one by one, ensure it's successful:

    begin

    cmn_option_values_del_sp('E2E_TSV_LINKS_ENABLE');

    end;

     

    begin

    cmn_options_del_sp('E2E_TSV_LINKS_ENABLE');

    end;

    1. Ensure the wrong values are gone by checking with queries:

    select * from cmn_options where id =51762

    select * from cmn_option_values where option_id =51762

     

    You should have no results on both queries

     

    1. Now readd the value by running the two stored proecdures 1 by 1:

    begin

    cmn_options_ins_sp ('E2E_TSV_LINKS_ENABLE','Enable TSV links for Export to Excel','Enable TSV links for Export to Excel',1,1,1);

    end;

     

    begin

    cmn_option_values_ins_sp('E2E_TSV_LINKS_ENABLE',NULL,NULL,1);

    end;

     

    1. Double check to ensure it has been inserted with a correct id (5000000+) and no 51762 in the system:

    select * from cmn_options where option_code = 'E2E_TSV_LINKS_ENABLE'

    1 row should be returned with ID >5000000

    select * from cmn_options where id =51762

    No rows should be returned.

     

    1. If it is all correct, proceed with the upgrade as usual.
    2. Consider using this procedure proactively for all your other environments, since all of them will have the issue.

     

     

    I hope this helps - please let me know how it goes.

     

    Thank you -Nika



  • 4.  Re: 15.2 Upgrade Fails

    Posted 07-14-2017 02:52 AM

    Hi Nika

     

    Thank you so much for your quick response. Let me try this and post you the updates.

     

    Regards

    Augustin



  • 5.  Re: 15.2 Upgrade Fails

    Posted 07-17-2017 07:54 AM

    Hi Nika

     

    I tried your steps and I was able to successfully upgrade to 15.2. Thanks a ton. You are rocking as usual.