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!
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?
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:
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.
Which ansible version are you using?: Try command:
ansible --version
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)]
Also, module requires to define “login_host”, “login_user”, “login_password”, etc to connect to a remote database.
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
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.
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
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:
Have you checked the syntax check?
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:
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/modulesIf 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
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:
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: rootname: 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 ‘-’ indicatorThe 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.
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!
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:
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.
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.
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
]
}
}