`availability_type` should be supported on MYSQL `sql_database_instance`

[eyalzek]

https://www.terraform.io/docs/providers/google/r/sql_database_instance.html#availability_type

Regional HA is available for mysql instances (https://cloud.google.com/sql/docs/mysql/high-availability) but this attribute can only be set for postgres instance at the moment.

[danawillow]

Hey @eyalzek, thanks for filing! Next time could you please fill out the issue template? That helps our bot label bugs / enhancements.

For this specific issue, does Terraform fail if you try to set availability_type on MySQL? I'm curious whether we need to update our code at all or just our docs.

[eyalzek]

Hey @eyalzek, thanks for filing! Next time could you please fill out the issue template? That helps our bot label bugs / enhancements.

Apologies!

For this specific issue, does Terraform fail if you try to set availability_type on MySQL? I'm curious whether we need to update our code at all or just our docs.

It fails, I can run it tomorrow and get you the output. The thing is, it looks like it's not supported in any Google api at the moment, nor gcloud. It's a manual process that has to be done via the cloud console.

[eyalzek]

@danawillow the waiting-response label doesn't seem right to me :)

[danawillow]

It fails, I can run it tomorrow and get you the output.

I was waiting for that :)

[eyalzek]

@danawillow my bad! Here is trace level output for the failed request. Let me know if I can provide any additional info:

