Gateway Database Upgrade Fails with "FUNCTION toGoid does not exist" on Azure Database for MySQL

Products

• CA API Gateway

Issue/Introduction

During an in-place upgrade of the Layer7 API Gateway, the database upgrade process may fail with a SQLSyntaxErrorException indicating that required stored functions—specifically toGoid, goidToString, or next_hi—are missing from the database. This error typically occurs when the Gateway database is hosted on Azure Database for MySQL.

Symptom: The database upgrade process via setup.sh aborts, and the logs report a migration failure: liquibase.exception.DatabaseException: FUNCTION ssg.toGoid does not exist

This error occurs because the Gateway database upgrade scripts assume these three stored functions exist, but they are not present in the environment.

Environment

API Gateway 11.x

Cause

The Gateway relies on a small set of MySQL stored functions created during the initial database setup. These functions may be missing if the database was migrated to Azure Database for MySQL using a method that does not migrate stored routines by default (e.g., a mysqldump execution without the --routines flag, or the Azure Database Migration Service).

Resolution

To address this, you must recreate the missing functions on your production ssg database and ensure your environment is configured to allow non-deterministic functions.

1. Prerequisites Ensure that log_bin_trust_function_creators is set to ON for your Azure MySQL server. This is required for the database to successfully create these functions.

2. Recreate Missing Functions Take a full backup of the ssg database before applying these changes. Then, execute the following SQL commands on your ssg database:

USE ssg;

DROP FUNCTION IF EXISTS toGoid;
DELIMITER //
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//
DELIMITER ;

DROP FUNCTION IF EXISTS goidToString;
CREATE FUNCTION goidToString(goid binary(16)) RETURNS CHAR(32) DETERMINISTIC
RETURN lower(hex(goid));

DROP FUNCTION IF EXISTS next_hi;
DELIMITER //
CREATE FUNCTION next_hi() RETURNS bigint NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER
BEGIN
UPDATE hibernate_unique_key SET next_hi=last_insert_id(next_hi)+IF(@@global.server_id=0,1,2);
RETURN IF((last_insert_id()%2=0 and @@global.server_id=1) or (last_insert_id()%2=1 and @@global.server_id=2),last_insert_id()+1,last_insert_id());
END//
DELIMITER ;

3. Verify Run the following query to verify that all three functions now exist in the database: SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'ssg';

4. Resume Upgrade Once the functions are confirmed to exist, re-run the database upgrade using the Gateway's setup.sh utility: /opt/SecureSpan/Gateway/runtime/bin/setup.sh

Select Option 1 to resume the upgrade process.