Error when provision read replica with binary log enabled

[muhammadaldyan]

I used this module to provision my cloudsql https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance

1. Provision master and read replica using terraform module above (version 5.7)
2. Enable binary log on read replica using gcloud command
3. But when running terraform apply, the error occurred :

│ 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 module.mysql.google_sql_database_instance.replicas[0],
│ on ../../../../modules/google-cloud-sql/mysql/read_replica.tf line 39, in resource "google_sql_database_instance" "replicas":
│ 39: resource "google_sql_database_instance" "replicas" {
│
╵

this issue related to : #9371

i tried using version 3.75.0 and latest version and error still on there.
also tried to create case support on GCP.

and this is he said :
I have been looking into this matter by attempting to reproduce the behavior you encounter, and I have been able to see the same error message you’re retrieving from executing your Terraform script.

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.

Given that this issue is caused by the Terraform provider making the API request, I suggest that you open an issue with the Terraform Team in their official GitHub repository 1 so that they may fix this issue in order to include the required “instanceType” parameter.

Alternatively, the Terraform team could switch from using the “PUT” method, which uses the instance.update method 2, to the “PATCH” method, which uses the instances.patch method 3. The latter automatically populates the fields that aren’t included within the request body, and should not cause the error message you’re encountering when the “instanceType” parameter is missing.

[edwardmedia]

@muhammadaldyan can you post the terraform config and gcloud command you used so I can repro the issue?

[muhammadaldyan]

verbose.txt
tf.txt
log.txt
plan (2).txt
log-with-verbose.txt

[muhammadaldyan]

main.txt
terraform.txt
terraform-tfvars.txt
variables.txt
versions.txt

gcloud command :

gcloud sql instances patch --enable-bin-log db-replica-1

[edwardmedia]

@muhammadaldyan I am curious why you want to update the config via gcloud command (step 2)? Was the error from creating the replica instance? What is the config for your master?

Are you aware of the requirement [here]?(https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#master_instance_name)

(Note, this requires the master to have binary_log_enabled set, as well as existing backups) 

Can you try update the master with below config first to see if your issue can be resolved?

    backup_configuration {
        binary_log_enabled = true
        enabled = true
    }

[caiodelgadonew]

I can confirm that this happens on any change of the settings for the google_sql_database_instance in a read replica,

We upgraded from provider 4.14 to 4.30, but we also tried lower versions from 4.25 up to 4.30, all them have the same issue.

My solution was to add a lifecycle to ignore_changes on the changed settings... but it is only a workaround.

Plan

  # google_sql_database_instance.recruitment-1-ro-1 will be updated in-place
  ~ resource "google_sql_database_instance" "recruitment-1-ro-1" {
        id                            = "recruitment-1-ro-1"
        name                          = "recruitment-1-ro-1"
        # (13 unchanged attributes hidden)

      ~ settings {
            # (10 unchanged attributes hidden)

          - active_directory_config {}

            # (5 unchanged blocks hidden)
        }

        # (2 unchanged blocks hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.
╷

This is what solved for us (workaround)

  lifecycle {
    ignore_changes = [
      settings["active_directory_config"]
    ]
  }

[edwardmedia]

@muhammadaldyan any change in below statement, which one as an example? In your case, there is a change here that might be related to yours

I can confirm that this happens on any change of the settings for the google_sql_database_instance in a read replica,

[edwardmedia]

@muhammadaldyan is this still an issue?

[edwardmedia]

Closing assuming this is no longer an issue

[caiodelgadonew]

Hello @edwardmedia , that is still happening, I did not replied because you where not asking me about it.

I can get a new output if you need..

[muhammadaldyan]

@edwardmedia yes it's still an issue. and this is not working. i used MySQL 5.7 (Cloudsql GCP)
lifecycle {
ignore_changes = [
settings["active_directory_config"]
]
}

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