# mysql\_user module with nested loops **URL:** \ **Category:** Ansible Project\ **Created:** [September 21, 2013, 5:34pm UTC](https://forum.ansible.com/t/mysql-user-module-with-nested-loops/13522 "2013-09-21T17:34:43Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Edgars](https://avatars.discourse-cdn.com/v4/letter/e/f17d59/32.png) [@Edgars](https://forum.ansible.com/u/Edgars)\ **Post date:** [September 21, 2013, 5:34pm UTC](https://forum.ansible.com/t/mysql-user-module-with-nested-loops/13522/1 "2013-09-21T17:34:43Z")
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 ---
**Author:** ![Michael\_DeHaan2](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@Michael\_DeHaan2](https://forum.ansible.com/u/Michael_DeHaan2)\ **Post date:** [September 21, 2013, 10:23pm UTC](https://forum.ansible.com/t/mysql-user-module-with-nested-loops/13522/2 "2013-09-21T22:23:03Z")
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](http://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. ---
**Author:** ![Edgars](https://avatars.discourse-cdn.com/v4/letter/e/f17d59/32.png) [@Edgars](https://forum.ansible.com/u/Edgars)\ **Post date:** [September 22, 2013, 8:23am UTC](https://forum.ansible.com/t/mysql-user-module-with-nested-loops/13522/3 "2013-09-22T08:23:40Z")
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: ---
**Author:** ![Javier\_Candeira](https://avatars.discourse-cdn.com/v4/letter/j/c6cbf5/32.png) [@Javier\_Candeira](https://forum.ansible.com/u/Javier_Candeira)\ **Post date:** [September 22, 2013, 12:39pm UTC](https://forum.ansible.com/t/mysql-user-module-with-nested-loops/13522/4 "2013-09-22T12:39:25Z")
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](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 ---
**Author:** ![Edgars](https://avatars.discourse-cdn.com/v4/letter/e/f17d59/32.png) [@Edgars](https://forum.ansible.com/u/Edgars)\ **Post date:** [September 22, 2013, 5:52pm UTC](https://forum.ansible.com/t/mysql-user-module-with-nested-loops/13522/5 "2013-09-22T17:52:35Z")
Thanks for clarification Javier Edgars svētdiena, 2013. gada 22. septembris 14:39:25 UTC+2, Javier Candeira rakstīja: