Cloud SQL - Cannot recreate DB instance if it was deleted outside Terraform and users/db's were defined in terraform

[ray-pitmon-bv]

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 a user, that user is claiming responsibility for the issue.
• Customers working with a Google Technical Account Manager or Customer Engineer can ask them to reach out internally to expedite investigation and resolution of this issue.

Terraform Version & Provider Version(s)

Terraform v1.9.8
on linux_amd64

• provider registry.terraform.io/hashicorp/google v6.16.0
• provider registry.terraform.io/hashicorp/google-beta v6.16.0

Affected Resource(s)

google_sql_database_instance
google_sql_database
google_sql_user

Terraform Configuration

resource "google_sql_database_instance" "google_sql_database_instance" {
  name             = var.name
  project          = var.project-id
  region           = var.region
  database_version = "MYSQL_8_0"

  settings {
    tier = var.db-tier
    }
    availability_type     = var.db-availability-type
    disk_size             = var.sql-db-disk-size
    disk_autoresize       = true
    disk_autoresize_limit = var.db-disk-resize-limit
  }
  deletion_protection = "true"
  lifecycle {
    ignore_changes = [settings[0].disk_size]
  }
}

resource "google_sql_database" "dedicated_google_sql_database" {
  name       = var.name
  project    = var.project-id
  instance   = google_sql_database_instance.google_sql_database_instance.name
}

resource "google_sql_user" "dedicated-mysql-sql-user" {
  name     = var.name
  instance = google_sql_database_instance.google_sql_database_instance.name
  password = random_password.sql-pass.result
  project  = var.project-id
}

Debug Output

No response

Expected Behavior

After removing the cloud sql instance from GCP using the console, terraform should recreate the instance and the associated user & DB.

Actual Behavior

terraform plan fails because of an error retrieving info about the cloud sql instance that was deleted.
Output:

Step #2 - "tf plan": Error: googleapi: Error 404: The Cloud SQL instance does not exist., instanceDoesNotExist
Step #2 - "tf plan": 
Step #2 - "tf plan":   with module.dev.google_sql_database.dedicated_google_sql_database,
Step #2 - "tf plan":   on ../../module/snapapp/main.tf line 74, in resource "google_sql_database" "dedicated_google_sql_database":
Step #2 - "tf plan":   74: resource "google_sql_database" "dedicated_google_sql_database" {
Step #2 - "tf plan": 
Step #2 - "tf plan": 
Step #2 - "tf plan": Error: googleapi: Error 404: The Cloud SQL instance does not exist., instanceDoesNotExist
Step #2 - "tf plan": 
Step #2 - "tf plan":   with module.dev.google_sql_user.dedicated-mysql-sql-user,
Step #2 - "tf plan":   on ../../module/snapapp/main.tf line 80, in resource "google_sql_user" "dedicated-mysql-sql-user":
Step #2 - "tf plan":   80: resource "google_sql_user" "dedicated-mysql-sql-user" {
Step #2 - "tf plan": 
Finished Step #2 - "tf plan"

Steps to reproduce

1. Create a Cloud SQL instance with a user + database via terraform apply
2. Delete the Cloud SQL instance using the GCP cloud console or gcloud.
3. Run terraform plan

Important Factoids

The workaround is to remove the user and db via terraform state rm.

References

No response

[ggtisc]

Hi @ray-pitmon-bv

I have noticed that the shared code structure is not correct and also to replicate this issue we need more information. Please share the values of your variables and the correct structure you are using to make a new try. For sensitive information you could use examples like:

project = "my-project"
password = "1234567890"
user = "[email protected]"