MySQL Terraform Provider removes backticks from column names in privileges, causing SQL syntax errors with reserved words

[maximmasiutin]

Summary

When specifying column-level privileges in the Terraform MySQL provider (e.g., SELECT(id, net, ...)), the provider strips backticks from column names. This leads to SQL syntax errors when column names are reserved words (such as id, key, etc.), since MySQL requires these to be quoted with backticks. Even if backticks are included in the Terraform configuration, the provider removes them, resulting in invalid SQL statements and failed applies. The provider should preserve backticks in column names to ensure valid SQL is generated.

More information

I have the following grant in my database:

GRANT SELECT (id, net, selling, currency, key, status, reference, serviceType, created_at, updated_at) ON mydatabase.mytable TO myrole;

This is a valid command accepted by the database (MySQL 8.4).

When I run terraform plan, I get the following message:

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # mysql_grant.backoffice_role_basicuser_grant_privileges_to_role["61"] will be created
  + resource "mysql_grant" "backoffice_role_basicuser_grant_privileges_to_role" {
      + database   = (sensitive value)
      + grant      = false
      + host       = "localhost"
      + id         = (known after apply)
      + privileges = [
          + "SELECT(`created_at`, `currency`, `id`, `key`, `net`, `reference`, `selling`, `status`, `updated_at`)",
        ]
      + role       = "myrole"
      + table      = "mytable"
      + tls_option = "NONE"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Then I get an error:

Error: Error running SQL (GRANT SELECT(CREATED_AT, CURRENCY, FLIGHT_REQUEST_ID, ID, KEY, NET, REFERENCE, SELLING, SERVICETYPE, STATUS, UPDATED_AT) ON mydatabase.mytableTO 'myrole'): Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'KEY, NET, REFERENCE, SELLING, STATUS, UPDATED_AT) ONmydatabase.' at line 1`

As you see, the provider removes the backticks making the SQL statement invalid because of unquoted ID. If I use " quote instead of backticks, the error is the same.

Pull request #220 illustrates the issue - the change is in the test only.

Pull request #222 fixes the issue - the change is in the test and in the code.