"Error 500 - Internal Server Error" when loading captions file

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

When trying to upload a captions/translations language package under Administration > General Settings > Localization, we get an error "Error 500 - Internal Server Error".

app-ca.log shows the following error:

Error message: ERROR: duplicate key value violates unique constraint "cmn_captions_nls_bkup_u1"
  Detail: Key (pk_id, table_name, language_code)=(5xxxxxx, BPM_DEF_STAGES, no) already exists.
Executed:
 insert into cmn_captions_nls_bkup (id
                                              , table_name
                                              , pk_id

Environment

Version: 15.9.3

Cause

Possibly due to upload of language package from a different environment into the environment with the issue. This could possibly cause a mismatch in IDs that get inserted into the cmn_captions_nls_bkup table.

Resolution

1. As a general best practice and highly recommended, back up the database schema.
2. Back up (export) the custom translation files as they will be lost when following the next steps.
3. Execute the following queries:

SELECT COUNT(*) FROM CMN_CAPTIONS_NLS_BKUP;
SELECT DISTINCT LANGUAGE_CODE FROM CMN_CAPTIONS_NLS_BKUP;

4. At this point, the first query is expected to return a value higher than 0. The second query should at least return the language codes where the translations could not be loaded.
5. For the language(s) where the captions could not be uploaded, go to Administration > General Settings > Localizations, select it, and click on the "Reset Captions" button.
6. Execute the same queries as in Step 2.
7. The first query should return 0. The second query should not return any of the language codes that have been reset.
8. Re-upload the captions file(s).