Layer7 API Gateway: Unable to upgrade the Gateway database because the toGoid function does not exist
Article ID: 42931
Updated On:
Products
Issue/Introduction
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:
mysql> SHOW FUNCTION STATUS;
+-----+--------------+----------+-------------------+
| 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.
Environment
Release: API Gateway 9.x
Component: APIGTW
Resolution
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;
end//
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.
Feedback
