broadlog id primary sequence exhaustion causing duplicate key error | Community
Skip to main content
S
Shrutii
Level 4
December 2, 2023
Solved

broadlog id primary sequence exhaustion causing duplicate key error

  • December 2, 2023
  • 2 replies
  • 1826 views

Hi all,

 

 

broadlog id primary sequence exhaustion causing duplicate key error in our Adobe V7 instance

I am looking for solution to fix this issue

Please help if anyone has faced and got this fixed

 

in our case, we have on prem instance so adobe support is not able to help much

they are saying u need to put your sequence in negative sequence

but how and where we need to do this, they have no idea

 

 

please please help me provide solutions

 

 

 

 

 

 

 

 

 

 

Re

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Somyash

Hi ,

The simplest solution is to reduce the retention time of broadlogs.

By default the retention time is 180 days                                                                                               

When the sequence reaches more than 2 billion (2,147,483,647 is the exact number), it goes back to zero,  the best solution is to switch to negative IDs, starting from -2,147,483,647.

I have applied and its working for me since 5years

In a very simple way , without extending your schema and changing anything else, you can simply run an ALTER Table command to have your sequence which generates IDs to restart with -2147483647

 

The command would have to run on the DB and the syntax would be something like :


ALTER sequence <Sequence name>
MINVALUE -2147483647
MAXVALUE 2147483647
RESTART WITH -2147483647 CACHE -2147483647 CYCLE;


ALTER SEQUENCE <Sequence name> INCREMENT BY 1;

 

This will reset the Sequence to restart with -2147483647 and will start assigning IDs incrementally after -2147483647                                                                                                  Try this and let me k now if it works

2 replies

Amine_Abedour
Community Advisor
Amine_AbedourCommunity Advisor
Community Advisor
December 2, 2023

Hello @shrutii,

 

The simplest solution is to reduce (if possible) the retention time of broadlogs. By default the retention time is 180 days. You can reduce it to just 90 days if there is no functional need to keep 6 months of logs.

 

Otherwise if you would like to go in the negative sequence route, check if the sequence used by your broadlog table has no minimum value (assuming your backend database is a postgreSQL one) :

Br,

Amine

 

Amine ABEDOUR
    S
    Shruti1
    Level 4
    December 2, 2023

    hi amine

    please help to provide info how  can we put our sequence in negative route??

     

    i am am unable to find any doc

    eaee help to tel me steps in detail

    even we raised adibe ticket but no one from them know this

     

     

    S
    SomyashAccepted solution
    December 3, 2023

    Hi ,

    The simplest solution is to reduce the retention time of broadlogs.

    By default the retention time is 180 days                                                                                               

    When the sequence reaches more than 2 billion (2,147,483,647 is the exact number), it goes back to zero,  the best solution is to switch to negative IDs, starting from -2,147,483,647.

    I have applied and its working for me since 5years

    In a very simple way , without extending your schema and changing anything else, you can simply run an ALTER Table command to have your sequence which generates IDs to restart with -2147483647

     

    The command would have to run on the DB and the syntax would be something like :


    ALTER sequence <Sequence name>
    MINVALUE -2147483647
    MAXVALUE 2147483647
    RESTART WITH -2147483647 CACHE -2147483647 CYCLE;


    ALTER SEQUENCE <Sequence name> INCREMENT BY 1;

     

    This will reset the Sequence to restart with -2147483647 and will start assigning IDs incrementally after -2147483647                                                                                                  Try this and let me k now if it works

      S
      Somyash
      December 4, 2023

      As best practice , you can use below code in JS activity in workflow to get the

      min and max of your sequence first

       

       


      var cnx=application.getConnection()
      var seqname="xtknewid"; //var seqname="nmsbroadlogid
      //Get the sequence info
      var request = "SELECT * FROM " + seqname + ";";
      var response = cnx.query(request);
      var result=";
      for each (var row in response)

      logInfo("sequence: " + row[0] + "|last value: "
      + row[1]+ "|start value:" + row[2]+"|Increment by:" + row[3]+"|max value:"+row[4]+ "|min value:" row[5]+ "| cache value:" +
      row[6])

       

        Terms & ConditionsAccessibility statement

        Loading footer...