Description
During a migration from 7.3.0 to higher version, the local base migration fails with a SQL error about duplicate key as mentioned below:
SQL Error: ERROR: duplicate key value violates unique constraint "idx1_coobject". SQL Error: DETAIL: Key (object_id)=(3424) already exists.. SQL Error: CONTEXT: SQL statement "insert into AMT_OBJECT (OBJECT_ID, NAME_ID, SHORT_NAME_ID, OBJECT_TYPE_ID, INTERNAL_ID, STATUS).
This issue occurs if you have duplicates in the objects table in the knowledge base even before migration so this issue can be encountered during analysis as well and is not due to CUT.
Observed in CAST AIP
Release | Yes/No |
|---|---|
| 8.3.x |  |
Observed on RDBMS
RDBMS | Yes/No |
|---|---|
| CSS | |
Step by Step Scenario
- CSS Upgrade
- Run CUT Migration
- Knowledge base fails with the SQL error during migration
Action Plan
The issue is due to duplication of the objects name and objects GUID in the table OBJECTS, in the local database.
Restore the local database before migration.
Run the below query on the local database to identify the duplication :
SELECT idnam FROM objects GROUP BY idnam HAVING Count(1) > 1
For example you will get the below results:
'src_Net?h:/sources/application/web/application/aff/faits.jsp' 'src_Net?h:/sources/application/web/application/bloc/blocadresse.jsp' 'src_Net?h:/sources/application/web/application/bloc/bloccompositionaudience.jsp' 'src_Net?h:/sources/application/web/application/bloc/blocdestinataire.jsp' 'src_Net?h:/sources/application/web/application/bloc/blocemetteur.jsp' 'src_Net?h:/sources/application/web/application/bloc/blocemetteurop.jsp' 'src_Net?h:/sources/application/web/application/bloc/blocsaisine.jsp' 'src_Net?h:/sources/application/web/application/bloc/blocsaisinepv.jsp' 'src_Net?h:/sources/application/web/application/exp/blocdestreqerr.jsp' 'src_Net?h:/sources/application/web/application/jgt/decision.jsp'
Using the result from the 1st query, run the below query on the local base to find the ids if the duplicate objects:
SELECT * FROM objects WHERE idnam IN (SELECT idnam FROM objects GROUP BY idnam HAVING Count(1) > 1)
For example, from the 2nd query you will find the below objects that are duplicated on the name and shortname. The Id of the objects is not the same. Only the name and shortname are the same.
26999084;'src_Net?h:/sources/application/web/application/aff/faits.jsp';'src_Net?h:/sources/application/web/application/aff/faits.jsp';274 115317101;'src_Net?h:/sources/application/web/application/aff/faits.jsp';'src_Net?h:/sources/application/web/application/aff/faits.jsp';274 26999281;'src_Net?h:/sources/application/web/application/bloc/blocadresse.jsp';'src_Net?h:/sources/application/web/application/bloc/blocadresse.jsp';274 115315058;'src_Net?h:/sources/application/web/application/bloc/blocadresse.jsp';'src_Net?h:/sources/application/web/application/bloc/blocadresse.jsp';274 115316986;'src_Net?h:/sources/application/web/application/bloc/bloccompositionaudience.jsp';'src_Net?h:/sources/application/web/application/bloc/bloccompositionaudience.jsp';274 26999283;'src_Net?h:/sources/application/web/application/bloc/bloccompositionaudience.jsp';'src_Net?h:/sources/application/web/application/bloc/bloccompositionaudience.jsp';274 115314986;'src_Net?h:/sources/application/web/application/bloc/blocdestinataire.jsp';'src_Net?h:/sources/application/web/application/bloc/blocdestinataire.jsp';274 26999286;'src_Net?h:/sources/application/web/application/bloc/blocdestinataire.jsp';'src_Net?h:/sources/application/web/application/bloc/blocdestinataire.jsp';274 115315501;'src_Net?h:/sources/application/web/application/bloc/blocemetteur.jsp';'src_Net?h:/sources/application/web/application/bloc/blocemetteur.jsp';274 26999288;'src_Net?h:/sources/application/web/application/bloc/blocemetteur.jsp';'src_Net?h:/sources/application/web/application/bloc/blocemetteur.jsp';274 56984548;'src_Net?h:/sources/application/web/application/bloc/blocemetteurop.jsp';'src_Net?h:/sources/application/web/application/bloc/blocemetteurop.jsp';274 115318836;'src_Net?h:/sources/application/web/application/bloc/blocemetteurop.jsp';'src_Net?h:/sources/application/web/application/bloc/blocemetteurop.jsp';274 26999293;'src_Net?h:/sources/application/web/application/bloc/blocsaisine.jsp';'src_Net?h:/sources/application/web/application/bloc/blocsaisine.jsp';274 115318345;'src_Net?h:/sources/application/web/application/bloc/blocsaisine.jsp';'src_Net?h:/sources/application/web/application/bloc/blocsaisine.jsp';274 115318900;'src_Net?h:/sources/application/web/application/bloc/blocsaisinepv.jsp';'src_Net?h:/sources/application/web/application/bloc/blocsaisinepv.jsp';274 26999294;'src_Net?h:/sources/application/web/application/bloc/blocsaisinepv.jsp';'src_Net?h:/sources/application/web/application/bloc/blocsaisinepv.jsp';274 115315070;'src_Net?h:/sources/application/web/application/exp/blocdestreqerr.jsp';'src_Net?h:/sources/application/web/application/exp/blocdestreqerr.jsp';274 26999447;'src_Net?h:/sources/application/web/application/exp/blocdestreqerr.jsp';'src_Net?h:/sources/application/web/application/exp/blocdestreqerr.jsp';274 115316990;'src_Net?h:/sources/application/web/application/jgt/decision.jsp';'src_Net?h:/sources/application/web/application/jgt/decision.jsp';274 26999542;'src_Net?h:/sources/application/web/application/jgt/decision.jsp';'src_Net?h:/sources/application/web/application/jgt/decision.jsp';274
Remove the duplication. The id of these duplication are the highest one. From the above example run the below query (replace the values below with the id's retrieved in the previous query).
DELETE FROM objects WHERE idkey IN ( select max(idkey) from objects group by idshortnam, idnam having count(1)>1 )
Run the Migration / the analysis again.
Impact on Analysis Results and Dashboard
Analysis will fail with the same duplicate key value violation error.
Notes/comments
Refer to Webcall 29677
Related Pages
