Intermittent destroy behavior with google_sql_database

[tpryan]

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 the 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 the issue is assigned to a user, that user is claiming responsibility for the issue. If the issue is assigned to "hashibot", a community member has claimed the issue already.

Description

Currently getting intermittent failures for destroying postgres databases created with google_sql_database, when used in a project with other resources. Sometimes it deletes, sometimes it doesn't with the error:

Error: Error when reading or editing Database: googleapi: Error 400: Invalid request: failed to delete database "todo". Detail: pq: database "todo" is being accessed by other users. (Please use psql client to delete database that is not owned by "cloudsqlsuperuser")., invalid

Database in this case is named 'todo'

I've done some exploration with using time_sleep, depenencies, and destroy_duration options to work around this. Sometimes they work, sometimes not.

Sometimes it deletes cleanly, some times it gives that error.

Rerunning destroy will remove.
Waiting some indeterminate amount of time between apply and destroy will also succeed.

I would prefer this just force deletion of this database, especially if the instance that the database exists on is also slated for destruction.

An alternative would be some sort of setting that would allow me to "abandon" this database like google_sql_user allows.

New or Affected Resource(s)

• google_sql_database

Potential Terraform Configuration

resource "google_sql_database" "database" {
  name     = "my-database"
  instance = google_sql_database_instance.instance.name
  deletion_policy = "ABANDON"
}

References

Currently a problem for this repo:
https://github.com/GoogleCloudPlatform/terraform-google-three-tier-app
Branch: sql-iam-auth
GoogleCloudPlatform/terraform-google-three-tier-web-app#13

Other reports:

• Can't destroy sql_user or sql_database #4299
• cloudsql instance deletion failure obsereved  #12400

[tpryan]

Looking into fixing with a PR.

It looks like this feature is implemented very simply in google_sql_user

In the definition of the resource:

"deletion_policy": {
				Type:     schema.TypeString,
				Optional: true,
				Description: `The deletion policy for the user. Setting ABANDON allows the resource
				to be abandoned rather than deleted. This is useful for Postgres, where users cannot be deleted from the API if they
				have been granted SQL roles. Possible values are: "ABANDON".`,
				ValidateFunc: validation.StringInSlice([]string{"ABANDON", ""}, false),
			},

And then in delete method:

if deletionPolicy := d.Get("deletion_policy"); deletionPolicy == "ABANDON" {
	// Allows for user to be abandoned without deletion to avoid deletion failing
	// for Postgres users in some circumstances due to existing SQL roles
	return nil
}

But this provider is created using Magic Modules, so the way to fix it there is not clear to me yet.

[tpryan]

Got a fix working. Submitting.

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