can't modify google_sql_database_instance after patching it through gcloud sql instances patch command

[vladyege]

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.13.5
provider google v3.47.0
provider google-beta v3.47.0

Affected Resource(s)

• google_sql_database_instance

Terraform Configuration Files

resource "google_sql_database_instance" "aws_ro_replica" {
  name = "aws-ro-replica-${random_id.replica_instance.hex}"
  master_instance_name = google_sql_source_representation_instance.aws.name

  project = var.gcp_project.id
  region = var.gcp_project.region
  database_version = "MYSQL_5_7"

  settings {
    tier = "db-g1-small"
    disk_type = "PD_SSD"
    disk_size = 10
    disk_autoresize = true

    ip_configuration {
      ipv4_enabled = true
      private_network = module.vpc.network_self_link

      dynamic "authorized_networks" {
        for_each = var.authorized_networks
        content {
          name = lookup(authorized_networks.value, "display_name", "")
          value   = lookup(authorized_networks.value, "cidr_block", "")
        }
      }
    }
    backup_configuration {
      enabled = false
      binary_log_enabled = false
    }

    user_labels = {
      dms_instance = true
      env = var.env
    }
  }

  replica_configuration {
    dump_file_path = "gs://path/dump.sql"
    failover_target = false
    username = var.aws_ro_replica_config.user
    password = var.aws_ro_replica_config.pass
  }
  deletion_protection = false

  lifecycle {
    ignore_changes = [
      settings[0].backup_configuration[0]
    ]
  }
  provisioner "local-exec" {
    command = "yes Y | gcloud sql instances patch --enable-bin-log --enable-database-replication ${google_sql_database_instance.aws_ro_replica.name} --project=${var.gcp_project.id}"
  }
}

Debug Output

https://gist.github.com/vladyege/853f7c104fb1c2e6a8b8143778d61d7b

Expected Behavior

As it's known binary logs on google sql instance can be enabled if backup is enabled. Back in its turn may not be enabled on read only replica instances. Nevertheless Google allows to enable binary logs on a read only replica instance after it's created by using the following CLI command
gcloud sql instances patch --enable-bin-log [instance]

So we start an instance with the following settings
backup_configuration {
enabled = false
binary_log_enabled = false
}

lifecycle block
lifecycle {
ignore_changes = [
settings[0].backup_configuration[0]
]
}

After patching an instance and changing binary_log_enabled parameter to "true" and ignoring it during next apply, an instance should be successfully modified if any other changes on resource.

Actual Behavior

During modification, the following error is reproduced
Error: Error, failed to update instance settings for : googleapi: Error 400: Invalid request: Binary log must be disabled when backup is disabled or the instance must be a replica instance with a MySQL 5.7 or above version., invalid

Steps to Reproduce

1. terraform apply
2. change some parameter in google_sql_database_instance, for an example add new label in "user_labels" block
3. terraform apply

• #0000

[edwardmedia]

@vladyege help me understand what you want to achieve. Did you want to run the gcloud command each time you call tf apply? If so, have you verified if you can rerun the gcloud command?

[vladyege]

@edwardmedia Hi,
No, I can run gcloud command from provisioner "local-exec" or manually in console. It's not a problem. The problem is that I can't modify an instance after that. If I make some changes in google_sql_database_instance after patching an instance and then run "terraform apply" I've got above described error
Error: Error, failed to update instance settings for : googleapi: Error 400: Invalid request: Binary log must be disabled when backup is disabled or the instance must be a replica instance with a MySQL 5.7 or above version., invalid

notwithstanding I have a lifecycle block with ignoring changes in binary_log_enabled and enabled

[edwardmedia]

@vladyege I see. Looking at the error Binary log must be disabled when backup is disabled or the instance must be a replica instance with a MySQL 5.7 or above version..., it seems to be a legit sql rule. Have you verified the patch you had applied is still compatible with your Terraform config?

[edwardmedia]

@vladyege have you verified if the gcloud command actually has changed the behavior that causes the error in further TF run?

[edwardmedia]

@vladyege Closing, assuming it is no longer an issue

[vladyege]

@edwardmedia
please reopen an issue. I've not managed to answer and you closed it immediately. Not nice, frankly speaking.
What did you mean by "have you verified if the gcloud command actually has changed the behavior that causes the error in further TF run?"
I provided all the information, logs, scenario how to reproduce it. Could you please start investigating it. 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 👉 hashibot-feedback@hashicorp.com. Thanks!