Upgrade portal failed with MySQl error - Failed SQL: (3780)

Products

• CA API Developer Portal

Issue/Introduction

Try to upgrade Portal v5.1 to v5.2, stuck on upgrading database.

The portal_db-upgrade log shows,

Unexpected error running Liquibase: Migration failed for change set doesntmatter::portaldb-29.1-create_rate_quota_api_org_xref_table::rk667895:
Reason: liquibase.exception.DatabaseException: Referencing column 'TENANT_ID' and referenced column 'TENANT_ID' in foreign key constraint 'FK_API_ORG_RATE_QUOTA_XREF_RATE_QUOTA_UUID' are incompatible. [Failed SQL: (3780) ALTER TABLE portal.API_ORG_RATE_QUOTA_XREF ADD CONSTRAINT FK_API_ORG_RATE_QUOTA_XREF_RATE_QUOTA_UUID FOREIGN KEY (TENANT_ID, RATE_QUOTA_UUID) REFERENCES portal.RATE_QUOTA (TENANT_ID, UUID) ON UPDATE NO ACTION ON DELETE CASCADE]

Cause

The error can be due to different reasons, but one of them is,

Any string-type FK column(s) must have the same character set and collation as the corresponding PK column(s)

 

When checked the table RATE_QUOTA, found that it's using utf8mb3 collatioin,

And the table API_ORG_RATE_QUOTA_XREF is using utf8mb4,

Resolution

utf8mb3 should be from mysql 5.7, if follow the document below to upgrade to mysql 8, it should be utf8mb4,

https://techdocs.broadcom.com/us/en/ca-enterprise-software/layer7-api-management/api-developer-portal/5-2/install-configure-and-upgrade/install-portal-on-docker-swarm/upgrade-portal-database-to-mysql-8.html

 

May need to alter the collation to utf8mb4 before upgrade.