Google Cloud SQL Instance cannot be modified after deployment with collation

[menzbua]

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 # terraform -v
Terraform v1.1.4
on linux_amd64
+ provider registry.terraform.io/hashicorp/google v4.7.0
+ provider registry.terraform.io/hashicorp/google-beta v4.7.0

Your version of Terraform is out of date! The latest version
is 1.1.4. You can update by downloading from https://www.terraform.io/downloads.html

Affected Resource(s)

• google_sql_database_instance

Terraform Configuration Files

This is the basic configuration:

resource "google_sql_database_instance" "ssq0edad6n" {
  project             = "cloud-dns-test-326307"
  name                = "ssq0edad6n"
  database_version    = "SQLSERVER_2019_STANDARD"
  deletion_protection = false
  region              = "europe-west3"

  root_password = <sensitive data>
  settings {
    pricing_plan = "PER_USE"
    tier         = "db-custom-6-32768"
    disk_type    = "PD_SSD"
    collation    = "Latin1_General_CI_AS"
    ip_configuration {

      ipv4_enabled = true
    }
    location_preference {
      zone = "europe-west3-a"
    }
    backup_configuration {
      enabled = false

      location = "eu"
    }
  }
}

Would be changed in:

resource "google_sql_database_instance" "ssq0edad6n" {
  project             = "cloud-dns-test-326307"
  name                = "ssq0edad6n"
  database_version    = "SQLSERVER_2019_STANDARD"
  deletion_protection = false
  region              = "europe-west3"

  root_password = <sensitive data>
  settings {
    pricing_plan = "PER_USE"
    tier         = "db-custom-6-32768"
    disk_type    = "PD_SSD"
    collation    = "Latin1_General_CI_AS"
    ip_configuration {

      ipv4_enabled = true
      require_ssl = true
    }
    location_preference {
      zone = "europe-west3-a"
    }
    backup_configuration {
      enabled = false

      location = "eu"
    }
  }
}

Debug Output

https://gist.github.com/menzbua/f51f064ecfed684513f58491c528b41c

Panic Output

│ Error: Error, failed to update instance settings for : googleapi: Error 400: Invalid request: Unsupported setting key: collation., invalid
│
│ with google_sql_database_instance.ssq0edad6n,
│ on main.tf line 1, in resource "google_sql_database_instance" "ssq0edad6n":
│ 1: resource "google_sql_database_instance" "ssq0edad6n" {
│

Expected Behavior

Google Cloud SQL instance type SQL-Server can be modified when it is already deployed. This happens when you try to modify an instance with the parameter collation enabled.

Actual Behavior

Google Cloud SQL Instance type SQL-Server can not be modified when it is already deployed.

Steps to Reproduce

1. Deploy a Google Cloud SQL Instance of type SQL-Server with the parameter collation configured
2. terraform apply
3. Change some configuration on the SQL Instance e.g. add parameter require_ssl
4. terraform apply

[c2thorn]

The request Terraform sends to the API looks as far as I'm concerned. It seems like an API bug that collation would be an invalid key for the update request, especially considering it is not even the field updating.

I'll bubble this up to the API team. In the meantime, it seems like you will not be able to perform this update via terraform. You could perform it using another method like gcloud, and then reimport the resource.

[c2thorn]

for internal folks: b/216858127

[menzbua]

Hi @c2thorn,

Thanks for the response.
Do you have a response from the API team regarding the issue?

Thank you very much.
Regards,
Manuel

[suckowbiz]

Hi @c2thorn,

what is the outcome of your talk to the API team? We use this module in an enterprise environment and having the ability to change the collation is crucial to us.

[suckowbiz]

Hi @c2thorn ,

today I checked with terraform_provider_google version 4.15.0 if the API bug is gone. Unfortunately it is not.

When the collation option (with an unchanged value) is part of an update of SQL Server, then the Terraform deployment fails with:

╷
│ Error: Error, failed to update instance settings for : googleapi: Error 400: Invalid request: Unsupported setting key: collation., invalid
│ 
│   with module.generator.google_sql_database_instance.obfuscated,
│   on ../../modules/ssq02-edit/main.tf line 2, in resource "google_sql_database_instance" "obfuscated":
│    2: resource "google_sql_database_instance" "obfuscated" {
│ 
╵

Is there any update regarding this issue?

[c2thorn]

API team closed my request stating working as intended. The provider will need to be updated source code will have to be modified to not include the collation field when updating.

[suckowbiz]

API team ended up stating working as intended.

Could you please clarify. Does this mean that the API will accept the unchanged collation field in any update request without error. The user just has to update the Google Terraform Provider to the latest version to have this fix in place?

[c2thorn]

@suckowbiz Apologies, I edited the comment. The API will still not accept the unchanged collation field. The provider resource code will need a workaround.

[suckowbiz]

HI @c2thorn, thank you for reply. To be forced to remove the collation field from any further update requests is contrary to an enterprise automation approach to me. Sending the request that created the DB instance a second time would lead to the above describe API error.

From my point of view the following behavior would be desired:

• API ignores the collation option of an update request if it contains an unchanged value.
• API declines the update request in case the collation option contains a changed value

For our client this is a great drawback when implementing a automated CI/CD approach in an enterprise manner use of Google Cloud.

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