mysql_user should flush privileges when changed

[lordnynex]

Issue Type:

Bug Report

Ansible Version:

Any version using mysql_user

Ansible Configuration:

N/A

Environment:

N/A

Summary:

mysql_user should flush privileges when changed. Currently requires extra task to reload mysql or manually flush privileges.

Steps To Reproduce:

- mysql_user: name=root password=root priv=*.*:ALL state=present check_implicit_admin=yes

Expected Results:

Changed credentials should be usable in subsequent tasks without a mysql reload. Ex

- shell: mysqladmin -u root password root

Actual Results:

Tasks/commands attempting to use the modified credentials will fail execution.

[Jmainguy]

I think we should be able to add a command to flush privs at the end of the module, when change == true. Will take a stab at that tomorrow.

[Jmainguy]

@lordnynex Using the latest version of the module, I am not generating your results.

[root@phy01 ansible]# ansible-playbook -i hosts site.yml 

PLAY [XZ] **********************************************************************

TASK [mysql_user : mysql_user] *************************************************
changed: [centos6.soh.re]

PLAY RECAP *********************************************************************
centos6.soh.re             : ok=1    changed=1    unreachable=0    failed=0   

[root@phy01 ansible]# ansible-playbook -i hosts site.yml 

PLAY [XZ] **********************************************************************

TASK [mysql_user : mysql_user] *************************************************
fatal: [centos6.soh.re]: FAILED! => {"changed": false, "failed": true, "msg": "unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials. Exception message: (1045, \"Access denied for user '********'@'localhost' (using password: NO)\")"}

PLAY RECAP *********************************************************************
centos6.soh.re             : ok=0    changed=0    unreachable=0    failed=1   

[root@phy01 ansible]# cat roles/mysql_user/tasks/main.yml 
---

- mysql_user: name=root password=root priv=*.*:ALL state=present check_implicit_admin=yes

In my case on centos6, the changes did not require a flush. Are you still seeing this on latest devel? and if so, on what version of mysql and os?

[lordnynex]

@Jmainguy

This issue came up when I was helping someone on IRC so I have limited insight into the behavior of the current stable release. After I helped the user isolate the issue, @bcoca advised me to file this issue. IIRC this was ~1.9.2 stable at the time.

Your reproduction steps do indicate this bug may be invalid now. It is possible that the issue has been fixed in the latest dev branch.

The reproduction steps I provided where not necessarily accurate because the user had the 'mysql_user' tasks followed directly by some task that used those credentials. Is it possible the module does flush privileges but somehow defers it until after the playbook finishes?

Maybe something like

- mysql_user: name=root password=root priv=*.*:ALL state=present check_implicit_admin=yes
- shell: mysql -u root -p root -e "select(1);"

[Jmainguy]

[root@phy01 ansible]# cat roles/mysql_user/tasks/main.yml                                                                                                                                     
---

- name: Select as root with a password
  shell: mysql -u root --password=root -e "select(1);"
  ignore_errors: true

- name: select as root without a password
  shell: mysql -u root -e "select(1);"
  ignore_errors: true

- name: Change root password to root
  mysql_user: name=root password=root priv=*.*:ALL state=present check_implicit_admin=yes
  ignore_errors: true

- name: Select as root with a password
  shell: mysql -u root --password=root -e "select(1);"
[root@phy01 ansible]# ansible-playbook -i hosts site.yml                                                                                                                                      

PLAY [XZ] **********************************************************************

TASK [mysql_user : Select as root with a password] *****************************
fatal: [centos6.soh.re]: FAILED! => {"changed": true, "cmd": "mysql -u root --password=root -e \"select(1);\"", "delta": "0:00:00.012306", "end": "2016-01-07 20:38:10.565748", "failed": true, "rc": 1, "start": "2016-01-07 20:38:10.553442", "stderr": "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)", "stdout": "", "stdout_lines": [], "warnings": []}
...ignoring

TASK [mysql_user : select as root without a password] **************************
changed: [centos6.soh.re]

TASK [mysql_user : Change root password to root] *******************************
changed: [centos6.soh.re]

TASK [mysql_user : Select as root with a password] *****************************
changed: [centos6.soh.re]

PLAY RECAP *********************************************************************
centos6.soh.re             : ok=4    changed=3    unreachable=0    failed=0   

[root@phy01 ansible]# ansible-playbook -i hosts site.yml 

PLAY [XZ] **********************************************************************

TASK [mysql_user : Select as root with a password] *****************************
changed: [centos6.soh.re]

TASK [mysql_user : select as root without a password] **************************
fatal: [centos6.soh.re]: FAILED! => {"changed": true, "cmd": "mysql -u root -e \"select(1);\"", "delta": "0:00:00.005519", "end": "2016-01-07 20:38:13.607178", "failed": true, "rc": 1, "start": "2016-01-07 20:38:13.601659", "stderr": "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)", "stdout": "", "stdout_lines": [], "warnings": []}
...ignoring

TASK [mysql_user : Change root password to root] *******************************
fatal: [centos6.soh.re]: FAILED! => {"changed": false, "failed": true, "msg": "unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials. Exception message: (1045, \"Access denied for user '********'@'localhost' (using password: NO)\")"}
...ignoring

TASK [mysql_user : Select as root with a password] *****************************
changed: [centos6.soh.re]

PLAY RECAP *********************************************************************
centos6.soh.re             : ok=4    changed=2    unreachable=0    failed=0   

Works for me, but I say we leave this open, maybe someone else can reproduce this.

[ansibot]

@Jmainguy, ping. This issue is still waiting on your response.
click here for bot help

[Jmainguy]

Looks like nobody else was able to reproduce. Ok to close out?

[Jmainguy]

needs_contributor

[ansibot]

This repository has been locked. All new issues and pull requests should be filed in https://github.com/ansible/ansible

Please read through the repomerge page in the dev guide. The guide contains links to tools which automatically move your issue or pull request to the ansible/ansible repo.

[ansibot]

This issue was migrated to ansible/ansible#30022