API Portal is not starting after upgrade to 5.2.x

Products

• CA API Developer Portal

Issue/Introduction

After upgrading portal , Portal does not start and the Portal_db-upgrade container logs the following error :

portal_db-upgrade 14:32:02 UTC Unexpected error running Liquibase: Migration failed for change set doesntmatter::create_tier_table::xxxxxxx:
portal_db-upgrade.14:32:02 UTC Reason: liquibase.exception.DatabaseException: Multiple primary key defined [Failed SQL: (1068) ALTER TABLE portal.TIER ADD PRIMARY KEY (TENANT_ID, UUID)]

Resolution

 

Beginning with MySQL 8.0.30, MySQL supports generated invisible primary keys for any InnoDB table that is created without an explicit primary key.

When the sql_generate_invisible_primary_key server system variable is set to ON, the MySQL server automatically adds a generated invisible primary key (GIPK) to any such table.

By default, the value of sql_generate_invisible_primary_key is OFF, meaning that the automatic addition of GIPKs is disabled.

This can be turned on in MySQL or with Azure it defaults this setting to ON

Check your mysql configuration if this variable is set to ON, it should be OFF 

mysql>  SHOW VARIABLES LIKE 'sql_generate_invisible_primary_key';

+------------------------------------+-------+

| Variable_name                      | Value |

+------------------------------------+-------+

| sql_generate_invisible_primary_key | ON   |

+------------------------------------+-------+

1 row in set (0.00 sec)

For MySql Azure

For MySQL version 8.0 and above, Generated Invisible Primary Keys(GIPK) is enabled by default for all the Azure Database for MySQL Flexible Servers.

Disabling GIPK fixes the issue, and to do so follow the following steps:

Sign in to the Azure portal, then locate your Azure Database for MySQL - Flexible Server.

Under the SETTINGS section, click Server parameters to open the server parameters page for the Azure Database for MySQL - Flexible Server.

Search for sql_generate_invisible_primary_key and update it to off

Delete the table and restart the portal stack .