Ansible: MySQL module fails to add privileges to user; throws errors.

[mverwijs]

Versions:

• Ansible: 1.3.2
• Python: 2.7
• OS: Debian 7 (ansible host), Centos6.4 (client), Debian Unstable (client)
• MySQL: mysql-5.5.30, amariadb-5.5.33a-MariaDB

Install method:

I installed Ansible the following way:

TARGET="/tmp/ansible01"
sudo apt-get install python-setuptools
sudo easy_install virtualenv
cd $TARGET && virtualenv ansible
source $TARGET/ansible/bin/activate
pip install ansible

Description:

Using this playbook (copy/paste from documentation) the mysql query on the client is able to actually create the user. But it fails to apply the privileges.

- name: Setting Galera User Password
  mysql_user: login_user={{ mysql_user }}  login_password={{ mysql_password }}  name={{ galera_user }} password={{ galera_password }} state=present priv=*.*:ALL

Error thrown:

 REMOTE_MODULE mysql_user login_user=root  login_password=curious  name=elvis password=spring state=present priv=*.*:ALL
fatal: [c6-4] => failed to parse: Traceback (most recent call last):
  File "/root/.ansible/tmp/ansible-1380442867.96-101659379289717/mysql_user", line 1348, in 
    main()
  File "/root/.ansible/tmp/ansible-1380442867.96-101659379289717/mysql_user", line 405, in main
    changed = user_mod(cursor, user, host, password, priv)
  File "/root/.ansible/tmp/ansible-1380442867.96-101659379289717/mysql_user", line 188, in user_mod
    privileges_revoke(cursor, user,host,db_table,grant_option)
  File "/root/.ansible/tmp/ansible-1380442867.96-101659379289717/mysql_user", line 263, in privileges_revoke
    cursor.execute(query)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")


FATAL: all hosts have already failed -- aborting

Note that the user actually does get created:

[root@c6-4 ~]# mysql mysql -e "select Host,User from user;" | grep elvis
| localhost        | elvis  |

[mpdehaan]

This seems to be a MySQL module authentication question (probably a need to set my.cnf, etc), please stop by the mailing list if you would like some help.

[mverwijs]

Lesson learned: Don't copy/paste from docs without thinking.
For future generations:

I used this task to change the password of mysql user 'root':

- name- name: Setting MySQL Root Password
  mysql_user: name=root password={{ mysql_password}} priv=*.*:ALL state=present

It appears that this clobbered the GRANT permissions of 'root', hence failing the next bit of granting privs to a newly created user....

[boscowitch]

Fuck, I the same happened to me . docs should be changed here ASAP.
Now I have to fix a lot of systems manually :(

Took me forever to figure out why some calls on existing users or removing users work fine but creating new ones is impossible with the same correct credentials....
this should be reopened until the docs are fixed and there should be a prevention mechanism in the mysql_user module or at least a warning !!!

this can potentionally break a lot of systems and finding or fixing it is cumbersome.

If someone would really want to remove grant through priv=*.*:ALL there should be another option to force this even for the root user (with a big warning in the docs that this might leave you without any users with Grant privileges) or it should fail if it finds no user with grant privileges left...

The manual fix is to restart mysql with the option:
--skip-grant-tables

And Execute the following commands in mysql command (NOTE: GRANT ... ALL ..does not work since that grant tables are skipped duh):

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;