Root object was present, but now absent. on mysql_grant

[williamohara]

Provider version

Run terraform -v

Terraform v1.9.4
on linux_amd64
+ provider registry.terraform.io/hashicorp/azuread v2.33.0
+ provider registry.terraform.io/hashicorp/azurerm v3.48.0
+ provider registry.terraform.io/hashicorp/helm v2.14.0
+ provider registry.terraform.io/hashicorp/kubernetes v2.18.1
+ provider registry.terraform.io/hashicorp/time v0.9.1
+ provider registry.terraform.io/petoju/mysql v3.0.63

You can find the latest version mentioned here: https://registry.terraform.io/providers/petoju/mysql/latest

MySQL version and settings

Azure Flexible MySQL configured in terraform as below. (Notice the SQL mode configs)

resource "azurerm_mysql_flexible_server" "subscripify_mysql_serv" {
  name                         = var.db_server_name
  administrator_login          = data.azurerm_key_vault_secret.dbUnSecret.value
  administrator_password       = data.azurerm_key_vault_secret.dbPwSecret.value
  resource_group_name          = azurerm_resource_group.repo_rg.name
  location                     = azurerm_resource_group.repo_rg.location
  backup_retention_days        = 7
  geo_redundant_backup_enabled = false
  sku_name = "B_Standard_B1ms"
  
  version = "8.0.21"
  zone    = "1"

  storage {
    auto_grow_enabled = true
    io_scaling_enabled = true
    size_gb           = 20
  }
  timeouts {}
}


resource "azurerm_mysql_flexible_server_configuration" "sql_mode" {
  name                = "sql_mode"
  resource_group_name = azurerm_resource_group.repo_rg.name
  server_name         = azurerm_mysql_flexible_server.subscripify_mysql_serv.name
  value               = "ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES"

}

Terraform Configuration Files

See above for server config- all one needs to do is change the variable names and set to their azure account.

terraform {
  required_providers {
    azurerm = {
      source = "hashicorp/azurerm"
      version = "3.48.0"
    }
    
    time = {
      source = "hashicorp/time"
      version = "0.9.1"
    }

    mysql = {
      source = "petoju/mysql"
      version = "3.0.63"
    }

    kubernetes = {
      source = "hashicorp/kubernetes"
      version = "2.18.1"
    }

    azuread = {
      source = "hashicorp/azuread"
      version = "2.33.0"
    }
  }
  required_version = ">= 0.14.9"
  backend "azurerm" {
  }
}

#connects to the Database server to run user setup
provider "mysql" {
  endpoint = "${data.azurerm_mysql_flexible_server.subscripify_mysql_serv.fqdn}:3306"
  # username = "${data.azurerm_mysql_flexible_server.subscripify_mysql_serv.administrator_login}@${data.azurerm_mysql_flexible_server.subscripify_mysql_serv.name}"
  username = "${data.azurerm_key_vault_secret.db_admin_username.value}"
  password = "${data.azurerm_key_vault_secret.db_admin_password.value}"
  tls      = true
}

provider "azuread" {
  # Configuration options
}
provider "azurerm" {
  features {}
}

provider "time" {

}
//sets up tenant user in db
resource "mysql_user" "tennat_db_user" {
  user               = replace("${var.tenant_name}_db_user","-","_")
  host               = "%"
  plaintext_password = var.SUBSCRIPIFY_DB_FIRST_TENANT_PW
}
resource "azurerm_mysql_flexible_database" "kratosdb" {
  name           = replace("${var.tenant_short_name}_user_db","-","_")
  resource_group_name = var.core_infra_resource_group_name
  server_name = data.azurerm_mysql_flexible_server.subscripify_mysql_serv.name
  charset = "latin1"
  collation = "latin1_swedish_ci"
}

resource "mysql_grant" "developer" {
  grant = true
  user     = mysql_user.tennat_db_user.user
  host     = mysql_user.tennat_db_user.host
  database = "${var.tenant_short_name}\\_%"
  privileges    = ["ALL"]

}

resource "mysql_grant" "create_user_t0_lord" {
  grant = true
  user     = mysql_user.tennat_db_user.user
  host     = mysql_user.tennat_db_user.host
  database = "*"
  privileges    = ["CREATE USER"]

}

