Simple Playbook for mysql query

[Adam]

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!

---
- name: Select trunk name from pbx
hosts: demoserver
become: true
become_user: root

tasks:
- name: Select trunk names
community.mysql.mysql_query:
login_db: asterisk
query: SELECT * FROM trunks

[Roberto Paz]

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?

[Adam]

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.  

[Roberto Paz]

Which ansible version are you using?: Try command:

ansible --version

[Adam]

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)]

[Roberto Paz]

Also, module requires to define "login_host", "login_user", "login_password", etc to connect to a remote database.

[Roberto Paz]

I think the module may be not installed yet. According to:

https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_query_module.html

Try:

ansible-galaxy collection install community.mysql

[Adam]

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.

[rajthecomputerguy]

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

[Adam]

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

[Rajthecomputerguy]

Have you checked the syntax check? 

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/db0453c4-a9d4-41ea-b7c1-adc57febdbe9n%40googlegroups.com.

[Adam]

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

[Markus Falb]

Hi,

> On 18.09.2021, at 14:28, Adam <adam.f...@gmail.com> wrote:
>
> 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?

```

- name: get trunk names

community.mysql.mysql_query:
```

it should be (note the spaces)

```

- name: get trunk names

community.mysql.mysql_query:
```

Best Regards, Markus

[Adam]

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

[Dick Visser]

Wrong indentation. 

Take a close look at the example and make sure you get the indentation right. 

--

You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/3eb1c218-3c34-4b11-898b-c72e9efc1f83n%40googlegroups.com.

--

Sent from a mobile device - please excuse the brevity, spelling and punctuation.

[Adam]

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!

[steve missoh]

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:

---

- hosts: localhost

  tasks:

  - name: query a db

    mysql_query:

            login_user: steve

            login_password: steve

            login_db: testdb

            login_host: db.example.com

            query: select * from test

    register: result

  - name:

    debug:

      var: result

[Adam]

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.

[flowerysong]

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.

[Adam]

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

        ]

    }

}

[steve missoh]

export ANSIBLE_STDOUT_CALLBACK=debug
or you  put it in ansible.cfg in your project folder.

(check current value with ansible-config dump | grep -i stdout_callba)

Cheers !

[Adam]

That will work perfect.

Thank you!