google_sql_database_instance "collation" option is not supported on mysql (and postgres) instances

[suckowbiz]

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 -v
Terraform v1.0.5
on linux_amd64

Affected Resource(s)

• google_sql_database_instance

Terraform Configuration Files

resource "google_sql_database_instance" "smy53928ef" {
  name                = "smy53928ef"
  database_version    = "MYSQL_8_0"
  deletion_protection = true
  region              = "europe-west3"
  settings {
    pricing_plan = "PER_USE"
    tier         = "db-g1-small"
    disk_type    = "PD_HDD"
    collation    = "utf8_general_ci"
    ip_configuration {

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

      location = "eu"
    }
  }
}

Expected Behavior

The expected behavior is that the collation setting is accepted and processed.

Actual Behavior

Terraform return the error Error 400: Invalid request: Unsupported setting key: collation.:

module.generator.google_sql_database_instance.smy53928ef: Creating...
╷
│ Error: Error, failed to create instance smy53928ef: googleapi: Error 400: Invalid request: Unsupported setting key: collation., invalid
│ 
│   with module.generator.google_sql_database_instance.smy53928ef,
│   on ../../modules/smy01-add/main.tf line 2, in resource "google_sql_database_instance" "smy53928ef":
│    2: resource "google_sql_database_instance" "smy53928ef" {
│ 
╵
time=2021-08-31T07:28:32Z level=error msg=1 error occurred:
	* exit status 1

Steps to Reproduce

1. terraform apply

Important Factoids

The collation attribute was only correctly accepted when configuring a sql server instance. Both, mysql and postgres failed to accept the collation setting.

I tested with provider version: v3.82.0:

terraform {
  required_version = ">= 1.0.1, < 2.0.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "3.82.0"
    }
    google-beta = {
      source  = "hashicorp/google-beta"
      version = "3.82.0"
    }
    local = {
      source  = "hashicorp/local"
      version = "2.1.0"
    }
    null = {
      source  = "hashicorp/null"
      version = "3.1.0"
    }
    random = {
      source  = "hashicorp/random"
      version = "3.1.0"
    }
    external = {
      source  = "hashicorp/external"
      version = "2.1.0"
    }
  }
}

Debug log

https://gist.github.com/suckowbiz/0ff6d86b03c6f066a988f1cefe8edc38

References

The collation feature was published with terraform-google-provider v3.82.0 (https://github.com/hashicorp/terraform-provider-google/releases/tag/v3.82.0).

• #0000

Can you confirm this is intentionally working only on sql server?

[edwardmedia]

@suckowbiz It is in v3.82.0 which was released 18 hours ago. Can you checkout with that version?

[suckowbiz]

@suckowbiz It is in v3.82.0 which was released 18 hours ago. Can you checkout with that version?

Sorry, I introduced a typo. I tested with v3.82.0. (I updated my description above).

The issue occured, using v3.82.0!

[edwardmedia]

@suckowbiz can you post the full debug log?

[suckowbiz]

@suckowbiz can you post the full debug log?

I added a gist containing the debug log (https://gist.github.com/suckowbiz/0ff6d86b03c6f066a988f1cefe8edc38) to my initial posting.

[edwardmedia]

@suckowbiz can you try below config? I think collation only works for certain versions

resource "google_sql_database_instance" "smy53928ef" {
  name                = "issue9945-1"
  database_version    = "SQLSERVER_2017_STANDARD"
  deletion_protection = false
  region              = "europe-west3"
  root_password    = "mypassword"
  settings {
    tier = "db-custom-1-3840"
    collation    = "Polish_CI_AS"
  }
}

Here is the api doc that does specify this field but not detailed

[suckowbiz]

@suckowbiz can you try below config? I think collation only works for certain versions

I did that already. The collation option is working on sql server but neither working for mysql nor for postgres. Please see my Important Factoids above.

[suckowbiz]

I looked a little deeper into this and I think that it is intentional that settings.collation is working only for SQL Server due the nature of it.

To change collation of postgres and mysql there is a working option with google_sql_database module (and this option works only for mysql and postgres).

So in the end it makes sense but is not very intuitive and I think the documentation should be updated to inform about settings.collation is only available for sql server.

[edwardmedia]

@suckowbiz you are right, and agreed with you on the documentation. But the provider's is usually aligned with API's. Closing this issue then

[suckowbiz]

To avoid another user of the google provider opens the same issue again we could open an issue directly at the place where google api is maintained. Once they updated their documentation the provider docs can be aligned.

@edwardmedia Can you provide the repo where the Google APIs are developed. I am going to open an issue then.

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