Adding multiple users results in Error 1396 (Operation CREATE USER failed for 'someuser'@'%')

[jabouchleih]

Hi there,

Thank you for opening an issue. Please note that we try to keep the Terraform issue tracker reserved for bug reports and feature requests. For general usage questions, please see: https://www.terraform.io/community.html.

Terraform Version

Run terraform -v to show the version. If you are not running the latest version of Terraform, please upgrade because your issue may have already been fixed.

Terraform v0.11.10

Affected Resource(s)

Please list the resources as a list, for example:

• mysql_user
• mysql_grant
• mysql_database

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

provider "mysql" {
  alias    = "local"
  endpoint = "localhost:3306"
  username = "root"
  password = "root"
}

resource "mysql_database" "application_database" {
  provider = "mysql.local"
  count    = "${length(var.service_names)}"
  name     = "${element(var.service_names, count.index)}"
}

resource "mysql_user" "application_database_user" {
  provider           = "mysql.local"
  count              = "${length(var.db_users)}"
  user               = "${lookup(var.db_users[count.index], "username")}"
  plaintext_password = "${lookup(var.db_users[count.index], "password")}"
  host               = "${lookup(var.db_users[count.index], "host")}"
}

resource "mysql_grant" "developer_privilege_mapping" {
  provider   = "mysql.local"
  count      = "${length(var.account_schema_mapping)}"
  database   = "${lookup(var.account_schema_mapping[count.index], "schema")}"
  user       = "${lookup(var.account_schema_mapping[count.index], "username")}"
  host       = "${lookup(var.account_schema_mapping[count.index], "host")}"
  privileges = ["SELECT"]
}

VARIABLES

variable "service_names" {
  description = "Configured service names that are used to create corresponding schemas"
  default = ["somedb", "anotherdb"]
}

variable "db_users" {
  description = "Username and Password for accessing the specific database"

  default = [
    {
      "username" = "someaccount"
      "password" = "myPW12341234123"
      "host"     = "%"
    },
    {
      "username" = "anotheraccount"
      "password" = "myPW12341234123"
      "host"     = "%"
    },
    {
      "username" = "yetanotheraccount"
      "password" = "myPW12341234123"
      "host"     = "%"
    },
  ]
}

variable "account_schema_mapping" {
  description = "Mapping of service privileges to specified users and schemas"

  default = [
    {
      "username" = "someaccount"
      "host"     = "%"
      "schema"   = "somedb"
    },
    {
      "username" = "anotheraccount"
      "host"     = "%"
      "schema"   = "anotherdb"
    },
  ]
}

Debug Output

* mysql_user.application_database_user.0: Error 1396: Operation CREATE USER failed for 'myuser'@'%'
2018/12/04 16:19:47 [ERROR] root.database: eval: *terraform.EvalSequence, err: 1 error(s) occurred:

* mysql_user.application_database_user.0: Error 1396: Operation CREATE USER failed for 'myuser'@'%'
2018/12/04 16:19:47 [TRACE] [walkApply] Exiting eval tree: module.database.mysql_user.application_database_user[0]
2018/12/04 16:19:47 [TRACE] dag/walk: upstream errored, not walking "module.database.provider.mysql.tunnel (close)"
2018/12/04 16:19:47 [TRACE] dag/walk: upstream errored, not walking "meta.count-boundary (count boundary fixup)"
2018/12/04 16:19:47 [TRACE] dag/walk: upstream errored, not walking "root"

Expected Behavior

What should have happened?

Users are created properly.

According to this StackOverflow entry, this is due to not FLUSH PRIVILEGES between each CREATE USER command.

Actual Behavior

What actually happened?

The CREATE USER command fails with Error 1396.
A user is created, but the PW field is empty.

The script aborts with:

Error: Error applying plan:

1 error(s) occurred:

• module.database.mysql_user.application_database_user[0]: 1 error(s) occurred:

• mysql_user.application_database_user.0: Error 1396: Operation CREATE USER failed for 'someuser'@'%'

Steps to Reproduce

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

1. terraform apply

Important Factoids

Happening on Amazon RDS with an SSH Tunnel, Amazon RDS directly and a local MySQL Instance (v <=5.7.2x)

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:

• StackOverflow entry

[ghost]

@jabouchleih Try to create dependency between grants and user creation resource. In my, quite similar case, I faced same issue as you and after adding dependency I am able to create multiple user accounts.