cancel
Showing results for 
Search instead for 
Did you mean: 

Database Counter Verification

djboyd4715
Participant
0 Kudos
217

We have run into an issue in which we are getting pk issues with, in this case, dbo.address. It turns out that the "counter" is not setup correctly and as such was making use of already consumed PK's.

Looking to see if someone has or knows of a counter verification tool which can look at the max PK, check the counter to see if they match or if the counter is behind and then suggest or provide the value of the counter that needs to be used to avoid duplicate PK usage.

Accepted Solutions (0)

Answers (1)

Answers (1)

mansurarisoy
Contributor
0 Kudos

The following SQL gives you the counter value of the PK for addresses table

SELECT * FROM numberseries WHERE serieskey = 'pk_23'

numberseries table contains all counters for PersistentKeyGenerators. PK counters are stored with the key pk_<typecode>

To find the maximum counter in the table, you can use the following Groovy.

import de.hybris.platform.core.PK
import de.hybris.platform.servicelayer.search.FlexibleSearchQuery
import de.hybris.platform.servicelayer.search.FlexibleSearchService
FlexibleSearchService flexibleSearchService = spring.getBean("flexibleSearchService");
String type = "Address"
String query = "SELECT {PK} FROM {" + type + "}"
FlexibleSearchQuery fsq = new FlexibleSearchQuery(query);
fsq.setResultClassList(Collections.singletonList(PK.class));
List<PK> pkList = flexibleSearchService.<PK>search(fsq).getResult();
println("Found " + pkList.size() + " result(s) for type " + type)
long start = System.currentTimeMillis();
Long maxCounter = 0L;
for(PK pk : pkList){
long pkCounter = pk.getCounter();
if(pkCounter > maxCounter){
maxCounter = pkCounter;
}
}
println("Max counter is " + maxCounter);
long duration = System.currentTimeMillis() - start;
println("Execution of script took " + duration + " ms.")

Unfortunately, there is no easy way of finding other than checking all the records from the DB.

To be able to determine maximum counter value with a query, you need to get the counter with the following transformation.

private long getCounterInternal() {
    long lower = this.longValue & 8796093022207L;
    long upper = this.longValue & -17592186044416L;
    return (upper >> 1 | lower) >> 15;
}

Hope this helps,