TAS upgrade failed with updating credhub instance due to DB migration failure issue with AWS RDS MySQL 5.7

Products

• VMware Tanzu Application Service

Issue/Introduction

If TAS is configured to use RDS MySQL 5.7 database, updating credhub instance might fail with the following error during TAS upgrade to a higher version that's shipped with Credhub BOSH release 2.12.70 ~ 2.12.85.

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration V58__remove_triggers.sql failed
-----------------------------------------
SQL State  : HY000
Error Code : 1419
Message    : (conn=86995) You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Location   : db/migration/mysql/V58__remove_triggers.sql (/var/vcap/data/packages/credhub/c09051cc773c59509c7e05ff558a7537df907afe/file:/var/vcap/data/packages/credhub/c09051cc773c59509c7e05ff558a7537df907afe/cred
hub.jar!/BOOT-INF/classes!/db/migration/mysql/V58__remove_triggers.sql)
Line       : 2
Statement  : DROP TRIGGER IF EXISTS tr_credential_version_pre_del

 

Environment

• TAS is configured with a remote RDS MySQL 5.7 database
• Upgrading TAS to a release including Credhub BOSH release 2.12.70 ~ 2.12.85. For example,
  • TAS 2.11.57, 2.11.58
  • TAS 2.13.39, 2.13.40
  • TAS 4.0.22 ~ 4.0.26
  • TAS 6.0.2 ~ 6.0.6

Cause

There is a change introduced in the aforementioned Credhub BOSH releases, which attempt to drop some triggers from the credhub database. However the "Drop Triggers" SQL statement couldn't work with current default settings of RDS MySQL 5.7 instance. 

Resolution

• Best option would be upgrading RDS MySQL instance to version 8
• Or avoid upgrading to the following TAS releases if an update of Credhub BOSH release will be incurred.
  • TAS 2.11.57, 2.11.58
  • TAS 2.13.39, 2.13.40
  • TAS 4.0.22 ~ 4.0.26
  • TAS 6.0.2 ~ 6.0.6

Additional Information

Please also pay attention to the following statement in TAS release note and github issue.

• If you are upgrading to this version directly from CredHub 2.12.67 and if you are using MySQL with CredHub, manually execute the following SQL statements in your database before the upgrade:

DROP TRIGGER IF EXISTS tr_credential_version_pre_del;
DROP TRIGGER IF EXISTS tr_credential_version_post_del;
DROP TRIGGER IF EXISTS tr_credential_pre_del;