Can't create Failover or Replica Cloud SQL instances

[goobysnack]

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 version: 0.12.24

Affected Resource(s)

resource "google_sql_database_instance"

Terraform Configuration Files

mssql.tf:
module "gcp_csql_instances" {
  source        = "git::https://<removed>/tf-modules/gcp-csql_ms?ref=master"

  project_id          = module.gcp_project.project_id
  region              = "us-central1"
  environment         = module.gcp_project.environment
  master_name         = "master-np-1"
  failover_name       = "failover-1"
  replica_name        = "replica"
  db_version          = "SQLSERVER_2017_ENTERPRISE"
  tier                = "db-custom-8-30720"
  disk_size           = 300
  maint_day           = "5"
  maint_hour          = "1"
  replica_count       = 0
  cert_name           = "csql-cert-1"
  authorized_nets     = {<removed>
  }
  vltaddress          = module.gcp_project.vltaddress
  vault_token         = module.gcp_project.vaulttoken
}
+++++++
gcp-csql_ms module/main.tf

provider "google-beta" {
  credentials = ""
  version     = "~> 3.0"
}

resource "google_sql_database_instance" "failover" {
  provider  = google-beta

  depends_on           = [google_sql_database_instance.master]
  project              = var.project_id
  name                 = var.failover_name
  database_version     = var.db_version
  master_instance_name = google_sql_database_instance.master.name
  region               = var.region
  root_password        = random_password.root_password.result

  settings {
    tier                   = var.tier
    disk_size              = var.disk_size
    //replication_type       = "SYNCHRONOUS"
    //crash_safe_replication = true

    maintenance_window {
      day          = var.maint_day
      hour         = var.maint_hour
    }

    ip_configuration {
      ipv4_enabled = true
      require_ssl = true
      dynamic "authorized_networks" {
        for_each = var.authorized_nets
        iterator = authorized_nets
        content {
         name = "authorized_nets-${authorized_nets.key}"
         value = authorized_nets.value
        }
      }
    }
  }

  replica_configuration {
    failover_target = true
  }
}

Debug Output

https://gist.github.com/goobysnack/d612bebeb2a7d9d631ef0ac6fbf4badb

Panic Output

Expected Behavior

Failover Instance should have been created without issue.

Actual Behavior

Timeout after 20-30min of retries.

Steps to Reproduce

1. terraform apply

Important Factoids

References

• #0000

[goobysnack]

Is this path valid?

type.googleapis.com/google.cloud.sql.v1beta4.SqlInstancesInsertRequest

Also from Google Support:

I checked our logs and see the following error message:
Exception thrown by server-side application: Invalid operation type: CREATE_REPLICA
I spoke to our engineers who confirmed that read replicas are not supported yet in Cloud SQL for SQL Server

So, this should be called out in the docs, but isn't relevant to the core of this issue. I think they were seeing earlier failures when I had replica count to 1.

[edwardmedia]

@goobysnack where did you see type.googleapis.com/google.cloud.sql.v1beta4.SqlInstancesInsertRequest? Many things could trigger internal error. It's hard to tell what's wrong from that response. Could you post entire debug log? Also could you share you master instance code?

[goobysnack]

@goobysnack where did you see type.googleapis.com/google.cloud.sql.v1beta4.SqlInstancesInsertRequest? Many things could trigger internal error. It's hard to tell what's wrong from that response. Could you post entire debug log? Also could you share you master instance code?

I can share the master, but the debug trace is very long and I don't know that I could reliably scrub it.

resource "google_sql_database_instance" "master" {
  //provider  = google-beta
  depends_on       = [random_password.root_password]
  project          = var.project_id
  name             = var.master_name
  database_version = var.db_version
  region           = var.region
  root_password    = random_password.root_password.result
  settings {
    tier             = var.tier
    disk_size        = var.disk_size
    //replication_type = "SYNCHRONOUS"

    backup_configuration {
      binary_log_enabled = true
      enabled            = true
    }
    maintenance_window {
      day          = var.maint_day
      hour         = var.maint_hour
    }

    ip_configuration {
      ipv4_enabled = true
      require_ssl = true
      dynamic "authorized_networks" {
        for_each = var.authorized_nets
        iterator = authorized_nets
        content {
         name = "authorized_nets-${authorized_nets.key}"
         value = authorized_nets.value
        }
      }
    }
  }

[goobysnack]

Looks like this is all that's needed in the master config settings section:
availability_type = "REGIONAL"

No separate failover instance creation required (it's automagical with the above). Also, read replicas are not supported.

This part of the doc needs to be updated to include SQL Server:
https://www.terraform.io/docs/providers/google/r/sql_database_instance.html

availability_type - (Optional) This specifies whether a PostgreSQL instance should be set up for high availability (REGIONAL) or single zone (ZONAL).

[edwardmedia]

@goobysnack have you resolved the issue? Regarding the replication_type Below are from the doc. Is this something you referred to?

Remove any fields that are not applicable to Second-generation instances: * settings.crash_safe_replication * settings.replication_type *

replication_type - (Optional, Deprecated) This property is only applicable to First Generation instances. First Generation instances are now deprecated, see here for information on how to upgrade to Second Generation instances. Replication type for this instance, can be one of ASYNCHRONOUS or SYNCHRONOUS.

[goobysnack]

Yup, replication type is commented. The issue is this in the provider docs. SQL Server needs to be added, not just PostgreSQL
@edwardmedia

availability_type - (Optional) This specifies whether a PostgreSQL instance should be set up for high availability (REGIONAL) or single zone (ZONAL).

[edwardmedia]

@goobysnack Below is what I see from the link you provided which is different from what you quoted. https://www.terraform.io/docs/providers/google/r/sql_database_instance.html
What suggestion of change do you suggest?

availability_type - (Optional) The availability type of the Cloud SQL instance, high availability (REGIONAL) or single zone (ZONAL).'

[goobysnack]

Weird. 18 days ago.

#6254

LOL. Not sure how or why my browser showed the previous version. Cheers, we're good.

[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 👉 hashibot-feedback@hashicorp.com. Thanks!