Unable to upgrade the Gateway database because the goidToString function does not exist

Products

• STARTER PACK-7
• CA Rapid App Security
• CA API Gateway

Issue/Introduction

Background 

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.x of the 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.goidToString 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\G;
+-----+--------------+----------+-------------------+
| 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 or goidToString row then this article should be executed. Note: next_hi function will be present in a clustered configuration only. If not, please review kb000057321.

Environment

Release:
Component: APIGTW

Resolution

Resolution

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

1. Log in to the Gateway appliance as the ssgconfig user
2. Select Option #3: Use a privileged shell (root)
3. Execute the following command: mysql ssg -e "DROP FUNCTION IF EXISTS goidToString;"
4. 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.

5. Enter the credentials for the specified user
6. Run the following SQL query:

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

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