Upgrading a dSource,VDB after a MySQL database upgrade

Overview

The following document describes the general methodology of upgrading your MySQL landscape. The “In-Place” upgrade method is the only recommended MySQL upgrade approach. This process replaces the old MySQL installation on the Target environment with the new version, using the same data directories as the previous version. 

Perform the MySQL upgrade in the following order: 

1. Target Environments and their VDBs 

2. Staging (Replica) and Source Environments

System requirements

Review the following requirements before performing any environment upgrade steps.

• Plan to install compatible versions on the staging and target environments. To know the compatible versions, see MySQL connector source and target compatibility section in MySQL matrix.

• Install MySQL Select Connector v4.1.0 or greater.

Pre-requisites

You can follow the below prerequisites before performing any activity as per this documentation

• Review and familiarize with the MySQL upgrade guidelines and the upgrade prerequisites. 

  • Delphix recommends validating these steps in a separate test environment before following the upgrade steps below.

• The MySQL upgrade from v5.7 to v8 introduced many incompatibility issues. We strongly recommend you install and run the MySQL Shell: Upgrade Checker Utility to identify those problems within all Source, Staging, Target, and VDB databases you plan to upgrade. Upgrading MySQL without fixing these issues can cause database initialization failure which might be unrecoverable and might need a rollback . 

• Following a successful MySQL platform upgrade, you will need to take precautions against dSource and VDB corruption. Unlike other database types, MySQL staging and target environments are not backward compatible. We recommend you avoid the following scenarios:

  • Refresh or rollback a VDB to a snapshot with a lower version [ ie 5.7 ] after an upgrade to a higher version [ ie 8.0 ] with incompatible data as defined by the upgrade checker utility.

  • Enable a dSource or VDB when the latest snapshot of the dSource or VDB’s MySQL contains incompatible data as defined by the upgrade checker utility. 

For the above scenario, follow troubleshooting guidelines in below section to analyze and correct the VDB or dSource

How to upgrade MySQL Target Environments and their VDBs

These directions outline how you can preserve the MySQL target environment and VDBs during an upgrade.

The target environment and VDBs must match the originating dSource to maintain full refresh and provision functionality. Therefore, it should be expected that users experience a temporary loss of the provision and refresh features. During this time, VDBs can be enabled and running, however, no refreshes should be performed until the following section is followed and the dSource version matches.

Delphix do not support an automated way to upgrade VDBs. In general, Delphix recommends deleting and re-provisioning each VDB from a new snapshot. However, if you wish to preserve certain VDBs, follow the steps below. If you would rather not modify this target environment and its VDBs, you may also create a completely new target environment as well.

Directions:

1. Select the Target environment that you plan to upgrade and its set of VDBs.

2. Identify all VDBs that you wish to preserve and delete all VDBs that you do not.

3. Run MySQL Utility Checker on the remaining VDBs. This utility will identify incompatibility issues that can occur while upgrading from version 5.7 to 8.0. 

4. For each VDB, solve the compatibility issues reported by MySQL Utility Checker.

5. Take a backup of the entire database.

6. Remove any VDB Refresh Policy assigned to the VDB.

7. Stop all VDBs.

8. By following the official MySQL in-place upgrade guidelines, perform an upgrade of MySQL installation on the Target environment.

9. Refresh the Target environment on the GUI.

10. Start all VDBs.

11. Take a new snapshot for each VDB.

    1. We recommend all prior snapshots are deleted as well to eliminate the chance of accidental rollback to a prior version.

12. Validate the upgraded VDBs.

At this point, the Target environment should be prepared to provision new VDBs from a matching dSource, and VDBs should be upgraded. Follow the next section to re-enable VDB refreshes.

How to upgrade MySQL Source and Staging Environments

Follow the steps below to upgrade dSources using the “In-Place” method. Each set of steps is organized based on the ingestion method.

If the issues reported by the MySQL Utility checker is not fixed for a dSource and upgrade is performed on the staging environment then the dSource will not start. To resolve this please check issue 2 in the troubleshooting section

Staging Push

1. Run MySQL Utility Checker on all dSource on the staging instance and also source to find all the incompatibility issues that can occur while upgrading from version 5.7 to 8.0. 

2. Solve all the compatibility issues reported by MySQL Utility Checker.

3. Take a backup of the entire database. 

4. Create a new snapshot on an existing dSource.

5. Disable all the dSource on the staging instance and MySQL database on source instance

6. By following the official MySQL in-place upgrade guidelines, upgrade the MySQL staging instance.

7. Perform an upgrade of the source MySQL instance as per the official MySQL in-place upgrade guidelines.

8. Refresh the source and staging environments in the GUI.

9. Enable all the dSource on the staging instance and also start MySQL database on source instance 

10. Take a new snapshot on an existing dSource.

11. Validate the dSource 

Replication with Delphix initiated backup

1. Run MySQL Utility Checker on all dSources on the instance to find all the incompatibility issues that can occur while upgrading from version 5.7 to 8.0. 

