search cancel

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". 

Environment

Gateway version : 9.2Portal Version : 3.5

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". 



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 | 
+--------------+----------+