Hello,
I am building a role for Ombi (Docker container), in which I want to have the option of deploying Mariadb (also Docker container). Everything works well so far - Mariadb container is deployed and then Ombi is deployed with both connecting with each other just fine. See: GitHub - Lebowski89/ombi_suite
However, I want to run tasks on an existing mariadb database - but am having no luck with the community.mysql.mysql_db module. Specifically, I want to make sure the Ombi database exists (akin to CREATE DATABASE IF NOT EXISTS “{{ ombi_mariadb_database }}”; ) and the Ombi user exists (akin to CREATE USER IF NOT EXISTS ‘{{ ombi_mariadb_auth_ombi_db_user }}’@‘%’ IDENTIFIED BY ‘{{ ombi_mariadb_auth_ombi_db_password }}’;). But if I run:
- name: Create Ombi mariadb database
community.mysql.mysql_db:
login_host: '{{ ombi_mariadb_basics_host }}'
login_password: '{{ ombi_mariadb_auth_root_password }}'
login_port: '{{ ombi_mariadb_ports_host }}'
name: '{{ ombi_mariadb_database }}'
state: 'present'
It works:
TASK [ombi : Create Ombi mariadb database] **********************************************************************************************************************************************************************************************************************************************************************************
task path: /ansible/roles/ombi/tasks/mariadb/mariadb_container.yml:134
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: ansible
<127.0.0.1> EXEC /bin/sh -c 'echo ~ansible && sleep 0'
<127.0.0.1> EXEC /bin/sh -c '( umask 77 && mkdir -p "` echo /home/ansible/.ansible/tmp `"&& mkdir "` echo /home/ansible/.ansible/tmp/ansible-tmp-1727853865.438928-353119-74195569487085 `" && echo ansible-tmp-1727853865.438928-353119-74195569487085="` echo /home/ansible/.ansible/tmp/ansible-tmp-1727853865.438928-353119-74195569487085 `" ) && sleep 0'
Using module file /usr/lib/python3/dist-packages/ansible_collections/community/mysql/plugins/modules/mysql_db.py
<127.0.0.1> PUT /home/ansible/.ansible/tmp/ansible-local-351681j8crpl74/tmpm80t5fkb TO /home/ansible/.ansible/tmp/ansible-tmp-1727853865.438928-353119-74195569487085/AnsiballZ_mysql_db.py
<127.0.0.1> EXEC /bin/sh -c 'chmod u+x /home/ansible/.ansible/tmp/ansible-tmp-1727853865.438928-353119-74195569487085/ /home/ansible/.ansible/tmp/ansible-tmp-1727853865.438928-353119-74195569487085/AnsiballZ_mysql_db.py && sleep 0'
<127.0.0.1> EXEC /bin/sh -c 'sudo -H -S -p "[sudo via ansible, key=dwhnkpnyfdwdjlokepuctyhquvomqzpd] password:" -u root /bin/sh -c '"'"'echo BECOME-SUCCESS-dwhnkpnyfdwdjlokepuctyhquvomqzpd ; /usr/bin/python3 /home/ansible/.ansible/tmp/ansible-tmp-1727853865.438928-353119-74195569487085/AnsiballZ_mysql_db.py'"'"' && sleep 0'
<127.0.0.1> EXEC /bin/sh -c 'rm -f -r /home/ansible/.ansible/tmp/ansible-tmp-1727853865.438928-353119-74195569487085/ > /dev/null 2>&1 && sleep 0'
ok: [localhost] => {
"changed": false,
"db": "Ombi",
"db_list": [
"Ombi"
],
"executed_commands": [],
"invocation": {
"module_args": {
"ca_cert": null,
"chdir": null,
"check_hostname": null,
"check_implicit_admin": false,
"client_cert": null,
"client_key": null,
"collation": "",
"config_file": "/root/.my.cnf",
"config_overrides_defaults": false,
"connect_timeout": 30,
"dump_extra_args": null,
"encoding": "",
"force": false,
"hex_blob": false,
"ignore_tables": [],
"login_host": "192.168.80.68",
"login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
"login_port": 3306,
"login_unix_socket": null,
"login_user": null,
"master_data": 0,
"name": [
"Ombi"
],
"pipefail": false,
"quick": true,
"restrict_config_file": false,
"single_transaction": false,
"skip_lock_tables": false,
"state": "present",
"target": null,
"unsafe_login_password": false,
"use_shell": false
}
}
}
But if I deviate from the database that was already created by the Mariadb docker container, I get permission denied:
TASK [ombi : Create Ombi mariadb database] **********************************************************************************************************************************************************************************************************************************************************************************
task path: /ansible/roles/ombi/tasks/mariadb/mariadb_container.yml:134
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: ansible
<127.0.0.1> EXEC /bin/sh -c 'echo ~ansible && sleep 0'
<127.0.0.1> EXEC /bin/sh -c '( umask 77 && mkdir -p "` echo /home/ansible/.ansible/tmp `"&& mkdir "` echo /home/ansible/.ansible/tmp/ansible-tmp-1727854054.8044755-354815-52329710360085 `" && echo ansible-tmp-1727854054.8044755-354815-52329710360085="` echo /home/ansible/.ansible/tmp/ansible-tmp-1727854054.8044755-354815-52329710360085 `" ) && sleep 0'
Using module file /usr/lib/python3/dist-packages/ansible_collections/community/mysql/plugins/modules/mysql_db.py
<127.0.0.1> PUT /home/ansible/.ansible/tmp/ansible-local-353671bot3of5p/tmp1c2xzy_r TO /home/ansible/.ansible/tmp/ansible-tmp-1727854054.8044755-354815-52329710360085/AnsiballZ_mysql_db.py
<127.0.0.1> EXEC /bin/sh -c 'chmod u+x /home/ansible/.ansible/tmp/ansible-tmp-1727854054.8044755-354815-52329710360085/ /home/ansible/.ansible/tmp/ansible-tmp-1727854054.8044755-354815-52329710360085/AnsiballZ_mysql_db.py && sleep 0'
<127.0.0.1> EXEC /bin/sh -c 'sudo -H -S -p "[sudo via ansible, key=oijwegtzklpjiagaznmgcfiftwxfktmp] password:" -u root /bin/sh -c '"'"'echo BECOME-SUCCESS-oijwegtzklpjiagaznmgcfiftwxfktmp ; /usr/bin/python3 /home/ansible/.ansible/tmp/ansible-tmp-1727854054.8044755-354815-52329710360085/AnsiballZ_mysql_db.py'"'"' && sleep 0'
<127.0.0.1> EXEC /bin/sh -c 'rm -f -r /home/ansible/.ansible/tmp/ansible-tmp-1727854054.8044755-354815-52329710360085/ > /dev/null 2>&1 && sleep 0'
The full traceback is:
Traceback (most recent call last):
File "/tmp/ansible_community.mysql.mysql_db_payload_l9hup7ja/ansible_community.mysql.mysql_db_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_db.py", line 737, in main
File "/tmp/ansible_community.mysql.mysql_db_payload_l9hup7ja/ansible_community.mysql.mysql_db_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_db.py", line 576, in db_create
File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 148, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 310, in _query
conn.query(q)
File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 775, in _read_query_result
result.read()
File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1156, in read
first_packet = self.connection._read_packet()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 725, in _read_packet
packet.raise_for_error()
File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/usr/lib/python3/dist-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1006, 'Can\'t create database \'ANSIBLE_TEST\' (errno: 13 "Permission denied")')
fatal: [localhost]: FAILED! => {
"changed": false,
"invocation": {
"module_args": {
"ca_cert": null,
"chdir": null,
"check_hostname": null,
"check_implicit_admin": false,
"client_cert": null,
"client_key": null,
"collation": "",
"config_file": "/root/.my.cnf",
"config_overrides_defaults": false,
"connect_timeout": 30,
"dump_extra_args": null,
"encoding": "",
"force": false,
"hex_blob": false,
"ignore_tables": [],
"login_host": "192.168.80.68",
"login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
"login_port": 3306,
"login_unix_socket": null,
"login_user": null,
"master_data": 0,
"name": [
"ANSIBLE_TEST"
],
"pipefail": false,
"quick": true,
"restrict_config_file": false,
"single_transaction": false,
"skip_lock_tables": false,
"state": "present",
"target": null,
"unsafe_login_password": false,
"use_shell": false
}
},
"msg": "error creating database: (1006, 'Can\\'t create database \\'ANSIBLE_TEST\\' (errno: 13 \"Permission denied\")')"
}
So it seems like it can ping an existing database but cannot make a new one. I run similar tasks with the postgres module (connecting to a postgres docker container) without issues. In both cases, the docker containers are given the password via a password file, while the module is given the password directly. Not sure what I am doing wrong here. Just want to ping and create a database on an existing mariadb docker container (provided the correct root password is given).
Ansible version and relevant modules:
ansible [core 2.16.11]
config file = /etc/ansible/ansible.cfg
configured module search path = ['/home/ansible/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python3/dist-packages/ansible
ansible collection location = /home/ansible/.ansible/collections:/usr/share/ansible/collections
executable location = /usr/bin/ansible
python version = 3.11.2 (main, Aug 26 2024, 07:20:54) [GCC 12.2.0] (/usr/bin/python3)
jinja version = 3.1.2
libyaml = True
community.mysql 3.10.3
community.docker 3.12.1
MariaDB Docker Container task:
- name: Create mariadb container
when: not ombi_mariadb_container_result.exists
community.docker.docker_container:
name: '{{ ombi_mariadb_basics_name }}'
image: '{{ ombi_mariadb_basics_image_repo }}:{{ ombi_mariadb_basics_image_tag }}'
networks:
- name: '{{ ombi_mariadb_network }}'
env:
PUID: '{{ ombi_mariadb_env_puid }}'
PGID: '{{ ombi_mariadb_env_pgid }}'
TZ: '{{ ombi_mariadb_env_timezone }}'
MARIADB_ROOT_PASSWORD_FILE: '/keys/{{ ombi_mariadb_auth_root_password_file }}'
MARIADB_DATABASE: '{{ ombi_mariadb_database }}'
MARIADB_USER: '{{ ombi_mariadb_auth_ombi_db_user }}'
MARIADB_PASSWORD_FILE: '/keys/{{ ombi_mariadb_auth_ombi_db_password_file }}'
ports:
- '{{ ombi_mariadb_ports_host }}:{{ ombi_mariadb_ports_cont }}'
volumes: '{{ ombi_mariadb_binds }}'
restart_policy: '{{ ombi_mariadb_basics_restart_policy }}'
Mariadb Env:
ombi_mariadb_basics_host: '192.168.80.68' ## VM on LAN
ombi_mariadb_basics_name: 'mariadb'
ombi_mariadb_basics_image_repo: 'mariadb'
ombi_mariadb_basics_image_tag: 'latest'
ombi_mariadb_ports_host: '3306'
ombi_mariadb_ports_cont: '3306'
ombi_mariadb_database: 'Ombi'
Thanks