Server Manager - Update Database - Fatal Error - Local base fails to update - duplicate key value violates unique constraint idx1 coobject
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’
3. ```
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 )
5. 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**