Cannot update Cloud SQL read replica instance with binary logging enabled

[shohei-ihaya]

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 v1.1.7

Affected Resource(s)

• google_sql_database_instance

Terraform Configuration Files

data "google_project" "project" {}

resource "google_sql_database_instance" "master" {
  project          = data.google_project.project.project_id
  region           = "asia-northeast1"
  name             = "mts-issue-test-master-001"
  database_version = "MYSQL_5_7"

  settings {
    availability_type = "ZONAL"
    disk_autoresize   = true
    disk_size         = 10
    disk_type         = "PD_SSD"
    tier              = "db-f1-micro"

    activation_policy = "ALWAYS"
    pricing_plan      = "PER_USE"

    backup_configuration {
      binary_log_enabled = true
      enabled            = true
      location           = "asia"
      start_time         = "18:00"
    }


    database_flags {
      name  = "character_set_server"
      value = "utf8mb4"
    }
  }
}


resource "google_sql_database_instance" "replica" {
  depends_on           = [google_sql_database_instance.master]
  name                 = "mts-issue-test-replica-001"
  project              = data.google_project.project.project_id
  master_instance_name = "mts-issue-test-master-001"
  region               = "asia-northeast1"
  database_version     = "MYSQL_5_7"
  replica_configuration {
    failover_target = false
  }
  settings {
    tier              = "db-f1-micro"
    availability_type = "ZONAL"
    pricing_plan      = "PER_USE"
    disk_autoresize   = true
    disk_size         = 10

    backup_configuration {
      binary_log_enabled = true
    }

    database_flags {
      name  = "slave_parallel_workers"
      value = "3"
    }

    database_flags {
      name  = "slave_parallel_type"
      value = "LOGICAL_CLOCK"
    }

    database_flags {
      name  = "slave_pending_jobs_size_max"
      value = "536870912" # 512MB
    }
  }
}

Debug Output

when create new replica with binary logging (success)

https://gist.github.com/Climber22/09449433d0433104dfa7f8b74b04df42

when update replica (failed)

https://gist.github.com/Climber22/bc2abc80e52bb1bcb9e18f261180d6f7

Panic Output

Expected Behavior

When you run terraform apply, updating Cloud SQL (read replica instance with binary logging enabled) completes without error.

Actual Behavior

When you add some diff and run terraform apply again, updating Cloud SQL was failed with error message below.

│ 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
│ 
│   with google_sql_database_instance.replica,
│   on cloudsql.tf line 35, in resource "google_sql_database_instance" "replica":
│   35: resource "google_sql_database_instance" "replica" {

Terraform tries to update read replica (mysql 5.7), so error message above looks wrong.
I wrote information about the reason for this confusing error message (Reason of this issue section)

Steps to Reproduce

When you firstly run terraform apply, Cloud SQL instance will be successfully created without error.
(Thanks for This PR, when create new one, PATCH method is explicitly called to enable binary_logging for replica instance )

Then, if you add some diff, and run terraform apply again, updating will be failed.

Important Factoids

The problem

When try to update some params of read replica instance with binary logging enabled, terraform fails.

Reason of this issue

I ask this issue for GCP support, and they said that “This is because the difference between PATCH and PUT method”.

In detail, PATCH method will evaluate diff between current status and request body, and apply only diff.
On the other hand, PUT method will try to overwrite all parameter with request body.

ref: https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/instances/patch?hl=ja

Updates settings of a Cloud SQL instance. This method supports patch semantics.

In this case, when terraform try to update Cloud SQL by PUT method, there is no information to show “this is replica instance”.
To add this information, terraform should add instanceType params and specify it as “READ_REPLICA_INSTANCE“
ref: https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/instances?hl=ja#SqlInstanceType

(I tried to call PUT method with instanceType parameter manually, and it successfully completed.)

How to resolve this issue

In my opinion, adding instanceType field to google_sql_database_instance resource will solve this issue.

Additionally, If google_sql_database_instance has instanceType field, creation of read replica with binary logging enabled can be done without PATCH method ( I’ve not tried it, just my hypothesis )

References

[zculek-fb]

Hello,

This bug/issue is still present, even though it was supposedly fixed in GoogleCloudPlatform/magic-modules#4907.

We are using the latest provider and TF, and the issue is still present. Other people have also confirmed it, in e.g. #12148, but I cannot reopen nor comment on that thread to confirm it there as well. We would really like to see this fixed, instead of having to use workarounds, which often cause some issues, as we have to manually (via gcloud) temporarily disable binary logging on replicas in order to update the conf and then manually enable it back, having to set ignore_changes = [ settings[0].backup_configuration[0] ] in lifecycle so that TF doesn't enable it back again in conf.

We are using MySQL 8.x, and the error still says:

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

Binary logging on read replicas can be enabled even when backups disabled - https://cloud.google.com/sql/docs/mysql/replication#bin-log-replica

Some info on the issue and solution here: https://discuss.hashicorp.com/t/error-when-provision-read-replica-with-binary-log-enabled/42255

Based on the verbose logs you provided, as well as the findings from the Cloud SQL Product Team, we were able to ascertain that the issue is present on Terraform, as it does not include the “instanceType” parameter which is required in order for the operation to succeed, as this can only be performed for instances of type “READ_REPLICA_INSTANCE”, or for instances with binary log disabled.

Please let me know if you need more information or any additional logs.

@ScottSuarez @edwardmedia @muhammadaldyan

[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.