mysql_user module with nested loops

Archives Ansible Project
1

Hi

I am trying to add multiple users with mysql_user module:

  • name: Create local DB users and set permissions
    action: mysql_user login_user=root login_password=xxxxxxx name={{ item[0] }} password={{ item[1] }} priv=.:ALL host={{ item[2] }}
    with_nested:
  • [‘user1’, ‘user2’, ‘user3’]
  • [‘pass1’, ‘pass2’, ‘pass3’]
  • [‘localhost’, ‘localhost’, ‘%’]

Is this correct syntax? Because it does not work:

failed: [localhost] => (item=[‘user1’, ‘pass1’, ‘localhost’]) => {“failed”: true, “item”: [“user1”, “pass1”, “localhost”], “parsed”: false}
invalid output was: Traceback (most recent call last):
File “/root/.ansible/tmp/ansible-1379784647.74-7643490092735/mysql_user”, line 1348, in
main()
File “/root/.ansible/tmp/ansible-1379784647.74-7643490092735/mysql_user”, line 409, in main
changed = user_add(cursor, user, host, password, priv)
File “/root/.ansible/tmp/ansible-1379784647.74-7643490092735/mysql_user”, line 140, in user_add
cursor.execute(“CREATE USER %s@%s IDENTIFIED BY %s”, (user,host,password))
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: (1396, “Operation CREATE USER failed for ‘user1’@‘localhost’”)

Edgars

2

with_nested creates nested loops.

Sounds like you would rather do:

with_items:

  • { name: ‘asdf’, password: ‘password1’, host: ‘host’ }
  • { name: ‘jkl’, password: ‘password2’, host: ‘host2’ }

In which case, dereference values as {{ item.name }} and so on.

You can of course also assign this to a variable and then just do:

with_items: db_users

which keeps your playbooks cleaner.

3

Thanks, this way it works. Perhaps you should then also change documentation, because in docs example is with nested loops.

Edgars

sestdiena, 2013. gada 21. septembris 19:34:43 UTC+2, Edgars rakstīja:

4

Hi, Edgars.

Thanks, this way it works. Perhaps you should then also change
documentation, because in docs example is with nested loops.

I wrote the example on the documentation. Thanks for making me
double-check it, because it had an SQL-related bug and a missing
password parameter, errors that I just fixed:
https://github.com/ansible/ansible/pull/4203

  - name: give users access to multiple databases
    mysql_user: name={{ item[0] }} priv={{ item[1] }}.*:ALL password='foo'
    with_nested:
      - [ 'alice', 'bob', 'eve' ]
      - [ 'clientdb', 'employeedb', 'providerdb' ]

However, nothing was wrong in the use of with_nested. This is what my
run of the fixed example code does:

GATHERING FACTS ***************************************************************
ok: [testhost]

TASK: [give users access to multiple databases] *******************************
changed: [testhost] => (item=['alice', 'clientdb'])
changed: [testhost] => (item=['alice', 'employeedb'])
changed: [testhost] => (item=['alice', 'providerdb'])
changed: [testhost] => (item=['bob', 'clientdb'])
changed: [testhost] => (item=['bob', 'employeedb'])
changed: [testhost] => (item=['bob', 'providerdb'])
changed: [testhost] => (item=['eve', 'clientdb'])
changed: [testhost] => (item=['eve', 'employeedb'])
changed: [testhost] => (item=['eve', 'providerdb'])

PLAY RECAP ********************************************************************
testhost : ok=2 changed=1 unreachable=0 failed=0

What the docs' example for with_nested does is give permissions to all
listed users on all listed databases. Your snippet attempts to do that
too, which was probably not your intent.

Michael has given you a snippet for what we think that you wanted to
do. Here's what your posted code does: it will do 27 passes of
mysql_user functionality, because with_nested will return 27 lists
with all the combinations of user, password, host:
- user1, password1, host1
- user1, password1, host2
- user1, password1, host3
- user1, password2, host1
- user1, password2, host2
- user1, password2, host3
- etc

What I think happens then is that, with the '%' wildcard in the host
position, ansible will end up trying to create a user that already
exists, and return a mysql error number 1396.

Regards,

Javier Candeira

5

Thanks for clarification Javier

Edgars

svētdiena, 2013. gada 22. septembris 14:39:25 UTC+2, Javier Candeira rakstīja: