Advisor feed insertion fails with duplicate key constraint error.

During the Advisor data download a duplicate key constraint error is shown in the advisor0.0.log and the feed fails to import the data until the issue is resolved:

Caused by: java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint 'ADV_PRODUCT_PK'. Cannot insert duplicate key in object 'esecdba.ADV_PRODUCT'.

For customers who used the Advisor DVD for the initial data load a problem exists with the seed values of the data inserted into the database.  As a result all customers who did not download their entire feed directly from Novell (versus using the DVD) will eventually get this error.  To resolve or prevent the error a simple fix exists for the database seed values.

The following queries need to be run to update the database seed values.  These must be run while all Sentinel components are shutdown but while the database is running:

UPDATE ESEC_SEQUENCE SET SEED = (SELECT FLOOR(MAX(product_id)/100 + 1) FROM ADV_PRODUCT) WHERE TABLE_NAME='ADV_PRODUCT' AND
COLUMN_NAME = 'ADV_PRODUCT.PRODUCT_ID';
 
UPDATE ESEC_SEQUENCE SET SEED = (SELECT FLOOR(MAX(SERVICE_PACK_ID)/100 + 1) FROM ADV_PRODUCT_SERVICE_PACK) WHERE TABLE_NAME='ADV_PRODUCT_SERVICE_PACK' ANDCOLUMN_NAME = 'ADV_PRODUCT_SERVICE_PACK.SERVICE_PACK_ID';
 
UPDATE ESEC_SEQUENCE SET SEED = (SELECT FLOOR(MAX(VERSION_ID)/100 + 1) FROM ADV_PRODUCT_VERSION) WHERE TABLE_NAME='ADV_PRODUCT_VERSION' ANDCOLUMN_NAME = 'ADV_PRODUCT_VERSION.VERSION_ID';
 
UPDATE ESEC_SEQUENCE SET SEED = (SELECT FLOOR(MAX(VENDOR_ID)/100 + 1) FROM ADV_VENDOR) WHERE TABLE_NAME='ADV_VENDOR' and
COLUMN_NAME = 'ADV_VENDOR.VENDOR_ID';

To verify that the issue is resolved the following query can be executed in the same database environment.

SELECT table_name, SEED FROM esec_sequence WHERE table_name LIKE 'ADV_%';

The results of this query should have certain minimum values to be valid.  If the value is greater-than or equal to 1053 for ADV_PRODUCT, greater-than or equal to 1164 for ADV_PRODUCT_SERVICE_PACK or ADV_PRODUCT_VERSION, or greater-than or equal to 1035 for ADV_VENDOR then the problem still exists.

As a note if the Sentinel environment is not stopped properly Sentinel's cached values for these fields will be rewritten back to the database and the changes will be undone.  Before resuming the Advisor service go to the $ESEC_HOME/data/advisor_data/new directory and delete the current extracted feed file leaving all of the .zip files behind.  For example if the following files are present delete only 'feed.1500.0':

feed.1500.0
feed.1500.0.zip
feed.1501.0.zip
feed.1502.0.zip