WebSphere Commerce Version 7.0.0.6 or later

Troubleshooting: Madisons store publish fails because of duplicate key value errors in GEONODE table

When publishing the Madisons-FEP.sar store archive on a system with Fix Pack 6 or higher installed, the store publish fails and an error is displayed in the SystemOut.log file.

The error contains information similar to the following example:

A SQL Exception was received The statement was aborted because it would have caused a duplicate key value 
in a unique or primary key constraint or unique index identified by 'I0001459' defined on 'GEONODE'. 
on record [geonode: [GEONODE_ID=11051][IDENTIFIER=Canada][TYPE=CNTY][OPTCOUNTER=0]]. [sqlcode: 20,000, sqlstate: 23505]

Cause

In Fix Pack 6, the GEONODE table and STLOC tables were updated to include a STOREENT_ID column, which was added to the unique index of the tables. However, the sample data for the Madisons starter store was not updated to have the STOREENT_ID column. As a result, when publishing the store with sample data, the store publish fails and a duplicate exception error is thrown.

Solution

Try republishing the Madisons starter store with sample data after you edit several files in the MadisonsSampleData-fep.sar store archive. Follow these steps:
  1. Extract the MadisonsSampleData-FEP.sar store archive for editing:
    1. Navigate to the MadisonsSampleData-fep.sar store archive at the following path:
      • SolarisLinuxAIXWindowsWCDE_installdir/starterstores/common/samplecatalogs/MadisonsSampleData-FEP.sar
      • WebSphere Commerce DeveloperWC_installdir/instances/instance_name/starterstores/common/samplecatalogs/MadisonsSampleData-FEP.sar
    2. Extract the MadisonsSampleData-FEP.sar file into a temporary folder called MadisonSampleData-Edited.
    3. Rename the existing MadisonsSampleData-FEP.sar file to MadisonsSampleData-FEP-Backukp.sar.
  2. Update the extracted store archive files to include the required STOREENT_ID data:
    1. Replace the following two files with updated versions:

      /MadisonsSampleData_Edited/WEB-INF/stores/MadisonsCatalog/data/geonodes.xml

      /MadisonsSampleData_Edited/WEB-INF/stores/MadisonsCatalog/data/storelocations.xml

      You can download and extract the updated versions at this link: MadisonsSampleData-FEP.zip

    2. In a text editor, open the wcs.dtd file at the following path:

      MadisonSampleData-Edited/WEB-INF/stores/MadisonsCatalog/data/wcs.dtd

    3. Update the <!ATTLIST geonode section in the file to include the line shown with 1:
      <!ATTLIST geonode
   geonode_id		CDATA		#REQUIRED
   identifier		CDATA		#REQUIRED
   type		CDATA		#REQUIRED
   optcounter		CDATA		"0"
   storeent_id		CDATA		"0" 1
>
    4. Update the <!ATTLIST stloc section in the file to include the line shown with 2:
      <!ATTLIST stloc
   stloc_id		CDATA		#REQUIRED
   identifier		CDATA		#REQUIRED
   phone		CDATA		#IMPLIED
   fax		CDATA		#IMPLIED
   address1		CDATA		#IMPLIED
   address2		CDATA		#IMPLIED
   address3		CDATA		#IMPLIED
   city		CDATA		#IMPLIED
   state		CDATA		#IMPLIED
   country		CDATA		#IMPLIED
   zipcode		CDATA		#IMPLIED
   active		CDATA		"1"
   latitude		CDATA		#REQUIRED
   longitude		CDATA		#REQUIRED
   geonode_id		CDATA		#IMPLIED
   optcounter		CDATA		"0"
   storeent_id		CDATA		"0" 2
>
  3. Compress the entire MadisonSampleData-Edited folder structure into a compressed file. Rename the compressed file to MadisonsSampleData-FEP.sar.
  4. Publish the Madisons-FEP.sar file again.