Mysql Sql User cannot be updated or deleted if host is blank

[chrisst]

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

all

Affected Resource(s)

• google_sql_user

Terraform Configuration Files

func TestAccSqlUser_mysqlNoHost(t *testing.T) {
	t.Parallel()

	instance := acctest.RandomWithPrefix("i")
	resource.Test(t, resource.TestCase{
		PreCheck:     func() { testAccPreCheck(t) },
		Providers:    testAccProviders,
		CheckDestroy: testAccSqlUserDestroy,
		Steps: []resource.TestStep{
			{
				Config: testGoogleSqlUser_mysqlNoHost(instance, "password"),
				Check: resource.ComposeTestCheckFunc(
					testAccCheckGoogleSqlUserExists("google_sql_user.user2"),
				),
			},
			{
				Config: testGoogleSqlUser_mysqlNoHost(instance, "password2"),
				Check: resource.ComposeTestCheckFunc(
					testAccCheckGoogleSqlUserExists("google_sql_user.user2"),
				),
			},
			{
				ResourceName:            "google_sql_user.user2",
				ImportStateId:           fmt.Sprintf("%s/%s/localhost/admin", getTestProjectFromEnv(), instance),
				ImportState:             true,
				ImportStateVerify:       true,
				ImportStateVerifyIgnore: []string{"password"},
			},
		},
	})
}

func testGoogleSqlUser_mysqlNoHost(instance, password string) string {
	return fmt.Sprintf(`
	resource "google_sql_database_instance" "instance" {
		name = "%s"
		region = "us-central"
		settings {
			tier = "D0"
		}
	}

	resource "google_sql_user" "user2" {
		name = "admin"
		instance = "${google_sql_database_instance.instance.name}"
		password = "%s"
	}
	`, instance, password)
}

Problem

The host attribute of SqlUser for MySql is Optional and the empty string is the default if not passed to the rest API. However once a User is created with an empty host it can no longer be updated or deleted. The request will fail with "Error 400: Invalid request: Host required for MySQL instances."

This is because the upstream API requires host in the url as a param, but doesn't recognize ?host= as a valid option.

References

b/134505616

[justindujardin]

@chrisst Your problem statement says that update and delete will fail, but your referenced code change only sets the default host for Users.Update. Is this an oversight in the PR? I see this 400 error while deleting a SQL user:

...
Error: Error applying plan:
1 error(s) occurred:
* google_sql_user.project-db-user (destroy): 1 error(s) occurred:
* google_sql_user.project-db-user: Error, failed to deleteuser cool-user in instance terraform-1337:
  googleapi: Error 400: Missing parameter: host., required

[chrisst]

The referenced PR wasn't meant to fix this issue. This issue is a problem with the API since there it allows creating a user that can not be modified. We can't fix this issue until the API addresses this.

In contrast the referenced PR was fixing the Update call to pass the host as a param to make normal updates succeed with a valid host present.

If you have a config where you have a valid host and you can't delete the sql use can you share your config?

[justindujardin]

I see, thanks for the info.

If you have a config where you have a valid host and you can't delete the sql use can you share your config?

I'll look deeper into my issue and follow up here with a reproduction case if I can't resolve it.

[morgante]

Would it be possible to work around this in the provider by providing an option to force delete?

Specifically, when trying to delete an entire database it doesn't actually matter if the individual users are deleted but Terraform will error out on that step.

We're encountering this issue here: forseti-security/terraform-google-forseti#148

[chrisst]

It's my understanding from the API team that the user shouldn't be able to be created with no host. Once the user has been created in this state it appears to be impossible to modify/delete via the rest API. Ignoring the delete errors would result in an orphaned resource. I'll ping the API team and see if there's a plan for resolution because anything we do in the provider will be some degree of broken.

[chrisst]

Assigning this to 4.0 so that if there still isn't any update from the API team we can make a breaking change to avoid this for future users. It will likely require manual resolution of problematic resources if we address this in the provider.

[c2thorn]

This was resolved and verified in b/134758506

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