# Simple Playbook for mysql query **URL:** \ **Category:** Ansible Project\ **Created:** [September 17, 2021, 8:03pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755 "2021-09-17T20:03:47Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 17, 2021, 8:03pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/1 "2021-09-17T20:03:47Z")
Hello, I have been using Ansible for very basic playbooks. I now want to have a simply playbook that connected to my asterisk PBX, and lookup a value in the my SQL table. I have tried many various combinations to get this working, but keep getting errors. Can someone help with this simply playbook? I dont know if the issue is bad YAML formatting, or something else. Thank you in advance! ---
**Author:** ![Roberto\_J\_R\_Paz](https://avatars.discourse-cdn.com/v4/letter/r/d2c977/32.png) [@Roberto\_J\_R\_Paz](https://forum.ansible.com/u/Roberto_J_R_Paz)\ **Post date:** [September 17, 2021, 8:07pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/2 "2021-09-17T20:07:58Z")
Can you include the returning error message you get? Is the database running in the same server where you are running the playbook? Database is reachable if you use the mysql client? ---
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 17, 2021, 8:37pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/3 "2021-09-17T20:37:50Z")
Sure! ERROR! couldn’t resolve module/action ‘community.mysql.mysql\_query’. This often indicates a misspelling, missing collection, or incorrect module path. The error appears to be in ‘/etc/ansible/playbooks/get\_trunk\_name.yml’: line 8, column 5, but may be elsewhere in the file depending on the exact syntax problem. The offending line appears to be: tasks: - name: Select trunk names ^ here The database is NOT running on the ansible server. The ansible server is not running on my local PC , but rather a server at a colo at a different IP. So I can’t use the mysql client from that server. ---
**Author:** ![Roberto\_J\_R\_Paz](https://avatars.discourse-cdn.com/v4/letter/r/d2c977/32.png) [@Roberto\_J\_R\_Paz](https://forum.ansible.com/u/Roberto_J_R_Paz)\ **Post date:** [September 17, 2021, 8:52pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/4 "2021-09-17T20:52:22Z")
Which ansible version are you using?: Try command: ansible --version ---
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 17, 2021, 8:53pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/5 "2021-09-17T20:53:20Z")
ansible 2.9.25 config file = /etc/ansible/ansible.cfg configured module search path = [‘/home/intulseadmin/.ansible/plugins/modules’, ‘/usr/share/ansible/plugins/modules’] ansible python module location = /usr/lib/python3.6/site-packages/ansible executable location = /usr/bin/ansible python version = 3.6.8 (default, Mar 19 2021, 05:13:41) [GCC 8.4.1 20200928 (Red Hat 8.4.1-1)] ---
**Author:** ![Roberto\_J\_R\_Paz](https://avatars.discourse-cdn.com/v4/letter/r/d2c977/32.png) [@Roberto\_J\_R\_Paz](https://forum.ansible.com/u/Roberto_J_R_Paz)\ **Post date:** [September 17, 2021, 8:54pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/6 "2021-09-17T20:54:18Z")
Also, module requires to define “login\_host”, “login\_user”, “login\_password”, etc to connect to a remote database. ---
**Author:** ![Roberto\_J\_R\_Paz](https://avatars.discourse-cdn.com/v4/letter/r/d2c977/32.png) [@Roberto\_J\_R\_Paz](https://forum.ansible.com/u/Roberto_J_R_Paz)\ **Post date:** [September 17, 2021, 8:57pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/7 "2021-09-17T20:57:49Z")
I think the module may be not installed yet. According to: [https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql\_query\_module.html](https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_query_module.html) Try: ansible-galaxy collection install community.mysql ---
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 18, 2021, 12:33am UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/8 "2021-09-18T00:33:15Z")
I confirmed that it was already installed by running that command again. It’s installed, and I just specified the login host, user, password, etc. ---
**Author:** ![Pushparaj\_Gothandam](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/pushparaj_gothandam/32/5538_2.png) [@Pushparaj\_Gothandam](https://forum.ansible.com/u/Pushparaj_Gothandam)\ **Post date:** [September 18, 2021, 2:43am UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/9 "2021-09-18T02:43:24Z")
Can you check the syntax using **ansible-playbook yuouplaybook.yaml --syntax-check** also, ensure that module exists by running **ansible-doc community.mysql.mysql\_query** ---
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 18, 2021, 12:28pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/10 "2021-09-18T12:28:22Z")
SO this is what happens when I check if the module exists when logged in as a normal user: [WARNING]: module community.mysql.mysql\_query not found in: /home/normaluser/.ansible/plugins/modules:/usr/share/ansible/plugins/modules:/usr/lib/python3.6/site- packages/ansible/modules If I run that same check when logged in as root, it appears like I get the appropriate output with mysql information. But I still get the error when running the syntax check: ERROR! ‘community.mysql.mysql\_query’ is not a valid attribute for a Play The error appears to be in ‘/etc/ansible/playbooks/get\_trunk\_name.yml’: line 12, column 3, but may be elsewhere in the file depending on the exact syntax problem. The offending line appears to be: - name: get trunk names ^ here ---
**Author:** ![Pushparaj\_Gothandam](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/pushparaj_gothandam/32/5538_2.png) [@Pushparaj\_Gothandam](https://forum.ansible.com/u/Pushparaj_Gothandam)\ **Post date:** [September 18, 2021, 12:45pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/11 "2021-09-18T12:45:09Z")
Have you checked the syntax check? ---
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 18, 2021, 1:39pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/12 "2021-09-18T13:39:12Z")
Yes, the error is the same. ERROR! ‘community.mysql.mysql\_query’ is not a valid attribute for a Play The error appears to be in ‘/etc/ansible/playbooks/get\_trunk\_name.yml’: line 12, column 3, but may be elsewhere in the file depending on the exact syntax problem. The offending line appears to be: - name: get trunk names ^ here ---
**Author:** ![Markus\_Falb](https://avatars.discourse-cdn.com/v4/letter/m/b5e925/32.png) [@Markus\_Falb](https://forum.ansible.com/u/Markus_Falb)\ **Post date:** [September 18, 2021, 2:13pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/13 "2021-09-18T14:13:07Z")
Hi, > SO this is what happens when I check if the module exists when logged in as a normal user: > > [WARNING]: module community.mysql.mysql\_query not found in: > /home/normaluser/.ansible/plugins/modules:/usr/share/ansible/plugins/modules:/usr/lib/python3.6/site- > packages/ansible/modules > > If I run that same check when logged in as root, it appears like I get the appropriate output with mysql information. SO, you installed the collection as root, probably into /root/.ansible/plugins/modules unprivileged normaluser does not have /root/.ansible/plugins/modules in the $ANSIBLE\_COLLECTIONS\_PATHS one solution would be: install it again as normaluser > But I still get the error when running the syntax check: > > ERROR! 'community.mysql.mysql\_query' is not a valid attribute for a Play > > The error appears to be in '/etc/ansible/playbooks/get\_trunk\_name.yml': line 12, column 3, but may > be elsewhere in the file depending on the exact syntax problem. > > The offending line appears to be: > > - name: get trunk names >   ^ here SO, syntax went wrong? Wrong indentation? ```auto - name: get trunk names community.mysql.mysql_query: ``` it should be (note the spaces) ```auto - name: get trunk names community.mysql.mysql_query: ``` Best Regards, Markus ---
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 18, 2021, 5:03pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/14 "2021-09-18T17:03:28Z")
OK, thank you. I think I am getting closer. I installed as the normal user. Now I am just getting another error. This is the file: ---
**Author:** ![Dick\_Visser](https://avatars.discourse-cdn.com/v4/letter/d/d9b06d/32.png) [@Dick\_Visser](https://forum.ansible.com/u/Dick_Visser)\ **Post date:** [September 18, 2021, 6:10pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/15 "2021-09-18T18:10:35Z")
> OK, thank you. I think I am getting closer. I installed as the normal user. Now I am just getting another error. This is the file: > > * * * > > - name: Select trunk name from pbx > hosts: demoserver > become: true > become\_user: root > > - name: get trunk names > community.mysql.mysql\_query: > login\_db: asterisk > login\_user: root > login\_password: > query: SELECT \* FROM trunks > … > > This is the error now: > > ERROR! We were unable to read either as JSON nor YAML, these are the errors we got from each: > JSON: Expecting value: line 1 column 1 (char 0) > > Syntax Error while loading YAML. > did not find expected ‘-’ indicator > > The error appears to be in ‘/etc/ansible/playbooks/get\_trunk\_name.yml’: line 9, column 1, but may > be elsewhere in the file depending on the exact syntax problem. > > The offending line appears to be: > > - name: get trunk names > community.mysql.mysql\_query: > ^ here Wrong indentation. Take a close look at the example and make sure you get the indentation right. ---
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 18, 2021, 6:41pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/16 "2021-09-18T18:41:51Z")
I appreciate all the help so far. I just simply do not understand what I am doing wrong with the spacing, indentation, etc. It all looks the exact same to me in the examples that are posted to me here. I don’t get it. I am looking at other examples of working play books that I have, and they work perfectly. Would it be possible for someone to explain in detail where specifically the problem is? Or provide a working example with proper spacing/indentation? (currently banging head against keyboard) Thanks! ---
**Author:** ![steve\_missoh](https://avatars.discourse-cdn.com/v4/letter/s/3da27b/32.png) [@steve\_missoh](https://forum.ansible.com/u/steve_missoh)\ **Post date:** [September 18, 2021, 7:32pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/17 "2021-09-18T19:32:03Z")
Hello Adam, 1 - I think the module isn’t loaded on the control node. I think you need to do load it locally because its complaining it cannot find the module. 2 - Ensure you have a mysql connector on the control node (most common is PyMySQL) In summary: after ansible-galaxy collection install community.mysql you should copy the modules into an adhoc directory (check which ones by running ansible --version) cp -p ~/.ansible/collections/ansible\_collections/community/mysql/plugins/modules/\* /home/steve/.ansible/plugins/modules/ if it works, you should be able to check that with ansible localhost -m mysql\_query for example or even ansible-doc mysql\_query Then, run simply your playbook. example: ---
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 18, 2021, 8:05pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/18 "2021-09-18T20:05:43Z")
Thank you! That was exactly the problem. I wonder why on earth it didn’t copy the modules properly on installation. Regardless, you made my day. Thanks again to you and everyone else who assisted. ---
**Author:** ![flowerysong1](https://avatars.discourse-cdn.com/v4/letter/f/ecae2f/32.png) [@flowerysong1](https://forum.ansible.com/u/flowerysong1)\ **Post date:** [September 18, 2021, 8:24pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/19 "2021-09-18T20:24:32Z")
2.9 is before the collection split, so the available version of the module is called simply `mysql_query`. Unless you have a specific reason to use the collection (and have correctly installed the collection locally), you should not use `community.mysql.mysql_query` with 2.9.x. ---
**Author:** ![Adam1](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/adam1/32/7102_2.png) [@Adam1](https://forum.ansible.com/u/Adam1)\ **Post date:** [September 18, 2021, 8:37pm UTC](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755/20 "2021-09-18T20:37:55Z")
Thank you for the explanation Flowerysong. As a followup, to learn how to do some proper formatting, I would like to format my output and export it to a file. For example, I successfully get the following output below. In an ideal world, I could filter this output and put it into a file. I would like something like this: SERVER1: name: trunk-primary SERVER2: name: TRUNK How would I do that? TASK [debug] \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* ok: [SERVER 1] =\> { “result”: { “changed”: false, “executed\_queries”: [ “select name from trunks” ], “failed”: false, “query\_result”: [ [ { “name”: “trunk-primary” } ] ], “rowcount”: [ 1 ] } } ok: [SERVER 2] =\> { “result”: { “changed”: false, “executed\_queries”: [ “select name from trunks” ], “failed”: false, “query\_result”: [ [ { “name”: “TRUNK” } ] ], “rowcount”: [ 1 ] } } [Next page](https://forum.ansible.com/t/simple-playbook-for-mysql-query/35755.md?page=2)