Can't upgrade API Portal 4.2.0 to 4.3.x

Products

• CA API Developer Portal

Issue/Introduction

- Update API Portal 4.2.0 to version 4.3.2
- Run the portal.sh script, the script hangs with the message "Checking to see if DB schema update is needed ..."

Environment

Release : 4.2

Component : API PORTAL

Cause

From the journal log, it shows following error messages,

Apr 20 13:44:40 ip-172-16-0-46 4d4432556cc4[19260]: Unexpected error running Liquibase: ERROR: duplicate key value violates unique constraint "uk_theme_setting"

Apr 20 13:44:40 ip-172-16-0-46 4d4432556cc4[19260]:   Detail: Key (tenant_id, ref_id, name)=(portal, , DISPLAY_VERSION_NUMBER) already exists. [Failed SQL: UPDATE public.THEME_SETTING SET REF_ID = '' WHERE REF_ID IS NULL]

Apr 20 13:44:40 ip-172-16-0-46 4d4432556cc4[19260]: SEVERE 4/20/20 1:44 PM: liquibase: doesntmatter::set-refid-empty-str::chean22: ERROR: duplicate key value violates unique constraint "uk_theme_setting"

The indicates data problem in old portal database.

Resolution

Need remove duplicate data.

For OOBT postgreSql,

1. log into the portaldb container
docker exec -it -u 0 $(docker ps --filter name=portal_portaldb.1 -q) /bin/bash 

2. inside the container, log into the internal database, password is 7layer
psql portal admin

3. run the sql statement to remove duplicated data (it could take some time, ensure it runs successfully)
DELETE FROM theme_setting t WHERE t.uuid NOT IN (SELECT DISTINCT ON (t1.name, t1.tenant_id, t1.ref_id) t1.uuid from theme_setting t1);

4. run portal.sh script to start portal again

For mysql,
The SQL statement should be,
DELETE FROM THEME_SETTING
WHERE UUID NOT IN (SELECT UUID FROM (SELECT T1.UUID AS UUID FROM THEME_SETTING T1 GROUP BY T1.NAME, T1.TENANT_ID, T1.REF_ID) alias );