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:- Extract the MadisonsSampleData-FEP.sar store
archive for editing:
- Navigate to the MadisonsSampleData-fep.sar store
archive at the following path:
- WCDE_installdir/starterstores/common/samplecatalogs/MadisonsSampleData-FEP.sar
- WC_installdir/instances/instance_name/starterstores/common/samplecatalogs/MadisonsSampleData-FEP.sar
- Extract the MadisonsSampleData-FEP.sar file into a temporary folder called MadisonSampleData-Edited.
- Rename the existing MadisonsSampleData-FEP.sar file to MadisonsSampleData-FEP-Backukp.sar.
- Navigate to the MadisonsSampleData-fep.sar store
archive at the following path:
- Update the extracted store archive files to include the required
STOREENT_ID data:
- 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
- In a text editor, open the wcs.dtd file at
the following path:
MadisonSampleData-Edited/WEB-INF/stores/MadisonsCatalog/data/wcs.dtd
- 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 >
- 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 >
- Replace the following two files with updated versions:
- Compress the entire MadisonSampleData-Edited folder structure into a compressed file. Rename the compressed file to MadisonsSampleData-FEP.sar.
- Publish the Madisons-FEP.sar file again.