mysql_grant and mysql_user have race condition between each other when used with for_each, no way to use depends_on to workaround it

[ghost]

This issue was originally opened by @jnerin as hashicorp/terraform#22545. It was migrated here as a result of the provider split. The original body of the issue is below.

---

Terraform Version

terraform -v
Terraform v0.12.6
+ provider.aws v2.23.0
+ provider.mysql v1.7.0
+ provider.random v2.2.0

Terraform Configuration Files

locals {
  users_grants = {
    "user1" = {
      "database"   = "db1"
      "privileges" = ["SELECT", "DELETE", "INSERT", "UPDATE"]
    }
    "user2" = {
      "database"   = "db2"
      "privileges" = ["SELECT", "DELETE", "INSERT", "UPDATE"]
    }
    "user3" = {
      "database"   = "db3"
      "privileges" = ["EXECUTE", "SELECT", "DELETE", "INSERT", "UPDATE"]
    }
  }
}

provider "mysql" {
  endpoint = aws_rds_cluster.db.endpoint
  username = "admin"
  password = aws_secretsmanager_secret_version.rds-password-version.secret_string
}

resource "mysql_user" "backend_users" {
  for_each = local.users_grants
 
  user               = each.key
  host               = "%"
  plaintext_password = "sample-password"
}
 
resource "mysql_grant" "backend_users_grants" {
  for_each = local.users_grants
 
  # depends_on = mysql_user.backend_users[each.key] # ERROR: Invalid expression A static list expression is required.
  user       = each.key
  host       = "%"
  database   = each.value["database"]
  privileges = each.value["privileges"]
}

Debug Output

Crash Output

Expected Behavior

Correct creation and deletion of users on mysql db

Actual Behavior

Error: Error 1396: Operation CREATE USER failed for 'user1'@'%'

The error happens because GRANT ran before CREATE USER and then CREATE USER failed because the user already exists. Due to me being using for_each I can't add a explicit depends_on for each GRANT on each CREATE_USER, and depending on what random order they run they randomly fail (or sometimes succeed).

Steps to Reproduce

To reproduce repeatedly create and destroy a list of several users, the longer the better, more chances of hitting the race condition, my setup was creating 15, and failed way more times than succeeded.

Additional Context

References