permadiff on google_sql_database_instance

[wyardley]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request.
• Please do not leave +1 or me too comments, they generate extra noise for issue followers and do not help prioritize the request.
• If you are interested in working on this issue or have submitted a pull request, please leave a comment.
• If an issue is assigned to the modular-magician user, it is either in the process of being autogenerated, or is planned to be autogenerated soon. If an issue is assigned to a user, that user is claiming responsibility for the issue. If an issue is assigned to hashibot, a community member has claimed the issue already.

I'm running into an issue with permadiff

  ~ resource "google_sql_database_instance" "this" {
        id                            = "xx-bbee"
        name                          = "xx-bbee"
        # (12 unchanged attributes hidden)

      ~ settings {
            # (10 unchanged attributes hidden)

          ~ location_preference {
              - secondary_zone = "us-west2-c" -> null
                # (1 unchanged attribute hidden)
            }

            # (10 unchanged blocks hidden)
        }
    }

Note: this is likely not reproducible - we have the same code running in multiple projects and with multiple instances, and this is only affecting one of them. However, it is sticky / consistent, and seems to have popped up after updating from 4.27.0 to 4.28.0, though that's not 💯 for sure.

Terraform Version

Terraform v1.2.5
on darwin_arm64
+ provider registry.terraform.io/hashicorp/google v4.28.0
+ provider registry.terraform.io/hashicorp/google-beta v4.28.0
+ provider registry.terraform.io/hashicorp/random v3.3.2

Affected Resource(s)

• google_sql_database_instance

Terraform Configuration Files

locals {
  default_database_flags = {
    "cloudsql.iam_authentication" = "on",
    "log_checkpoints" = "on",
    "log_connections" = "on",
    "log_disconnections" = "on",
    "log_lock_waits" = "on",
    "log_temp_files" = "0",
  }
  database_flags = merge(var.database_flags, local.default_database_flags, {})
}

resource "random_id" "name_suffix" {
  byte_length = 2
}

resource "google_sql_database_instance" "this" {
  name     = var.add_random_id ? "${var.name}-${random_id.name_suffix.hex}" : var.name
  provider = google-beta

  region              = var.region
  database_version    = var.database_version
  deletion_protection = var.deletion_protection


  settings {
    availability_type = "REGIONAL"
    tier              = var.tier

    user_labels = var.labels

    dynamic "database_flags" {
      for_each = local.database_flags

      content {
        name  = database_flags.key
        value = database_flags.value
      }
    }

    location_preference {
      zone = "${var.region}-a"
    }

    ip_configuration {
      require_ssl     = true
      private_network = var.ip_configuration_network_id
    }

    maintenance_window {
      day  = 3
      hour = 2
    }

    backup_configuration {

      enabled = true
      location = "us"
      start_time = "08:00"
      backup_retention_settings {
        retained_backups = var.backups_retention_count
      }
    }
  }
}

Debug Output

Between a "working" (no permadiff) and instance with the permadiff, the locationPreference bit in the API response from Google looks the same to me:
"good"

