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

Document ID : KB000008462
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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