Importing CloudSQL replica instance causes errors with settings.maintenance_window

[YoSmudge]

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

Terraform v0.10.8
Google provider 2.9.1

Affected Resource(s)

• google_sql_database_instance

Terraform Configuration Files

resource "google_sql_database_instance" "replica" {
  name                 = "${local.db_instance_name}-failover"
  database_version     = "${var.db_version}"
  region               = "${var.gcp_region}"
  master_instance_name = "${google_sql_database_instance.primary.name}"

  replica_configuration {
    failover_target = true
  }

  settings {
    tier                   = "${var.instance_type}"
    disk_type              = "${var.disk_type}"
    disk_size              = "${var.disk_size}"
    disk_autoresize        = true
    crash_safe_replication = true

    ip_configuration {
      ipv4_enabled    = true
      private_network = "${data.google_compute_network.network.self_link}"
    }

    maintenance_window {
      day          = "2"
      hour         = "9"
    }
  }

  depends_on = ["google_sql_database_instance.primary"]
}

Expected Behavior

CloudSQL replica imported with terraform import with matching configuration should show no changes.

Actual Behavior

When setting the settings.maintenance_window options to match the configuration in Google Cloud, terraform plan shows

I, [2019-06-27T11:55:07.517805 #507]  INFO -- :       settings.0.maintenance_window.0.day:                                         "0" => "2"
I, [2019-06-27T11:55:07.517954 #507]  INFO -- :       settings.0.maintenance_window.0.hour:                                        "0" => "9"

When attempting to apply this Google API returns

module.[redacted].google_sql_database_instance.replica: Modifying... (ID:[redacted]-failover)
  settings.0.maintenance_window.0.day:                                         "0" => "2"
  settings.0.maintenance_window.0.hour:                                        "0" => "9"
google_sql_database_instance.replica: Error, failed to update instance settings for : googleapi: Error 400: Invalid request: Cannot update maintenance window for replica instances., invalid

Setting the parameters to 0 returns a Terraform error with plan

Error: module.[redacted].google_sql_database_instance.replica: expected settings.0.maintenance_window.0.day to be in the range (1 - 7), got 0

Removing the entire maintenance_window and running terraform apply with any other change to the google_sql_database_instance resource returns

module.[redacted].google_sql_database_instance.replica: Modifying... (ID: [redacted]-failover)
  settings.0.maintenance_window.#:                                             "1" => "0"
google_sql_database_instance.replica: Error, failed to update instance settings for : googleapi: Error 400: Invalid request: Cannot update maintenance window for replica instances., invalid

Adding

  lifecycle{
    ignore_changes = ["settings.0.maintenance_window"]
  }

and running apply with any other change to the google_sql_database_instance returns

google_sql_database_instance.replica: Error, failed to update instance settings for : googleapi: Error 400: Invalid request: Invalid maintenance window. Day of week is unspecified., invalid

Steps to Reproduce

1. import a google_sql_database_instance replica via terraform import resource
2. make any change to the google_sql_database_instance resource

[RafiGreenberg]

I'm hitting this too. Only happens with imported replica instances. Probably has something to do with the fact that read replicas don't support configuration of maintenance window.

Terraform v0.12.6

• provider.google v2.10.0
• provider.google-beta v2.10.0

[RafiGreenberg]

Anyone looking into this?

[codersasha]

I'm experiencing this too. Might be because Cloud SQL instances allow "any" as a maintenance window spec.

[RafiGreenberg]

Still waiting on a fix for this...

[RafiGreenberg]

@rileykarson

[mercuriete]

same problem
any workaround?

[mercuriete]

trying to edit remote state on GCS to do a workaround fails due to refresh is setting always this:

                "maintenance_window": [
                  {
                    "day": 0,
                    "hour": 0,
                    "update_track": "stable"
                  }
                ],

My intention is to fix by hand the remote state.
I thought that if I fix the remote state then the apply doesn't change and I could continue with another changes.
But the problem is terraform refresh is changing again the remote state to that previous error vaules.

Someone can share their experiencies trying to edit the remote state?

this bug is blocking me so priority should be high.

[mercuriete]

ok...
I can continue for now.
What I did was trying to lie terraform in order that everything is up-to-date

1. do the modifications by hand adding more authorized_netwoks using the google console.
2. putting this:

    maintenance_window {
      update_track = "stable"
    }

this obviously fails because doesnt have days and it will return

google_sql_database_instance.replica: Error, failed to update instance settings for : googleapi: Error 400: Invalid request: Invalid maintenance window. Day of week is unspecified., invalid

but only if you have differences with your state.
3) change the plan using gcloud:

gcloud sql instances patch --pricing-plan=PER_USE  instance-name

With all this changes done by hand.... terraform thinks the state is up-to-date and I can continue working by now.

Please fix this bug :)

The suggestion to fix the bug is...
when the db is a replica, ignore the maintenance_window and dont write to the state and dont use the api call with this parameter to avoid misusage of google api.

[RafiGreenberg]

This is kind of ridiculous as this point. The bug has been open for almost 8 months. Nobody seems to care.

[rileykarson]

Ah, I'd been trying to look at this but couldn't figure it out. I've lost all context- unassigning myself so that someone else can pick it up.

[mercuriete]

Please someone pick this bug.

I just give it up messing with my remote state. The DB replica support is broken that means I can't use terraform on my production systems.
Anyway my boss doesn't like to have terraform on important assets so I just leave the databases out of terraform.

I am looking forward to this bug to be fixed.

Thanks in advance.

[RafiGreenberg]

I was actually able to work around it by removing those replicas completely from remote state, then re-importing them without the maintenance_window defined

[mercuriete]

@mackoftrack can you explain better on what you mean on "reimporting them without the maintenance_window defined"?

delete all DB from state.
go to google cloud console web and disable maintenance window using de UI?
reimport again?

Thanks for your help.
(this is only curiosity because I am not using terraform on DB or at least I am not using continuous delivery with dangerous assets)

[edwardmedia]

@YoSmudge @mackoftrack @mercuriete Here is what from cloudsql doc:
You can't set a maintenance window directly on a failover instance, because it shares the maintenance window of the primary instance.

https://cloud.google.com/sql/docs/mysql/maintenance#how_does_maintenance_affect_read_replicas_and_failover_instances

From GCP console, I don't see maintenance window available for the failover instance. How did you set their values initially?

Can you provide full debug logs so we can take a closer look?

Please take a look, and let me know if this address your issue. Thanks

[mercuriete]

We didn't do anything.
just import and the terraform provider tries to missuse the API.
the provider itself set the state in a bad state and tries to set a maintenance window on objects that are impossible to do so.
I will try to reproduce the error, but it is very clear in the description of this ticket.

Edit: Probably is more useful to you if I can provide you the output of the API response when terraform is doing a refresh and a import. Can you tell me the way of debugging API Calls made by terraform?

[edwardmedia]

@mercuriete what do you see terraform show? To obtain the debug log, in your terminal, create the environment variable export TF_LOG=DEBUG first, and then you can start to run terraform commands. Yes, please post full debug logs. Thanks

[mercuriete]

Im no longer working on that company.
So I cant reproduce the error.
I am unsuscribing this issue.
Thanks for your help and this amazing piece of software.

[edwardmedia]

I am closing this issue. Feel free to reopen it as someone finds a same issue. Thanks

[ghost]

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!