The TAS pxc-mysql pre-start fails when using MySQL 8.0.30 external database

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

TAS 2.13 added support for an external MySQL 8 database. When trying to use a MySQL 8.0.30 external database for TAS 2.13, the pre-start fails with errors like the following in the pxc-mysql/pre-start.stdout.log. Note, the messages below are the most interesting related to this problem and not everything that was logged.

[2022-09-16 14:05:19+0000] rake aborted!
[2022-09-16 14:05:19+0000] Sequel::DatabaseError: Mysql2::Error: Referencing column 'app_guid' and referenced column 'guid' in foreign key constraint 'route_mappings_ibfk_1' are incompatible.
[2022-09-16 14:05:19+0000] Caused by:
[2022-09-16 14:05:19+0000] Mysql2::Error: Referencing column 'app_guid' and referenced column 'guid' in foreign key constraint 'route_mappings_ibfk_1' are incompatible.
[2022-09-16 14:05:19+0000] Tasks: TOP => db:migrate
[2022-09-16 14:05:19+0000] (See full trace by running task with --trace)

There are at least 492 migrations that should be performed given the scripts on the TAS cloud_controller instances at "/var/vcap/packages/cloud_controller_ng/cloud_controller_ng/db/migrations". When this problem has occurred, it is the 218th migration that has failed. To confirm this is the issue, connect to the external database and run the query "SELECT COUNT(*) FROM ccdb.schema_migrations;". For this issue, the number returned should be 217.

Environment

Product Version: Other

Resolution

This is an issue specific to MySQL 8.0.30 being used as a TAS external database. The resolution will be included in TAS 3.0 and backported to TAS 2.13. The change is a one line change to the "/var/vcap/packages/cloud_controller_ng/cloud_controller_ng/db/migrations/20150514190458_fix_mysql_collations.rb" migration script on the cloud_controllers. This change can be made manually to the script on the cloud controller instance to resolve the issue and enable successful TAS database migration to the 2.13 schema. When making this change, no other changes should be made in this directory. The change is shown at:

https://github.com/cloudfoundry/cloud_controller_ng/pull/2891/commits/58469dd043d05a6ec0abeb53ef95024a298ac9de

It really only needs to be changed on the "Bootstrap" cloud_controller instance. To determine this, a command like the following can be run. 

bosh -d `bosh ds --column=name | grep ^cf-` instances -i --column=Instance --column=Bootstrap | grep -w cloud_controller

Once this change has been made, drop and recreate the "ccdb" database/schema in the external MySQL 8 database, then run the Apply Changes.
 
While this change to the "20150514190458_fix_mysql_collations.rb" migration script will not survive a recreate of the cloud_controller instance, the database will have already been migrated so will not be needed again unless another new ccdb database/schema is created in the future.