2. Solve all the compatibility issues reported by MySQL Utility Checker.

3. Take a backup of the entire database.

4. Create a new snapshot on an existing dSource.

5. Disable all the dSource on the staging instance and MySQL database on source instance

6. By following the official MySQL in-place upgrade guidelines, perform an upgrade of the replica instances. so here it is a staging instance.

7. By following the official MySQL in-place upgrade guidelines, perform an upgrade of the MySQL source instance. 

8. Refresh the staging and source environments in the Delphix Engine UI.

9. Enable all the dSource on the staging instance and also start MySQL database on source instance 

10. Take a new snapshot on an existing dSource.

11. Validate the dSource

Replication with customer-provided backup

1. Run MySQL Utility Checker on all dSources on the instance to find all the incompatibility issues that can occur while upgrading from version 5.7 to 8.0. 

2. Solve all the compatibility issues reported by MySQL Utility Checker.

3. Take a backup of the entire database.

4. Create a new snapshot on an existing dSource.

5. Disable all the dSource on the staging instance and MySQL database on source instance

6. By following the official MySQL in-place upgrade guidelines, perform an upgrade of the replica instances. So here it is the staging instance.

7. By following the official MySQL in-place upgrade guidelines, perform an upgrade of the MySQL Source instance.

8. Take a new Backup of the source database, and place it in the staging environment.

9. Refresh the staging and source environments in the GUI.

10. Edit the backup file location in the staging dSource configuration on Delphix Engine UI or replace the backup file on the staging environment.

11. Enable all the dSource on the staging instance and also start MySQL database on source instance 

12. Take a new snapshot on an existing dSource.

13. Validate the dSource

 Troubleshooting

• Issue 1: After starting/enabling or making a new snapshot, VDB/dSource fails to start.
  Solution: This occurs after an upgrade if you refresh or rollback the VDB to an older version of a snapshot or currently pointing to the latest snapshot which contains incompatible data. To resolve this, refresh or rollback to the correct version of the snapshot that was taken immediately after the upgrade.

  The logs in MySQL 8.0 will show that the database initialization failure occurred and because of this, the database was not able to start. An example of such an error is as below. Here there was a table named `catalogs` in MySQL schema of MySQL 5.7 database which is a default table in MySQL 8.0

  CODE

  023-07-26T15:02:39.477811Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.34) starting as process 15891
  2023-07-26T15:02:39.513413Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
  2023-07-26T15:02:39.513453Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
  2023-07-26T15:02:40.265033Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
  2023-07-26T15:02:40.270816Z 1 [ERROR] [MY-010781] [Server] Found ./mysql/catalogs.frm file in mysql schema. DD will create .ibd file with same name. Please rename table and start upgrade process again.
  2023-07-26T15:02:40.270838Z 1 [ERROR] [MY-010336] [Server] Found .frm file with same name as one of the Dictionary Tables.
  2023-07-26T15:02:40.271004Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
  2023-07-26T15:02:40.271028Z 0 [ERROR] [MY-013236] [Server] The designated data directory /temp/vdbtarget/data/ is unusable. You can remove all files that the server added to it.
  2023-07-26T15:02:40.271039Z 0 [ERROR] [MY-010119] [Server] Aborting
  2023-07-26T15:02:40.819837Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.34)  MySQL Community Server - GPL.
  2023-07-26T15:02:40.820669Z 0 [ERROR] [MY-010065] [Server] Failed to shutdown components infrastructure.

• Issue 2: dSource is not starting after an upgrade 
  Solution :  When the dSource is enabled after an upgrade below error is shown on the UI of the engine. 

  CODE

  Traceback (most recent call last): File "plugin/platform_server.py", 
  line 66, in _run_helper response = internal_func(request) File "./_linked.py", 
  line 517, in _internal_start_staging File "./plugin_runner.py", 
  line 75, in start_staging File "./pluginops/pluginops.py", 
  line 172, in start_staging File "./libs.py", 
  line 174, in run_bash File "./libs.py", 
  line 91, in _check_exit_code 
  dlpx.virtualization.libs.exceptions.PluginScriptError: The script failed 
  with exit code

Here the error depicts that dSource was not able to start . One of the reasons why the dSource won't start after an upgrade is it might have some incompatible data which does not work in MySQL 8.0. Commonly, the issues reported by MySQL Upgrade Checker were ignored and the upgrade was performed. Please follow the below steps if you encounter any error as above. 

1. Uninstall MySQL version 8.0
   Note: Please make sure you don't delete your data directory

2. By following the official MySQL guidelines install MySQL version 5.7

3. Enable the dSource and Refresh the environment

4. Connect to the dSource and fix the incompatibilities

5. Take a new snapshot on the dSource 

6. Disable the dSource on the GUI

7. Again upgrade the staging to MySQL version 8.0

8. Refresh the staging environment in the GUI.

9. Enable the dSource [ dSource should be able to start since there is no incompatible data in the snapshot ] on the GUI

10. Take a new snapshot [ This snapshot will be of version 8.0 ]