ERROR while upgrading cluster-database: "FUNCTION: dropForeignKey does not exist"

book

Article ID: 8462

calendar_today

Updated On:

Products

CA API Developer Portal CA API Gateway

Issue/Introduction

Reported a persistent error while upgrading a cluster-gateway from 8.2 to 9.2. 
The patching is successful (8.2 > 8.3 > 8.4 > 9.0 > 9.1 > 9.2 > 9.2 software - it says v9.2.00 correctly) but when trying to upgrade the DB it fails during a test-DB-creation with the error: 
"FUNCTION: dropForeignKey does not exist". 

Cause

Reported a persistent error while upgrading a cluster-gateway from 8.2 to 9.2. 
The patching is successful (8.2 > 8.3 > 8.4 > 9.0 > 9.1 > 9.2 > 9.2 software - it says v9.2.00 correctly) but when trying to upgrade the DB it fails during a test-DB-creation with the error: 
"FUNCTION: dropForeignKey does not exist". 



Environment

Gateway version : 9.2Portal Version : 3.5

Resolution

There were two stored procedures dropForeignKey and dropIndexIfExists, that were supposed to be removed after the upgrade to 8.0 failed to remove. 

 

Each stored procedure should have a matching function, but these two are 'orphan'. 

Please run the two commands below in order to remove the stored procedures: 

DROP PROCEDURE IF EXISTS dropForeignKey; 
DROP PROCEDURE IF EXISTS dropIndexIfExists; 

And then run the query from the previous comment to make sure that the above two procedures are being deleted by running the comment  below: 

mysql mysql -e 'select name, type from proc' 

If the result is as below, try upgrading again. 
+--------------+----------+ 
| name | type | 
+--------------+----------+ 
| goidToString | FUNCTION | 
| next_hi | FUNCTION | 
| toGoid | FUNCTION | 
+--------------+----------+