How do you upgrade MySQL HeatWave when deploying with Terraform?

Have you already tried to upgrade the MySQL version of your MySQL HeatWave instance in OCI that is deployed with Terraform?

When you tried, you realized, I hope you didn’t turn off backups, that the instance is destroyed and recreated new!

This is our current MySQL HeatWave DB System deployed using Terrafrom:

And this is the module used to deploy it:

resource "oci_mysql_mysql_db_system" "MDSinstance" {
    admin_password = var.admin_password
    admin_username = var.admin_username
    availability_domain = var.availability_domain
    compartment_id = var.compartment_ocid
    configuration_id = data.oci_mysql_mysql_configurations.mds_mysql_configurations.configurations[0].id
    shape_name = local.shape_name 
    mysql_version = "9.3.1"
    subnet_id = var.subnet_id
    data_storage_size_in_gb = var.mysql_data_storage_in_gb
    display_name = var.display_name

    count = var.existing_mds_instance_id == "" ? 1 : 0

    is_highly_available = var.deploy_ha

}

Of course, if you do it following the rules of art, the plan would have warned you:

Why is that?

The mysql_version forces the recreation of the MySQL DB System resource, as we can see here in the source code of the OCI provider:

How to proceed?

If you are deploying your infrastructure on OCI and particularly your MySQL instance with Terraform, the upgrade procedure must be performed outside of Terraform.

You can update your DB System from the OCI Web Console or using the oci cli:

$ oci mysql db-system update --mysql-version 9.4.0 \
  --db-system-id ocid1.mysqldbsystem.oc1.eu-frankfurt-1.xxxxxyoq \
  --region eu-frankfurt-1
{
  "opc-work-request-id": "ocid1.mysqlworkrequest.oc1.eu-frankfurt-1.xxx"
}

When the request is finished, we can see that the system has been successfully updated:

However, if we rerun Terraform, it will try to downgrade. That means the DB System instance will be destroyed and a new one created!

Fixing the code

To avoid such a problem, we need to modify our Terraform module like this:

resource "oci_mysql_mysql_db_system" "MDSinstance" {
    admin_password = var.admin_password
    admin_username = var.admin_username
    availability_domain = var.availability_domain
    compartment_id = var.compartment_ocid
    configuration_id = data.oci_mysql_mysql_configurations.mds_mysql_configurations.configurations[0].id
    shape_name = local.shape_name
    mysql_version = "9.3.1"
    subnet_id = var.subnet_id
    data_storage_size_in_gb = var.mysql_data_storage_in_gb
    display_name = var.display_name

    count = var.existing_mds_instance_id == "" ? 1 : 0

    is_highly_available = var.deploy_ha

    lifecycle {
        ignore_changes = [mysql_version]
    }
}

Note the lifecycle block at the end.

Now we can retry, and Terraform will reply:

No changes. Your infrastructure matches the configuration.

Terraform has compared your real infrastructure against your configuration
and found no differences, so no changes are needed.

Conclusion

As you have seen, it’s currently not possible to upgrade a MySQL HeatWave version in OCI using Terraform without destroying the instance and recreating it, which will lead to data loss.

However, it’s possible to modify the code to ignore version changes, and the upgrade should then be issued in the OCI Console or the command-line using oci-cli.

Happy deployment in OCI!