module.development_mysql_instance.google_sql_database_instance.database: Modifying... [id=development-mysql-master]
2020/02/03 10:52:48 [DEBUG] module.development_mysql_instance.google_sql_database_instance.database: applying the planned Update change
2020/02/03 10:52:48 [TRACE] GRPCProvider: ApplyResourceChange
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: 2020/02/03 10:52:48 [DEBUG] Waiting for state to become: [success]
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: 2020/02/03 10:52:48 [DEBUG] Google API Request Details:
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: ---[ REQUEST ]---------------------------------------
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: PUT /sql/v1beta4/projects/development/instances/development-mysql-master?alt=json&prettyPrint=false HTTP/1.1
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Host: www.googleapis.com
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: User-Agent: google-api-go-client/0.5 HashiCorp Terraform/0.12.19 (+https://www.terraform.io) Terraform Plugin SDK/1.4.0 terraform-provider-google/3.3.0
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Content-Length: 540
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Content-Type: application/json
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: X-Goog-Api-Client: gl-go/1.11.0 gdcl/20191007
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Accept-Encoding: gzip
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: 
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: {
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:  "settings": {
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "activationPolicy": "ALWAYS",
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "availabilityType": "REGIONAL",
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "backupConfiguration": {
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:    "enabled": true,
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:    "startTime": "00:00"
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   },
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "dataDiskSizeGb": "15",
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "dataDiskType": "PD_SSD",
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "ipConfiguration": {
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:    "ipv4Enabled": true,
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:    "privateNetwork": "projects/development/global/networks/default",
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:    "requireSsl": false
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   },
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "locationPreference": {
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:    "zone": "europe-west4-b"
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   },
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "maintenanceWindow": {
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:    "day": 1,
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:    "hour": 2,
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:    "updateTrack": "canary"
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   },
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "pricingPlan": "PER_USE",
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "replicationType": "SYNCHRONOUS",
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "settingsVersion": "66",
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "storageAutoResize": true,
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "tier": "db-g1-small"
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:  }
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: }
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: 
2020-02-03T10:52:48.083+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: -----------------------------------------------------
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: 2020/02/03 10:52:48 [DEBUG] Google API Response Details:
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: ---[ RESPONSE ]--------------------------------------
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: HTTP/2.0 400 Bad Request
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Alt-Svc: quic=":443"; ma=2592000; v="46,43",h3-Q050=":443"; ma=2592000,h3-Q049=":443"; ma=2592000,h3-Q048=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Cache-Control: private
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Content-Type: application/json; charset=UTF-8
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Date: Mon, 03 Feb 2020 09:52:48 GMT
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Server: ESF
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Vary: Origin
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Vary: X-Origin
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: Vary: Referer
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: X-Content-Type-Options: nosniff
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: X-Frame-Options: SAMEORIGIN
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: X-Xss-Protection: 0
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: 
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: {
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   "error": {
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:     "code": 400,
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:     "message": "The incoming request contained invalid data.",
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:     "errors": [
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:       {
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:         "message": "The incoming request contained invalid data.",
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:         "domain": "global",
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:         "reason": "invalidRequest"
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:       }
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:     ]
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5:   }
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: }
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: 
2020-02-03T10:52:48.956+0100 [DEBUG] plugin.terraform-provider-google_v3.3.0_x5: -----------------------------------------------------
2020/02/03 10:52:48 [DEBUG] module.development_mysql_instance.google_sql_database_instance.database: apply errored, but we're indicating that via the Error pointer rather than returning it: Error, failed to update instance settings for : googleapi: Error 400: The incoming request contained invalid data., invalidRequest
2020/02/03 10:52:48 [TRACE] module.development_mysql_instance: eval: *terraform.EvalMaybeTainted
2020/02/03 10:52:48 [TRACE] module.development_mysql_instance: eval: *terraform.EvalWriteState
2020/02/03 10:52:48 [TRACE] EvalWriteState: recording 1 dependencies for module.development_mysql_instance.google_sql_database_instance.database
2020/02/03 10:52:48 [TRACE] EvalWriteState: writing current state object for module.development_mysql_instance.google_sql_database_instance.database
2020/02/03 10:52:48 [TRACE] module.development_mysql_instance: eval: *terraform.EvalApplyProvisioners
2020/02/03 10:52:48 [TRACE] EvalApplyProvisioners: google_sql_database_instance.database is not freshly-created, so no provisioning is required
2020/02/03 10:52:48 [TRACE] module.development_mysql_instance: eval: *terraform.EvalMaybeTainted
2020/02/03 10:52:48 [TRACE] module.development_mysql_instance: eval: *terraform.EvalWriteState
2020/02/03 10:52:48 [TRACE] EvalWriteState: recording 1 dependencies for module.development_mysql_instance.google_sql_database_instance.database
2020/02/03 10:52:48 [TRACE] EvalWriteState: writing current state object for module.development_mysql_instance.google_sql_database_instance.database
2020/02/03 10:52:48 [TRACE] module.development_mysql_instance: eval: *terraform.EvalIf
2020/02/03 10:52:48 [TRACE] module.development_mysql_instance: eval: *terraform.EvalIf
2020/02/03 10:52:48 [TRACE] module.development_mysql_instance: eval: *terraform.EvalWriteDiff
2020/02/03 10:52:48 [TRACE] module.development_mysql_instance: eval: *terraform.EvalApplyPost
2020/02/03 10:52:48 [ERROR] module.development_mysql_instance: eval: *terraform.EvalApplyPost, err: Error, failed to update instance settings for : googleapi: Error 400: The incoming request contained invalid data., invalidRequest
2020/02/03 10:52:48 [ERROR] module.development_mysql_instance: eval: *terraform.EvalSequence, err: Error, failed to update instance settings for : googleapi: Error 400: The incoming request contained invalid data., invalidRequest
2020/02/03 10:52:48 [TRACE] [walkApply] Exiting eval tree: module.development_mysql_instance.google_sql_database_instance.database
2020/02/03 10:52:48 [TRACE] vertex "module.development_mysql_instance.google_sql_database_instance.database": visit complete
2020/02/03 10:52:48 [TRACE] dag/walk: upstream of "provider.google (close)" errored, so skipping
2020/02/03 10:52:48 [TRACE] dag/walk: upstream of "meta.count-boundary (EachMode fixup)" errored, so skipping
2020/02/03 10:52:48 [TRACE] dag/walk: upstream of "root" errored, so skipping

[danawillow]

Yeah, that's a GCP-side error. Since your other comment mentions that it can't be done via the API or gcloud, there isn't anything we can really do in Terraform to get around that. Marking as upstream since we can't start working on this until there's support in the API for it.

[CAFxX]

It is now not possible anymore to create the legacy HA configuration via the GCP console, so restoring a database from the console will now automatically yield a database with the new HA configuration.

Legacy MySQL high availability option

Until April 2020, you have the option of using the legacy process for adding high availability to MySQL instances, which uses a failover replica. The legacy functionality is not available in the Cloud Console. Instead, use gcloud or cURL commands.

This is problematic as it causes configuration drift since it is not yet possible to create the new HA configuration using this provider (as doing so requires availability_type).

[CAFxX]

@danawillow just one note about https://github.com/terraform-providers/terraform-provider-google/issues/5286#issuecomment-581544511.

I think the problem is mostly that enabling availability_type=REGIONAL also requires to enable binary logging (and possibly backups), and AFAICT this was not done during the attempt above.

See e.g. https://github.com/terraform-google-modules/terraform-google-sql-db/pull/99

[danawillow]

@CAFxX, can you clarify- are you saying that the provider should work fine as-is, and it's just up to the user to make sure they set the right things? Or are you saying there's still something we need to add to the provider, and if so, what is it?

[aluis-ac]

Hello everybody, I've been facing this issue and got it fixed as @CAFxX suggested.

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

}

Thank you!

[eyalzek]

Looks like availability_type = "REGIONAL" is already supported for google_sql_database_instance with database version MYSQL_5_7. I'm closing this issue.

[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 👉 hashibot-feedback@hashicorp.com. Thanks!