[Bug]: rds mysql aurora force replacement on upgrade from 5.6 to 5.7

[chandankashyap19]

Terraform Core Version

1.1.4

AWS Provider Version

4.33.0

Affected Resource(s)

mysql aurora rds instance

Expected Behavior

in place upgrade supposed to be happen while performing upgrade from mysql5.6 to mysql5.7 aurora engine

Actual Behavior

Force replacement of rds cluster & rds instance while performing upgrade from mysql5.6 to mysql5.7 aurora engine

Relevant Error/Panic Output Snippet

# aws_rds_cluster.rds-cluster must be replaced
-/+ resource "aws_rds_cluster" "rds-cluster" {
      ~ engine                              = "aurora" -> "aurora-mysql" # forces replacement
      ~ engine_version                      = "5.6.mysql_aurora.1.23.4" -> "5.7.mysql_aurora.2.09.2"
      ~ engine_version_actual               = "5.6.mysql_aurora.1.23.4" -> (known after apply)

Terraform Configuration Files

resource "aws_rds_cluster" "rds-cluster" {
    backup_retention_period = 2
    cluster_identifier = "tfvalidated"
    availability_zones = ["${local.aws_region}a", "${local.aws_region}b", "${local.aws_region}c"]
    engine_version = "5.7.mysql_aurora.2.09.2"
    engine = "aurora-mysql"
    database_name = "mypoc"
    master_username = "testadminuser"
    master_password = "xxxxxxxxxxx"
    iam_database_authentication_enabled = false
    db_subnet_group_name = "${aws_db_subnet_group.rds.id}"
    vpc_security_group_ids = ["sg-09b3f5exxxxxxxxxx"]
    skip_final_snapshot = true
    deletion_protection = true
    allow_major_version_upgrade = true
    apply_immediately = true
    enable_global_write_forwarding = false
    db_cluster_parameter_group_name = "${aws_rds_cluster_parameter_group.rds_v57.name}"
    enabled_cloudwatch_logs_exports = ["audit","error","general"]

}

resource "aws_rds_cluster_instance" "rds-instance" {
    cluster_identifier = "${aws_rds_cluster.rds-cluster.id}"
    identifier = "tfvalidated-instance"
    apply_immediately = true
    instance_class = "db.t3.small"
    engine                  = data.aws_rds_orderable_db_instance.rds.engine
    engine_version          = data.aws_rds_orderable_db_instance.rds.engine_version
    publicly_accessible = true
   db_parameter_group_name      = "${aws_db_parameter_group.rds_v57.name}"
    
    db_subnet_group_name = "${aws_db_subnet_group.rds.id}"


    depends_on = [aws_db_parameter_group.rds]
 
}

resource "aws_db_parameter_group" "rds_v57" {
 name = "test-database-parameter-v57"
 family = "aurora-mysql5.7"

  parameter {
    name  = "slow_query_log"
    value = "1"
  }

  parameter {
    name  = "long_query_time"
    value = "0"
  }

}

resource "aws_rds_cluster_parameter_group" "rds_v57" {
 name = "test-database-parameter-v57"
 family = "aurora-mysql5.7"

}

data "aws_rds_orderable_db_instance" "rds" {
  engine                     = aws_rds_cluster.rds-cluster.engine
  engine_version             = aws_rds_cluster.rds-cluster.engine_version
}

Steps to Reproduce

previously provisioned mysql aurora resource was having below setting.

    engine_version = "5.6.mysql_aurora.1.23.4"
    engine = "aurora"

To upgrade the cluster both parameters get replace with.

    engine_version = "5.7.mysql_aurora.2.10.2"
    engine = "aurora-mysql"

Also created additional parameter group thats support aurora-mysql5.7 family.

Terraform plan shows both cluster & instance force replacement and its due to the engine change.

Debug Output

No response

Panic Output

No response

Important Factoids

• Upgrade should be in place nor force replacement.
• keeping engine=aurora is giving me some different short of error like: InvalidParameterCombination: The current DB instance parameter group test-database-parameter is custom. You must explicitly specify a new DB instance parameter group, either default or custom, for the engine version upgrade.

References

No response

Would you like to implement a fix?

No response

[github-actions[bot]]

Community Note

Voting for Prioritization

• Please vote on this issue by adding a 👍 reaction to the original post to help the community and maintainers prioritize this request.
• Please see our prioritization guide for information on how we prioritize.
• Please do not leave "+1" or other comments that do not add relevant new information or questions, they generate extra noise for issue followers and do not help prioritize the request.

Volunteering to Work on This Issue

• If you are interested in working on this issue, please leave a comment.
• If this would be your first contribution, please review the contribution guide.

[alucardgt86]

This issue is still occurring and with AWS retiring 5.6 we need to be able to upgrade. I have noted that https://github.com/cloudposse/terraform-aws-rds-cluster/pull/151/commits/12428083b1ba059923ae08e904e2fbb6a65190ed This provider fixed the issue with the parameter group not being included as part of the major upgrade option with a join.

[ferschubert-hm]

I have managed to upgrade it thanks to this issue and several hours of troubleshooting.

In short:

• Add to aws_db_parameter_group and aws_rds_cluster_parameter_group

  lifecycle {
    create_before_destroy = true
  }

• Add to aws_rds_cluster

  engine                           = local.legacy_engine ? "aurora" : local.mysql_engine
  engine_version                   = local.mysql_version
  engine_mode                      = "provisioned"
  allow_major_version_upgrade      = local.allow_major_version_upgrade
  db_instance_parameter_group_name = local.allow_major_version_upgrade ? aws_db_parameter_group.default.name : ""

• Basically engine cannot be changed to aurora-mysql this always triggers a replacement, but keeping it aurora and changing engine_version to a v2 version (5.7) works. Not an elegant solution but the only one I have managed to find.
• Set allow_major_version_upgrade to true
• You need to add the db_instance_parameter_group_name as well if allow_major_version_upgrade is true
• Remove engine from aws_rds_cluster_instance
• After the upgrade the engine should be set to aurora-mysql

[devonbleak]

At this point AWS is forcing updates to 5.7 since 5.6 went EOL last week.

Amazon Aurora MySQL 1 (with MySQL 5.6 compatibility) will reach end of life on February 28, 2023. We are providing you with a one week notice so you have sufficient time to upgrade your database cluster(s). You can find additional information needed to plan your upgrade including a detailed timeline with milestones in the 'Preparing for Amazon Aurora MySQL-Compatible Edition version 1 end of life' documentation.

Clusters that are running these deprecated engine versions after March 1, 2023 will be upgraded on your behalf within a maintenance window after March 1, 2023. The major version upgrade will start within your maintenance window and typically finish within that timeframe. Depending on the cluster and the database activity at the time, the upgrade process may extend beyond the maintenance window. For more information, please refer to the Amazon RDS maintenance window.

Terraform now wants to re-create clusters that have been updated out from under it - I'd propose some action to make this a better experience. Maybe make aurora and aurora-mysql equivalent for some period until we can remove aurora as an option in v5.0.0 (or even remove the default entirely)?

[alucardgt86]

I was able to update via console and match terraform to the updated cluster. No ideal but EOL approaching meant actioning it.