Cannot upgrade MySQL DB engine from 5.7 to 8.x - AES_ENCRYPT_PORTAL - FUNCTION uses removed function ENCRYPT

Products

• CA API Developer Portal

Issue/Introduction

Currently 5.02 Portal is using AWS RDS 5.7 MySQL DB.

When upgrading MySQL from 5.7 to 8.x  (for 5.02 to 5.1.2 upgrade)  the AWS/RDS does a pre-flight check.

However, it does find the following and won't continue the engine upgrade.

dev_portal_portal.AES_ENCRYPT_PORTAL - FUNCTION uses removed function ENCRYPT (consider using SHA2 instead)

What can be done to remove this function (without destroying Portal integrity) from the Portal_portal DB to get this check to pass and continue?

Environment

Release : 5.1.2

Resolution

It seems AWS is using custom precheck rules when upgrading MySQL in AWS RDS

The prechecks include some checks that are included with MySQL and some that were created specifically by the Amazon RDS team. For information about the prechecks provided by MySQL, see Upgrade checker utility.

You need to speak to AWS RDS support as it seems there is a bug in there precheck routine which false identify AES_ENCRYPT as the removed ENCRYPT function and blocks the database upgrade . 

Portal functions do not use this depreciated  ENCRYPT function.

Additional Information

The following workaround could be used but make sure you have a valid MySQL backup .

dump the current functions with 

mysqldump -u <username> -p --routines  --skip-triggers --no-create-info --no-data --no-create-db --skip-opt portal > outputfile.sql

delete the two functions in mysql 

drop function  AES_ENCRYPT_PORTAL;

drop function  AES_DECRYPT_PORTAL;

try to upgrade to mysql 8 in aws . 

restore missing functions

mysql portal -u <username> -p <outputfile.sql -f

Enter password:
ERROR 1304 (42000) at line 66: FUNCTION CURRENT_MS already exists
ERROR 1304 (42000) at line 83: FUNCTION generate_uuid already exists

verify if the two functions exist again 

mysql>  show create function portal.AES_DECRYPT_PORTAL \G;

mysql>  show create function portal.AES_ENCRYPT_PORTAL \G;

Make sure you have a full backup of the mysql5.7 in case of problems