Layer7 API Gateway: Unable to upgrade the Gateway database because the toGoid function does not exist
search cancel

Layer7 API Gateway: Unable to upgrade the Gateway database because the toGoid function does not exist


Article ID: 42931


Updated On:


STARTER PACK-7 CA Rapid App Security CA API Gateway


The following error message may be printed in the console of the Gateway when attempting to upgrade the Gateway database:

The database was not upgraded due to the following reasons:
FUNCTION ssg_testUpgrade.toGoid does not exist
No changes have been made to the database. Please correct the problem and try again.
Press [Enter] to continue

In addition, a Gateway administrator can run the following SQL query from the privileged shell of the Gateway to determine if the three necessary stored procedures are present:

| Db  | Name         | Type     | Definer           |
| ssg | goidToString | FUNCTION | [email protected] |
| ssg | next_hi      | FUNCTION | [email protected] |
| ssg | toGoid       | FUNCTION | [email protected] |

It is critical that the displayed columns display the data above. There may be additional columns but the data in these columns should match the data in the Gateway's columns. If there is a discrepancy or absence in the toGoid row then this article should be executed.



Release: API Gateway 9.x
Component: APIGTW


The solution is to access MySQL as the gateway user, drop the functions, and recreate them. Here are the steps to perform:

Log in to the Gateway appliance as the ssgconfig user

Select Option #3: Use a privileged shell (root)

Execute the following command:  mysql ssg -e "DROP FUNCTION IF EXISTS toGoid;"

Log in to the MySQL server: mysql ssg -u gateway -p

NOTE: The value of "gateway" may vary in your environment based upon your configuration but the "root" user should not be used.

Enter the credentials for the specified user

Run the following SQL queries:

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;

Exiting the MySQL prompt and re-executing the SHOW FUNCTION STATUS query should result in the toGoid row being present and correct as prescribed. The Gateway upgrade process can be re-attempted

Additional Information

The Gateway relies on several stored procedures in the CA API Gateway's database. These stored procedures allow the Gateway to perform frequent operations in an expedient and efficient manner without having to constantly execute complex queries. The Gateway uses these stored procedures to calculate cardinality and convert data to necessary formats before adding data into the database. Creating, updated, or deleting data in the Gateway database may fail if these stored procedures are not working properly or were not formulated properly. This article will prescribe the steps to resolve a particular issue with a set of stored procedures that were introduced in version 8.0.0 of the Gateway.