Terraform-provider-google: Creating CloudSQL with same name as deleted CloudSQL still having issues in v3.13.0

[syedrakib]

References

I had previously commented on a pre-existing issue #5101. But the issue was closed saying it is resolved in Google provider version v3.3. Turns out it's not yet resolved in Google provider version v3.13.0

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 v0.12.24
+ provider.google v3.13.0
+ provider.google-beta v3.13.0

Affected Resource(s)

• google_sql_database_instance - link

Terraform Configuration Files

Official Google Terraform module for CloudSQL MySQL - link

Debug Output

module.my_infra.module.mysql_db.google_sql_database_instance.default: Still creating... [9m20s elapsed]
module.my_infra.module.mysql_db.google_sql_database_instance.default: Still creating... [9m30s elapsed]
module.my_infra.module.mysql_db.google_sql_database_instance.default: Still creating... [9m40s elapsed]
module.my_infra.module.mysql_db.google_sql_database_instance.default: Still creating... [9m50s elapsed]

Error: Error, failed to create instance mysql-rakib-dev: googleapi: Error 409: The instance or operation is not in an appropriate state to handle the request., invalidState

  on .terraform/modules/my_infra.mysql_db/terraform-google-sql-db-3.1.0/modules/mysql/main.tf line 27, in resource "google_sql_database_instance" "default":
  27: resource "google_sql_database_instance" "default" {

"Still creating..." for 10 minutes until it timed out.

Steps to Reproduce

• Create a CloudSQL instance with a specific name
• Delete the CloudSQL instance
• Try to recreate another CloudSQl instance with the same name.

Current Behavior

It kept trying for 10 minutes (until timeout) and then errored out with a failed to create instance error.

Expected Behavior

Terraform shouldn't hang like this until timeout (which was 10 minutes here). Instead, terraform could try for some time and then fail saying this is a probable cause and the developer should verify.

Reference

This GCloud doc says "You cannot reuse an instance name for up to a week after you have deleted an instance".

[edwardmedia]

Can we stop retrying if 409 and the message "You cannot reuse an instance name for up to a week after you have deleted an instance" are received?

[syedrakib]

Any development with this?

[megan07]

Hi @syedrakib! Sorry for the delayed response, we are looking into it. The problem is that the API responds with a 409 error, which means two different things for this endpoint, either "the sql instance is undergoing another change and when it's finished we will retry the request" _or_ "a sql instance with the same name has been deleted and the name cannot be reused for a week".
When the issue was first addressed in December, the API was returning a different message in it's response depending on which of those cases it was, however, there must have been a change recently that the response message is now The instance or operation is not in an appropriate state to handle the request. Thus, from our side, we can't distinguish a difference between those cases. We're waiting to hear back from the team at Google to figure out next steps.
Thanks!

[syedrakib]

Thanks for the update. Closely following this :)

[kirin-13]

• specific

Encountered the same problem as you. In the test, the instance name contains a random string, and an error is reported when updating the instance. The instance name does not contain a random string, and the instance configuration can be successfully updated.
I don't know if it will help you solve the problem.

[syedrakib]

hi @kirin-13 . I am not sure i understand you. What random string are you talking about?

Just to re-iterate, we are facing this issues when i have deleted a CloudSQL instance and then trying to create a new CloudSQL instance with the "same" name as the deleted instance's name.

[ryboe]

I worked around this problem by appending a random string to the name of the db on creation.

resource "google_sql_database_instance" "main_primary" {
  name = "main-primary-${random_string.four_chars.result}"
}

resource "random_string" "four_chars" {
  length  = 4
  upper   = false # instance names can only have lowercase letters, numbers, and hyphens
  special = false
}

[syedrakib]

hi @y0ssar1an . This workaround may not completely work.

If you delete the google_sql_database_instance.main_primary resource and then re-create that resource, you have a new db_instance resource.

However, the random_string.four_chars was never destroyed and re-created. So the four_chars resource will still return the same output as before and, thus, back to the same problem again.

Instead we can just replace the -${random_string.four_chars.result} entirely with anything like -woody... If we need to make it another time, replace it with -bingo. Silly work around but works :)

[syedrakib]

BTW, this GitHub issue is raised here so that the prodvider fails early and creates a meaningful error message if the CloudSQL name is the issue instead of having to wait forever as it is currently behaving now.

[ryboe]

I see the concern. It hasn't been an issue, though. I've got the random_string and google_sql_database_instance together in a module. They always get created and destroyed together.

[stepanstipl]

It would be interesting to know how _gcloud_ cli implements this, it returns different error messages depending whether the instance exists(ed) in the same zone or different one:

# same zone
$gcloud sql instances create test-01 --zone us-east1-b
ERROR: (gcloud.sql.instances.create) Resource in project [xxx-redacted] is the subject of a conflict: The Cloud SQL instance already exists. When you delete an instance, you cannot reuse the name of the deleted instance until one week from the deletion date.


# different zone
$gcloud sql instances create test-01 --zone europe-west4-a
ERROR: (gcloud.sql.instances.create) Resource in project [xxx-redacted] is the subject of a conflict: The instance or operation is not in an appropriate state to handle the request.

[syedrakib]

It would be interesting to know how _gcloud_ cli implements this, it returns different error messages depending whether the instance exists(ed) in the same zone or different one:

# same zone
$gcloud sql instances create test-01 --zone us-east1-b
ERROR: (gcloud.sql.instances.create) Resource in project [xxx-redacted] is the subject of a conflict: The Cloud SQL instance already exists. When you delete an instance, you cannot reuse the name of the deleted instance until one week from the deletion date.


# different zone
$gcloud sql instances create test-01 --zone europe-west4-a
ERROR: (gcloud.sql.instances.create) Resource in project [xxx-redacted] is the subject of a conflict: The instance or operation is not in an appropriate state to handle the request.

this could be something to consider for a start. Especially since @megan07 mentioned here that the error messages for 2 different cases are being the same right now.

[syedrakib]

Reviving this issue. Faced it again today after a long time 😭😭😭

The error being reported now is

Error: Error, failed to create instance my-cloudsql-db: googleapi:
Error 409: The instance or operation is not in an appropriate state to handle the request., invalidState

Terraform version: 0.12.28
Google provider version: 3.27.0

[vgomenyuk]

The same issue for v 3.51.0.
Error: Error, failed to create instance temp-cloud-sql-db-1-stg: googleapi: Error 409: The Cloud SQL instance already exists. When you delete an instance, you cannot reuse the name of the deleted instance until one week from the deletion date., instanceAlreadyExists