Terraform-provider-google: terraform updates authorized networks in google_sql_database instance every time plan/apply is run

[dbamohsin]

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.

Terraform Version

0.11.13

Affected Resource(s)

• google_sql_database instance

Terraform Configuration Files

variable "authorized_networks" {
  description = "The networks that can connect to cloudsql"
  type        = "list"

  default = [
    {
      name  = "Manchester (DC1)"
      value = "193.1.1.1/32"
    },
    {
      name  = "Manchester (DC2)"
      value = "193.0.0.0/32"
    }
  ]
}


#Create the instance
resource "google_sql_database_instance" "master" {
  count            = "${var.create_master}"
  name             = "${var.instancename}"
  database_version = "${var.dbtype}"
  project          = "${var.project}"
  region           = "${var.region}"

  settings {
    tier      = "${var.instance_tier}"
    disk_size = "${var.initial_disk_size_gb}"
    disk_type = "PD_SSD"

    location_preference {
      zone = "${var.zone}"
    }

    ip_configuration {
      require_ssl = true
      ipv4_enabled        = true
      authorized_networks = "${var.authorized_networks}"
    }

    backup_configuration {
      binary_log_enabled = "${var.binlog_enabled}"
      enabled            = "${var.backup_enabled}"
      start_time         = "${var.start_time_hour}"
    }

    maintenance_window {
      day  = "${var.maint_window_day}"
      hour = "${var.maint_window_hour}"
    }

    database_flags = "${var.flags}"

    user_labels {
      app = "${var.instancename}"
    }
  }

  timeouts {
    create = "30m"
    delete = "60m"
    update = "24h"
  }
}

Expected Behavior

If there are no changes to authorized networks, then the terraform plan or apply should not show authorized networks that need updating.

Actual Behavior

every time a plan or apply is run, terraform shows authorized networks need updating. eg:

  ~ module.cloud-sql-mos-sandbox.google_sql_database_instance.master
      settings.0.ip_configuration.0.authorized_networks.1271356474.expiration_time: "" => ""
      settings.0.ip_configuration.0.authorized_networks.1271356474.name:            "" => "My DC (DC2)"
      settings.0.ip_configuration.0.authorized_networks.1271356474.value:           "" => "193.0.0.0/32"
      settings.0.ip_configuration.0.authorized_networks.1583501150.expiration_time: "" => ""
      settings.0.ip_configuration.0.authorized_networks.1583501150.name:            "" => "My DC (DC1)"
      settings.0.ip_configuration.0.authorized_networks.1583501150.value:           "" => "193.1.1.1/32"
      settings.0.ip_configuration.0.authorized_networks.2376941909.expiration_time: "1970-01-01T00:00:00Z" => ""
      settings.0.ip_configuration.0.authorized_networks.2376941909.name:            "Manchester (DC1)" => ""
      settings.0.ip_configuration.0.authorized_networks.2376941909.value:           "193.1.1.1/32" => ""
      settings.0.ip_configuration.0.authorized_networks.2550682161.expiration_time: "1970-01-01T00:00:00Z" => ""
      settings.0.ip_configuration.0.authorized_networks.2550682161.name:            "Manchester (DC2)" => ""
      settings.0.ip_configuration.0.authorized_networks.2550682161.value:           "193.0.0.0/32" => ""
Plan: 0 to add, 1 to change, 0 to destroy.

Steps to Reproduce

1. Create a google_sql_database_instance resource with an ip_configuration block that holds authorized_networks.
2. create the resource using terraform apply
3. from the second run onwards, terraform tries to update the resource even though no changes have been made.

[rmeffan]

Can confirm the same behavior with Terraform v0.11.8; we first noticed it on Tue, 2 Apr 2019.

I opened a ticket with Google Support yesterday. It appears to be a Google Cloud SDK or CloudSQL issue, given that we can reproduce the behavior by repeatedly running the following command against any of our CloudSQL instances:

gcloud sql instances describe cloudsql_instance_name \
    --project google_project_name \
    --format=value\(settings.ipConfiguration.authorizedNetworks[0].expirationTime\)

The response should be an empty string. However, ~20-30% of the time, the response is the epoch time in ISO 8601 format: 1970-01-01T00:00:00Z

[dbamohsin]

Thats very reassuring to know - i thought i was going mad because its only happening intermittently so sometimes the plan is fine, and you run it again after 5 minutes and its an update even though nothing has changed.

I'd agree that we also first noticed it on Tuesday 2nd.

[jwcmd]

~20-30% of the time

This is just anecdotal but we're seeing this too and it feels a heck of a lot more frequent then just 20-30% of the time. I'm having to run terraform apply ~10-15 times before it reports no changes.

[dbamohsin]

hi,

as a workaround while google work on a bug fix - i tried to add the following to my cloudsql module:

    lifecycle {
        ignore_changes = [
            "settings.0.ip_configuration.0.authorized_networks.%"
        ]
    }

but doesnt seem to have the desired impact. Has anyone else tried this or have a workaround?

Also tried at more granular level:

    lifecycle {
        ignore_changes = [
            "settings.0.ip_configuration.0.authorized_networks.%.expiration_time"
        ]
    }

[dbamohsin]

Hi,

FYI from Google.

Thank you for contacting Google Cloud Platform Support.

I did try to reproduce your issue on my project, and I got the same results as for your instance. It means that it is an API issue.

The issue was previously known to our engineers. I did noticed our engineers about your issue. I did also create public issue for you.

For the time being, I do not know any workaround for your issue. Also please note that there is no ETA to resolve it.

the issue can be tracked here: https://issuetracker.google.com/130014385

Thanks

Mos

[rmeffan]

FYI, received this from our Google Support rep last night:

We have a consensus! I will be consulting with the SQL API developers since this has been confirmed as an API issue instead of Terraform, good catch. I will keep you updated as I know more.

[rmeffan]

One additional note : I tried setting expiration_time = 1970-01-01T00:00:00Z in a TF CloudSQL instance config authorized_networks stanza, but the result was the same behavior in the opposite manner: 20-30% of the time, the defined value would be stripped by Google and replaced with an empty string. 🙄

Admittedly, the above was a punt. Had hoped setting the value to the epoch might act as a -1 equivalent (common for a 'never expire' value in many things), but nooooope.

[rileykarson]

This should be resolved; there was an error in the the API caused by a partial rollout of a bad deployment that's now been rolled back. I'll leave this issue open for a few hours in case anyone is seeing this problem still, and close it out if not.

[rmeffan]

Ran a wrapper script that executes the gcloud command I posted above, and ran Terraform plan several times. The reported issue appears to have been resolved.

#COMPUTE_PROJ,#TOTAL_PASSES,#COUNT_WITH_EXPIRATION,#COUNT_WITHOUT_EXPIRATION
redacted,300,0,300

[rileykarson]

Thanks! I'll close this out in that case, if anyone experiences it still feel free to comment.

[hashibot[bot]]

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 feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 [email protected]. Thanks!