Setup CA API Gateway9.4 with Azure Database for MySQL5.7

Products

• CA API Gateway
• API SECURITY

Issue/Introduction

API Gateway 9.4 has been deployed on Microsoft Azure.

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/layer7-api-management/api-gateway/9-4/other-gateway-form-factors/run-ca-api-gateway-in-microsoft-azure-cloud/best-practices-and-recommendations-to-configure-gateway-in-azure.html

Azure Database for MySQL was created along with the link "Create an Azure Database for MySQL server by using the Azure portal" on the above page but "2) Create a new CA API Gateway database" was failed with the following error message.

Error creating database when saving configuration 'Cannot create database: 'Migration failed for change set ssg-base.xml::createProcedure-toGoid::gateway:
     Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE FUNCTION toGoid (prefix bigint, suffix bigint)
            RETURNS binary(16) DETERMINISTIC
            begin
            if suffix is null then RETURN null;
            else RETURN concat(lpad(char(prefix >> 32, prefix),8,'\0'),lpad(char(suffix >> 32, suffix),8,'\0'));
            end if;
            end;: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)' [code:5, ERROR]'

This error was resolved by the knowledge article https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=46740 but another error was shown up.

---------------------
Configuration Results
---------------------

An error occurred during configuration.

Error creating database when saving configuration 'Cannot create database: 'Migration failed for change set ssg-base.xml::1408665452836-106::gateway:
    Reason: liquibase.exception.UnexpectedLiquibaseException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.' [code:5, ERROR]'

Environment

Release : 9.4

Component : API GATEWAY

Cause

The idle connection timeout of Azure Database for MySQL seems too short by default.

Resolution

Azure Database for MySQL has a short wait_timeout value for the server parameter by default. The Gateway Configuration Menu may fail to setup the external ssg database on Azure. The value for the wait_timeout parameter needs to be increased to 28800 which is the default of MySQL 5.7.