Debug Output

creds cleaned...
https://gist.github.com/williamohara/924b7c3a58f809beeaf790b9e6f5d0b2

Panic Output

None

Expected Behavior

No error - grants given to user

Actual Behavior

Error: Provider produced inconsistent result after apply
│ 
│ When applying changes to mysql_grant.developer, provider
│ "provider[\"registry.terraform.io/petoju/mysql\"]" produced an unexpected
│ new value: Root object was present, but now absent.
│ 
│ This is a bug in the provider, which should be reported in the provider's
│ own issue tracker.

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

Important Factoids

Are there anything atypical about your accounts that we should know? For example: Running in EC2 Classic? Custom version of OpenStack? Tight ACLs?
I am using terragurnt

References

I don't think so

[petoju]

It seems here that the table for which the grant is created contains uppercase and lowercase - so the provider doesn't match it to the grant returned by mysql, that is normalized just to lowercase.

Workaround for now: use just lowercase DB names.
But this certainly needs to be fixed.

[williamohara]

That was it! 😮‍💨 - I didn't even realize. The database name variable is a variable used in several non-database components in my system too - looks like if I want consistency across my system I need to make sure that it is always lowercase. No big deal - its still early.

I read up on this https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

it seems like a straightforward check to see the value of lower_case_table_names using a SHOW VARIABLES LIKE 'lower_case_table_names'; statement. then just make the provider behave similarly

If lower_case_table_names = 0 then do not convert to lower case when loading the database name or table name for any resource requiring database name or table name, basically do nothing

if lower_case_table_names = 1 then convert to lowercase when loading the database name for any resource requiring the database name or table name

If lower_case_table_names = 2 then convert to lowercase only when loading resource names for lookup. For instance - when doing a check to see if the database was created with MyDatabase - the name of the object created will be MyDatabase" but when looked up in the INFORMATION_SCHEMA (where I don't think the provider is doing anywhere) convert to lowercase.

Logic I think would be straitforward but to test it one would need to have three db's one with each condition.

or - just always convert everything to lowercase no matter what.

What do you think the best approach is @petoju ? - if its an easier approach i can attempt to get a PR together...

[petoju]

@williamohara
We have to obey the variable lower_case_table_names. That's because one can have more databases differentiated only by case like DB1, Db1 and db1.

So we could initialize it once and save it to something like OneConnection. And then just check it from there.

[jessebye]

I've just encountered this same error when attempting to create a grant like this:

  + resource "mysql_grant" "roles_grants_roles" {
      + database   = "my_db"
      + grant      = false
      + host       = "localhost"
      + id         = (known after apply)
      + role       = "readonly_medium_role"
      + roles      = [
          + "readonly_low_role",
        ]
      + table      = "*"
      + tls_option = "NONE"
    }

I wonder if it's because the host here defaults to localhost (I'm not setting it in my resource block), but the MySQL grant defaults to %?

Update: confirmed, MySQL returns the host as %, which doesn't match what this provider expects (it expects empty string). Debug output:

2025-01-09T16:22:47.764-0800 [INFO]  provider.terraform-provider-mysql_v3.0.68: 2025/01/09 16:22:47 [DEBUG] Skipping grant &mysql.RoleGrant{Roles:[]string{"readonly_low_role"}, Grant:false, UserOrRole:mysql.UserOrRole{Name:"readonly_medium_role", Host:"%"}, TLSOption:"NONE"} as it doesn't match &mysql.RoleGrant{Roles:[]string{"readonly_low_role"}, Grant:false, UserOrRole:mysql.UserOrRole{Name:"readonly_medium_role", Host:""}, TLSOption:"NONE"}: timestamp=2025-01-09T16:22:47.764-0800
2025-01-09T16:22:47.764-0800 [INFO]  provider.terraform-provider-mysql_v3.0.68: 2025/01/09 16:22:47 [WARN] GRANT not found for mysql.UserOrRole{Name:"readonly_medium_role", Host:""} - removing from state: timestamp=2025-01-09T16:22:47.764-0800