terraform-provider-mysql
terraform-provider-mysql copied to clipboard
hashicorp
User Grant on Existing User Causing Errors - Version 1.5.0
Terraform Version
0.10.8
Affected Resource(s)
- mysql_grant
Expected Behavior
-/+ module.mod_aurora.mysql_grant.mig_user_grants (new resource required)
id: "usermig@%:dev_user" => <computed> (forces new resource)
database: "dev_user" => "dev_user"
grant: "false" => "false"
host: "%" => "%"
privileges.#: "1" => "1"
privileges.2914988887: "ALL" => "ALL"
table: "" => "*" (forces new resource)
tls_option: "" => "NONE" (forces new resource)
user: "usermig" => "usermig"
-/+ module.mod_aurora.mysql_grant.svc_user_grants (new resource required)
id: "user@%:dev_user" => <computed> (forces new resource)
database: "dev_user" => "dev_user"
grant: "false" => "false"
host: "%" => "%"
privileges.#: "4" => "4"
privileges.1759376126: "UPDATE" => "UPDATE"
privileges.3138006342: "SELECT" => "SELECT"
privileges.3404380929: "DELETE" => "DELETE"
privileges.892623219: "INSERT" => "INSERT"
table: "" => "*" (forces new resource)
tls_option: "" => "NONE" (forces new resource)
user: "user" => "user"
-/+ module.mod_aurora.mysql_user.mig_user (new resource required)
id: "usermig@%" => <computed> (forces new resource)
host: "%" => "%"
plaintext_password: <sensitive> => <sensitive> (attribute changed)
tls_option: "" => "NONE" (forces new resource)
user: "usermig" => "usermig"
-/+ module.mod_aurora.mysql_user.svc_user (new resource required)
id: "user@%" => <computed> (forces new resource)
host: "%" => "%"
plaintext_password: <sensitive> => <sensitive> (attribute changed)
tls_option: "" => "NONE" (forces new resource)
user: "user" => "user"
Actual Behavior
mysql_grant.svc_user_grants: error revoking GRANT (REVOKE GRANT OPTION ON `dev_user`. FROM 'dev_user'@'%'): Error 1064: 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 'FROM 'dev_user'@'%'' at line 1
Reverting back to version 1.1.0 the problem disappears and only appears to impact existing users
@masonbivens sorry about the troubles. A couple of questions:
- Do you have some example HCL?
- Did you get a dirty plan immediately with the new provider or was this a result of a
taintor HCL change?
Also, could you run this plan with TF_LOG=DEBUG? That should show the full SQL query that's failing. I'm wondering whether Aurora, MySQL, or the SQL are the issue here.
We're not doing anything too crazy with it. The dirty plan came up immediately after we updated to the new provider. No HCL has changed.
resource "mysql_database" "main" {
name = "${var.svc_db_name}"
}
resource "mysql_user" "svc_user" {
user = "${var.svc_user_name}"
host = "${var.svc_user_host}"
plaintext_password = "${var.svc_user_password}"
}
resource "mysql_user" "mig_user" {
user = "${var.mig_user_name}"
host = "${var.mig_user_host}"
plaintext_password = "${var.mig_user_password}"
}
resource "mysql_grant" "svc_user_grants" {
user = "${var.svc_user_name}"
host = "${var.svc_user_host}"
database = "${var.svc_db_name}"
privileges = "${var.svc_user_privileges}"
depends_on = ["mysql_user.svc_user"]
}
resource "mysql_grant" "mig_user_grants" {
user = "${var.mig_user_name}"
host = "${var.mig_user_host}"
database = "${var.svc_db_name}"
privileges = "${var.mig_user_privileges}"
depends_on = ["mysql_user.mig_user"]
}
-----------------------------------------------------
[NORMAL] 2018-11-07T22:58:54.341Z [DEBUG] plugin.terraform-provider-mysql_v1.5.0_x4: 2018/11/07 22:58:54 [DEBUG] SQL: REVOKE GRANT OPTION ON `dev_db`. FROM 'dev_usermig'@'%'
[NORMAL] 2018-11-07T22:58:54.341Z [DEBUG] plugin.terraform-provider-mysql_v1.5.0_x4: 2018/11/07 22:58:54 [DEBUG] SQL: REVOKE GRANT OPTION ON `dev_db`. FROM 'dev_user'@'%'
[NORMAL] 2018-11-07T22:58:54.432Z [DEBUG] plugin.terraform-provider-aws_v1.43.0_x4: 2018/11/07 22:58:54 [DEBUG] [aws-sdk-go] DEBUG: Response ec2/DescribeAccountAttributes Details:
[NORMAL] 2018-11-07T22:58:54.432Z [DEBUG] plugin.terraform-provider-aws_v1.43.0_x4: ---[ RESPONSE ]
+1, we just hit this too. This is pretty problematic for us since this would cause a downtime for our app if the user/grants had to be recreated. I've checked around in terraform's source code and think what might be needed is to add an entry to the StateUpgraders of both the user & grant resources (see https://github.com/hashicorp/terraform/blob/master/helper/schema/resource.go). I'm not super well versed in the innards of terraform, but I feel like this might be the issue since the defaults for those values changed (appeared, I believe, in this case) and they are marked as needing a new resource on change.
@yrivardmulrooney I think you're right. Either myself or @davidji99 will take a look at this soon.
I hit this error as well, but with no existing users - after creating a new user, I can't do a grant:
data "aws_db_instance" "database" {
db_instance_identifier = "${var.db_host}"
}
provider "mysql" {
endpoint = "${data.aws_db_instance.database.endpoint}"
password = "${var.rds_password}"
username = "${var.rds_username}"
}
resource "mysql_user" "db_user" {
auth_plugin = "AWSAuthenticationPlugin"
host = "${var.user_host}"
user = "${var.username}"
}
resource "mysql_grant" "db_grant" {
database = "${var.db_name}"
host = "${var.user_host}"
privileges = ["${var.privileges}"]
user = "${var.username}"
}
Yields:
module.dlq.module.lambda_db_user.mysql_user.db_user: Creating...
auth_plugin: "" => "AWSAuthenticationPlugin"
host: "" => "10.%"
tls_option: "" => "NONE"
user: "" => "dlq-lambda-dev"
2018-11-15T00:45:17.281Z [DEBUG] plugin.terraform-provider-mysql_v1.5.0_x4: 2018/11/15 00:45:17 [DEBUG] Waiting for state to become: [success]
2018-11-15T00:45:17.497Z [DEBUG] plugin.terraform-provider-mysql_v1.5.0_x4: 2018/11/15 00:45:17 Executing statement: CREATE USER 'dlq-lambda-dev'@'10.%' IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS'
Error: Error applying plan:
1 error(s) occurred:
* module.dlq.module.lambda_db_user.mysql_user.db_user: 1 error(s) occurred:
* mysql_user.db_user: Error 1396: Operation CREATE USER failed for 'dlq-lambda-dev'@'10.%'
Terraform does not automatically rollback in the face of errors.
Instead, your Terraform state file has been partially updated with
any resources that successfully completed. Please address the error
above and apply again to incrementally change your infrastructure.
2018/11/15 00:45:17 [DEBUG] plugin: waiting for all plugin processes to complete...
2018-11-15T00:45:17.554Z [DEBUG] plugin.terraform-provider-mysql_v1.5.0_x4: 2018/11/15 00:45:17 [ERR] plugin: plugin server: accept unix /tmp/plugin386660098: use of closed network connection
2018-11-15T00:45:17.554Z [DEBUG] plugin: plugin process exited: path=/var/cache/terraform/86a64663-d420-48f3-89c9-ebcef6f4bec1/.terraform/plugins/linux_amd64/terraform-provider-mysql_v1.5.0_x4
2018-11-15T00:45:17.559Z [DEBUG] plugin: plugin process exited: path=/var/cache/terraform/86a64663-d420-48f3-89c9-ebcef6f4bec1/.terraform/plugins/linux_amd64/terraform-provider-dns_v2.0.0_x4
2018-11-15T00:45:17.559Z [DEBUG] plugin.terraform-provider-aws_v1.43.2_x4: 2018/11/15 00:45:17 [ERR] plugin: plugin server: accept unix /tmp/plugin570650370: use of closed network connection
Hello, this seems to still be occurring with version 1.5.1 of the provider where terraform still try to recreate my existing user :
Terraform will perform the following actions:
-/+ mysql_grant.grants (new resource required)
id: "user@%:*" => <computed> (forces new resource)
database: "*" => "*"
grant: "false" => "false"
host: "%" => "%"
privileges.#: "18" => "18"
privileges.1095555592: "LOCK TABLES" => "LOCK TABLES"
privileges.1182853270: "DROP" => "DROP"
privileges.1209859093: "CREATE VIEW" => "CREATE VIEW"
privileges.1420471602: "SHOW DATABASES" => "SHOW DATABASES"
privileges.1759376126: "UPDATE" => "UPDATE"
privileges.1999087025: "INDEX" => "INDEX"
privileges.2133731197: "CREATE" => "CREATE"
privileges.249615494: "CREATE ROUTINE" => "CREATE ROUTINE"
privileges.2801755275: "ALTER ROUTINE" => "ALTER ROUTINE"
privileges.296578197: "ALTER" => "ALTER"
privileges.3074425287: "CREATE TEMPORARY TABLES" => "CREATE TEMPORARY TABLES"
privileges.3109611148: "PROCESS" => "PROCESS"
privileges.3138006342: "SELECT" => "SELECT"
privileges.3404380929: "DELETE" => "DELETE"
privileges.3438987287: "EVENT" => "EVENT"
privileges.3700112312: "SHOW VIEW" => "SHOW VIEW"
privileges.623833415: "TRIGGER" => "TRIGGER"
privileges.892623219: "INSERT" => "INSERT"
table: "" => "*" (forces new resource)
tls_option: "" => "NONE" (forces new resource)
user: "user" => "user"
-/+ mysql_user.user (new resource required)
id: "user@%" => <computed> (forces new resource)
host: "%" => "%"
plaintext_password: *************************
tls_option: "" => "NONE" (forces new resource)
user: "user" => "user"
Plan: 2 to add, 0 to change, 2 to destroy.
Any plan to look into what was proposed by @yrivardmulrooney or any other fixes ?
Thanks!
Hi! I found how to deal with it. Please see, upgraded from 1.1.0 to 1.5.1 caused new resource: Terraform HCL code:
resource "mysql_user" "cron_20181214" {
user = "cron_20181214"
host = "%"
plaintext_password = "${data.vault_generic_secret.cron-20181214-user-password.data["value"]}"
}
resource "mysql_grant" "grant_cron_20181214_elite" {
user = "cron_20181214"
host = "%"
database = "elite"
privileges = ["ALL"]
depends_on = ["mysql_user.cron_20181214"]
}
-/+ module.rds-db-users-elite.mysql_grant.grant_cron_20181214_elite (new resource required)
id: "cron_20181214@%:elite" => <computed> (forces new resource)
database: "elite" => "elite"
grant: "false" => "false"
host: "%" => "%"
privileges.#: "1" => "1"
privileges.2914988887: "ALL" => "ALL"
table: "" => "*" (forces new resource)
tls_option: "" => "NONE" (forces new resource)
user: "cron_20181214" => "cron_20181214"
-/+ module.rds-db-users-elite.mysql_user.cron_20181214 (new resource required)
id: "cron_20181214@%" => <computed> (forces new resource)
host: "%" => "%"
plaintext_password: <sensitive> => <sensitive> (attribute changed)
tls_option: "" => "NONE" (forces new resource)
user: "cron_20181214" => "cron_20181214"
Solution:
resource "mysql_user" "cron_20181214" {
user = "cron_20181214"
host = "%"
plaintext_password = "${data.vault_generic_secret.cron-20181214-user-password.data["value"]}"
tls_option = ""
}
resource "mysql_grant" "grant_cron_20181214_elite" {
user = "cron_20181214"
host = "%"
database = "elite"
privileges = ["ALL"]
tls_option = ""
table = ""
depends_on = ["mysql_user.cron_20181214"]
}
So we need to redundantly specify empty string for values which forcing changes:
tls_option = ""
table = ""
Good luck!
Hi,
I had this same issue when destroying a mysql database user.
The solution was to use lifecyle statement in the mysql_grant resource. Like this:
resource "mysql_grant" "migration" { user = "migration" host = "%" database = local.app_db_name privileges = ["CREATE", "DROP", "DELETE", "INSERT", "REFERENCES", "SELECT", "UPDATE", "ALTER"]
depends_on = [aws_rds_cluster_instance.cluster_instances, mysql_user.app_user]
lifecycle {
create_before_destroy = true
} }
I had to destroy everything manually than recreate with this lifecycle statement (create_before_destroy)
After that, I was able to destroy without any errors. Hope this helps.
Regards
