New Google Cloud SQL failover replica feature shouldn't try to delete root user

[hashibot]

This issue was originally opened by @ryan-mf as hashicorp/terraform#14947. It was migrated here as part of the provider split. The original body of the issue is below.

---

Hi there,

Terraform Version

0.9.6

Affected Resource(s)

• google_sql_database_instance

Terraform Configuration Files

// GCP Cloud SQL Primary DB in Primary Region, Primary Zone
resource "google_sql_database_instance" "db-primary" {
  provider           = "google.gcp-${var.region_primary}"
  region             = "${var.sqlregion_primary}"
  database_version   = "MYSQL_5_7"

  settings {
    tier             = "db-n1-standard-1"

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

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

    ip_configuration {
        require_ssl        = "true"
    }

    location_preference {
        zone               = "${var.region_primary_zone_primary}"
    }
  }
}

// GCP Cloud SQL Secondary DB in Primary Region, Secondary Zone
resource "google_sql_database_instance" "db-failover" {
  provider           = "google.gcp-${var.region_primary}"
  region             = "${var.sqlregion_primary}"
  database_version   = "MYSQL_5_7"
  master_instance_name = "${google_sql_database_instance.db-primary.name}"

  replica_configuration {
    connect_retry_interval = "30"
    failover_target        = "true"
  }

  settings {
    tier             = "db-n1-standard-1"

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

    ip_configuration {
        require_ssl        = "true"
    }

    location_preference {
        zone               = "${var.region_primary_zone_secondary}"
    }
  }
}

Expected Behavior

The resource should be created without issue.

Actual Behavior

The resource is created, but with error output in red:

Error applying plan:

1 error(s) occurred:

* google_sql_database_instance.db-failover: 1 error(s) occurred:

* google_sql_database_instance.db-failover: Error, failed to delete default 'root'@'*' user, but the database was created successfully: googleapi: Error 400: The requested operation is not valid for a read-replica instance., errorReadReplicaInvalidOperation

Terraform does not automatically rollback in the face of errors.
Instead, your Terraform state file has been partially updated with
any resources that successfully completed. Please address the error
above and apply again to incrementally change your infrastructure.

Steps to Reproduce

1. Create a database instance with resource type google_sql_database_instance
2. Create a failover replica instance with almost all of the same settings, but remove the backup_configuration section and add the replica_configuration section.
3. Watch error output.

Important Factoids

On page https://www.terraform.io/docs/providers/google/r/sql_database_instance.html, at the top in yellow it specifically says this resource type will delete the default 'root' user that Cloud SQL creates for you, and you need to define your own root user (which I do). However, this isn't a valid action for the failover replica.

[danawillow]

@ryan-mf this doesn't seem to be an issue anymore- it looks like cloud sql stopped creating a root user for the failover replica, so terraform has stopped trying to delete it. Closing now, but feel free to reopen if you notice this still occurring!

[roman-cnd]

@danawillow Still happening - if there had been a root user added previously to master instance.

* module.sql.google_sql_database_instance.failover: 1 error(s) occurred:

* google_sql_database_instance.failover: Error, failed to delete default 'root'@'*' user, but the database was created successfully: googleapi: Error 400: The requested operation is not valid for a read-replica instance., errorReadReplicaInvalidOperation

My setup is similar to mentioned in this ticket.

Thanks!

[ryan-mf]

I just ran into this again. So yes, still happening. Just upgraded to Terraform 0.10.2.

[ryan-mf]

Opened new, nearly identical ticket #347 because this is happening again.

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