Expand my Community achievements bar.

SOLVED

broadlog id primary sequence exhaustion causing duplicate key error

Avatar

Level 4

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

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Level 1

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

View solution in original post

4 Replies

Avatar

Community Advisor

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) :

Amine_Abedour_1-1701519091175.png

Br,

Amine

 

Avatar

Level 4

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

 

 

Avatar

Correct answer by
Level 1

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

Avatar

Level 1

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])