# Handling Duplicate Entries

This document addresses how an error in Database upgrade script can be managed effectively.

## Issue

If the below error has been encountered while attempting to execute the upgrade script, this can be resolved by following the steps mentioned in this document.

**Error message**

```
psql:sql/1.1.5.5_to_1.2.0.1-B1_upgrade.sql:13: ERROR: could not create unique index "cert_thumbprint_unique" DETAIL: Key (cert_thumbprint, partner_domain)=(231bd472ab24ef60ec6*******2cace89c34, AUTH) is duplicated. psql:sql/1.1.5.5_to_1.2.0.1-B1_upgrade.sql:13: STATEMENT: ALTER TABLE keymgr.ca_cert_store ADD CONSTRAINT cert_thumbprint_unique UNIQUE (cert_thumbprint,partner_domain);
```

The error message states that a unique index, named `cert_thumbprint_unique`, cannot be created due to a duplicate entry. Specifically, the values for the `cert_thumbprint` and `partner_domain` columns, which are (231bd472ab24ef60ec6\*\*\*\*\*\*\*2cace89c34, AUTH), already exist in the database. This duplicate entry violates the unique constraint defined for the `ca_cert_store` table in the `mosip_keymanager` database.

To address and successfully execute the DB upgrade script, the following steps can be taken:

1. Identify the duplicate entries in the mosip\_keymanager table.
2. To accomplish this, use the provided SQL query:

   ```
   SELECT * 
   FROM keymgr.ca_cert_store 
   WHERE (cert_thumbprint, partner_domain) IN 
         (SELECT cert_thumbprint, partner_domain 
          FROM your_table_name 
          GROUP BY cert_thumbprint, partner_domain 
          HAVING COUNT(*) > 1);
   ```

   This query will retrieve the rows of data that contain duplicate entries.
3. As a precautionary measure, it is advisable to create a backup of all the duplicate values.
4. Remove the duplicate entries so that only one composite key remains. The aforementioned SQL script can be reused to verify that the duplicates have been successfully deleted. If the result is empty, then all duplicates have been removed.
5. By following these steps, the problem should be resolved, and the DB upgrade script can be executed without any further issues.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.mosip.io/1.2.0/setup/upgrade/upgrade-runbook/mock-services/upgrade-db-script-error.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.