Error installing new gateway connecting to external MySQL 8

Products

• CA API Gateway

Issue/Introduction

Installing new Gateway and are trying to configure the database connection and are getting the following error:

"An error occurred during the configuration"

 and when we look at the logs we see the following message:

[2023/11/23 at 18:51:22] - [WARNING]: Could not create database. An exception occurred: liquibase.exception.MigrationFailedException:

Migration failed for change set ssg-base.xml::1408665452836-94::gateway:

Reason: liquibase.exception.DatabaseException: Multiple primary key defined [Failed SQL: (1068) ALTER TABLE ssg.active_connector

ADD PRIMARY KEY (goid)]

Environment

Gateway 10.x 11

Cause

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)

 

Resolution

Set the variable to OFF

SET GLOBAL sql_generate_invisible_primary_key = OFF;