{
 "kind": "sql#instance",
 "state": "RUNNABLE",
 "databaseVersion": "POSTGRES_14",
 "settings": {
  "authorizedGaeApplications": [],
  "tier": "db-f1-micro",
  "kind": "sql#settings",
  "userLabels": {
   "owner": "owner1"
  },
  "availabilityType": "REGIONAL",
  "pricingPlan": "PER_USE",
  "replicationType": "SYNCHRONOUS",
  "activationPolicy": "ALWAYS",
  "ipConfiguration": {
   "privateNetwork": "projects/xyz/global/networks/vvv-vpc-01",
   "authorizedNetworks": [],
   "ipv4Enabled": true,
   "requireSsl": true
  },
  "locationPreference": {
   "zone": "us-west2-a",
   "kind": "sql#locationPreference"
  },

{
 "kind": "sql#instance",
 "state": "RUNNABLE",
 "databaseVersion": "POSTGRES_14",
 "settings": {
  "authorizedGaeApplications": [],
  "tier": "db-f1-micro",
  "kind": "sql#settings",
  "userLabels": {
   "owner": "owner2"
  },
  "availabilityType": "REGIONAL",
  "pricingPlan": "PER_USE",
  "replicationType": "SYNCHRONOUS",
  "activationPolicy": "ALWAYS",
  "ipConfiguration": {
   "privateNetwork": "projects/xyz/global/networks/vvv-vpc-01",
   "authorizedNetworks": [],
   "ipv4Enabled": true,
   "requireSsl": true
  },
  "locationPreference": {
   "zone": "us-west2-a",
   "secondaryZone": "us-west2-c",
   "kind": "sql#locationPreference"
  },

I can't easily provide full debug output, but happy to do some more troubleshooting, or provide more information if needed.

Panic Output

Expected Behavior

Terraform plans clean, or applies once and does not show a diff on next plan / apply.

Actual Behavior

Terraform has a permadiff on this resource for this specific instance

Steps to Reproduce

1. terraform apply

Important Factoids

References

• google_sql_database_instance - support for secondary_zone GoogleCloudPlatform/magic-modules#6178

[wata727]

Same issue here. Perhaps this is the effect of secondary_zone added in v4.28.0. In our case, we were able to work around this issue by setting the secondary_zone attribute.

[wyardley]

Thanks for that reference. Also mentioning
terraform-google-modules/terraform-google-sql-db#320
and the upstream for the issue you linked
GoogleCloudPlatform/magic-modules#6178

[wyardley]

The issue does seem to be that secondaryZone (which is now supported) is set at the API level for the affected instance; however, as best I know, we haven't set it explicitly in the past.

Wondering if there's any way to unset it (e.g., using gcloud sql instances patch), vs. adjusting the terraform code to match.

[wata727]

Interestingly, I noticed that this issue occurs via changes from the Cloud Console. The reproduction steps are as follows:

1. Create Cloud SQL instance with avalability_type = "REGIONAL" in v4.27.0

terraform {
  required_providers {
    google = { version = "~> 4.27.0" }
  }
}

provider "google" {
  credentials = file("credentials.json")

  project = "<PROJECT>"
  region  = "us-central1"
  zone    = "us-central1-c"
}

resource "google_sql_database_instance" "main" {
  name             = "main-instance"
  database_version = "POSTGRES_14"
  region           = "us-central1"

  settings {
    tier = "db-f1-micro"
    availability_type = "REGIONAL"

    location_preference {
      zone = "us-central1-a"
    }
  }
}

2. Update Cloud SQL instance without changes via the Cloud Console

This may seem unnatural, but sometimes after changing settings from the Cloud Console, we change the Terraform configuration to follow.

3. Upgrade provider to v4.29.0

terraform {
  required_providers {
-    google = { version = "~> 4.27.0" }
+    google = { version = "~> 4.29.0" }
  }
}

4. Run terraform plan

  # google_sql_database_instance.main will be updated in-place
  ~ resource "google_sql_database_instance" "main" {
        id                            = "main-instance"
        name                          = "main-instance"
        # (11 unchanged attributes hidden)

      ~ settings {
            # (10 unchanged attributes hidden)

          ~ location_preference {
              - secondary_zone = "us-central1-b" -> null
                # (1 unchanged attribute hidden)
            }

          - maintenance_window {
              - day  = 0 -> null
              - hour = 0 -> null
            }

            # (2 unchanged blocks hidden)
        }

        # (1 unchanged block hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.

This is an issue with updates in the Cloud Console and may be beyond Terraform's responsibility.

[wyardley]

Interestingly, I noticed that this issue occurs via changes from the Cloud Console. The reproduction steps are as follows:

That tracks with what I know - I can imagine that someone may have viewed or edited (as a noop) the instance in the cloud console, and also agree that this may be outside of what the provider itself can solve (i.e., it's not really a bug or provider permadiff, however, as I don't recall anyone setting this explicitly for this instance vs. any other, the behavior is still unexpected / undesirable.

[wyardley]

So maybe closing this and creating an issuetracker ticket is the right thing? I asked on the GCP slack if there's an "under the hood" way to zero out that field again, but haven't gotten a response yet.

[edwardmedia]

@wata727 @wyardley I can't repro this using the config here. Reviewed the provider code, secondary_zone is defined optional. Based on the response under output it appears API sets a value to secondary_zone under certain circumstances even you did not config it. I have tried different configurations and not able to identify the circumstances. Anyway, it appears to be an issue at API. You may file an issue with Cloud SQL if you want. The provider's behavior is as by design. Closing the issue now. Please reopen if it comes back. Thanks

[wyardley]

Thanks all
@edwardmedia yes, after @wata727's comments, I agree that this is not a provider bug

@wata727 I created
https://issuetracker.google.com/issues/240108929
if you'd like to follow that or add additional comments

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.