KB484071: MicroStrategy Platform Analytics unique constraint violation on src_tran_valid_account_id_ts for Access Transactions Table

Description 

After resetting the offsets for Messaging Services, the Platform Analytics Consumer is unable to insert data into the Data Warehouse due to a unique constraint violation. 
For example:
 
ERROR BatchProcessUtil:114 PlatformAnalytics.IsDocumentStats_consumer_0 - Error upserting during batch process. Falling back to individual insert
org.hibernate.exception.ConstraintViolationException: could not execute batch
                at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129)
                at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
                at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
                at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:132)
                at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:111)
                …
at com.Strategy.kafka.consumer.ConsumerProcess$ConsumerRecordsProcessor.call(ConsumerProcess.java:232)
                at java.util.concurrent.FutureTask.run(FutureTask.java:266)
                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
                at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.BatchUpdateException: Duplicate entry '25557-0-Mstr.PlatformAnalytics.IsDocumentStats--1-300--1-6523807' for key 'src_tran_valid_account_id_ts'

Why is this happening? 

The unique constraints for the Access_Transactions table in Platform Analytics are src_tran_offset, src_tran_partition, src_tran_topic, validating_account_id, action_type,id, recipient_id, and broker_list_id. If data is removed from the Messaging Services layer such that it causes a reset to the offset, then there is no longer a unique offset value to validate this compound unique constraint. 
Subsequently, this will cause inserts of transactions to the Warehouse to fail as it will have a repeat of the Offset/Partition/Topic possible for a given set of Messaging Services Brokers. 
In normal operation, the Offset value never resets and only increases within Messaging Services to allow for this unique constraint to function. 
 

Solution

This issue is being investigated by the Strategy Technology team. 
 

Workaround

Since the Offsets are handled by the Strategy Services Broker, you should reset the Broker List ID to make all newly submitted transactions unique from the transactions existing in the ecosystem. 

1. Turn off the Platform Analytics Consumer.
2. Query the Platform Analytics Warehouse to get the current Brokers: 
   

   
   SELECT * FROM LU_BROKER_LIST

   
   
3. Insert a slightly modified Broker List ID for your current active Broker List. The Description must also be unique, if the same Description exists twice it will stop the Platform Analytics Consumer
   For example, if the above SELECT statement returns the following: '6489569685619609600', 'CSG-ANNA.corp.Strategy.com:9092,CSG-ELSA.corp.Strategy.com:9092,CSG-OLAF.corp.Strategy.com:9092,' then it is possible to run the SQL syntax: 
   

   
   INSERT INTO lu_broker_list VALUES (6489569685619609599, 'CSG-ANNA.corp.Strategy.com:9092,CSG-ELSA.corp.Strategy.com:9092,CSG-OLAF.corp.Strategy.com:9092_original')
   

   
   
4. Change the existing data in the Platform Analytics Warehouse by setting the broker_list_id equal to the new value for all existing instances of that value.
   For example, given the above information: 
   

   
   UPDATE access_transactions SET broker_list_id = 6489569685619609599 WHERE broker_list_id = 6489569685619609600;
   

   
   
5. Restart the Platform Analytics Consumer