{"action":"opened","issue":{"url":"https://api.github.com/repos/ansible/ansible-modules-core/issues/1870","labels_url":"https://api.github.com/repos/ansible/ansible-modules-core/issues/1870/labels{/name}","comments_url":"https://api.github.com/repos/ansible/ansible-modules-core/issues/1870/comments","events_url":"https://api.github.com/repos/ansible/ansible-modules-core/issues/1870/events","html_url":"https://github.com/ansible/ansible-modules-core/issues/1870","id":99248331,"number":1870,"title":"Mysql_user Fails if Sql-Mode is ANSI","user":{"login":"bbatsche","id":500909,"avatar_url":"https://avatars.githubusercontent.com/u/500909?v=3","gravatar_id":"","url":"https://api.github.com/users/bbatsche","html_url":"https://github.com/bbatsche","followers_url":"https://api.github.com/users/bbatsche/followers","following_url":"https://api.github.com/users/bbatsche/following{/other_user}","gists_url":"https://api.github.com/users/bbatsche/gists{/gist_id}","starred_url":"https://api.github.com/users/bbatsche/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bbatsche/subscriptions","organizations_url":"https://api.github.com/users/bbatsche/orgs","repos_url":"https://api.github.com/users/bbatsche/repos","events_url":"https://api.github.com/users/bbatsche/events{/privacy}","received_events_url":"https://api.github.com/users/bbatsche/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-08-05T16:45:51Z","updated_at":"2015-08-05T16:45:51Z","closed_at":null,"body":"**Issue Type:** Bug Report\r\n\r\n**Ansible Version:**\r\n```\r\nansible 1.9.2\r\n  configured module search path = None\r\n```\r\n**Environment:**\r\n\r\n- Host: OS X 10.10.4\r\n- Guest: Ubuntu 14.04\r\n\r\n**Summary:**\r\n\r\nIf the `sql-mode` for MySQL includes `ANSI`, using `mysql_user` to assign privileges for a given database will work the first time. However, addition calls to `mysql_user` will fail with the error\r\n\r\n    1141, \"There is no such grant defined for user 'username' on host 'localhost'\"\r\n\r\n**Steps To Reproduce:**\r\n\r\n1. Add `sql-mode = ANSI` to `my.cnf` in the managed environment\r\n1. Run the following task at least twice\r\n\r\n    ```yml\r\n    - mysql_user:\r\n      name:     new_username\r\n      password: terrible_password\r\n      priv:     db_name.*:ALL\r\n      state:    present\r\n    ```\r\n\r\n**Expected Results:** First run through should be `changed`, subsequent runs should be `ok`.\r\n\r\n**Actual Results:** First run through is `changed`, subsequent runs `fail`.\r\n\r\n**Additional Notes:**\r\n\r\nMy hypothesis is that the `mysql_user` module is failing to correctly parse the output from `SHOW GRANTS` in this case. This is causing the check for whether grants need to be modified to receive a false positive. By default, `SHOW GRANTS` quotes table names with backticks (`` ` ``) like the following:\r\n\r\n    GRANT ALL PRIVILEGES ON `db_name`.* TO 'new_username'@'localhost'\r\n\r\nWhen `sql-mode` includes `ANSI`, database names are quoted with double quotes (`\"`):\r\n\r\n    GRANT ALL PRIVILEGES ON \"db_name\".* TO 'new_username'@'localhost'"}}