Syntax error on user updates CentOS6 with "hosts_all" enabled #39

hluaces · 2020-09-30T12:14:36Z

SUMMARY

Trying to update a mysql user using the mysql_user module with hosts_all: true fails on CentOS6 machines which are being provisioned with Ansible 2.10.1:

"msg": "(1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1\")"

I've been playing around and found that changing the list in this line to a single element (to clarify, modify [user] for user) seems to make the module work. I assume that's not the root cause though.

ISSUE TYPE

Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.10.1
  config file = None
  configured module search path = ['/home/hector/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/hector/.local/lib/python3.8/site-packages/ansible
  executable location = /home/hector/.local/bin/ansible
  python version = 3.8.2 (default, Jul 16 2020, 14:00:26) [GCC 9.3.0]

Works fine on 2.9.13.

CONFIGURATION

Reproducible with the default Ansible configuration.

OS / ENVIRONMENT

CentOS6 with 10.3.24-MariaDB.

7 and 8 works as expected.

STEPS TO REPRODUCE

Use the mysql_user module with hosts_all: true to update a mysql user on a CentOS6. For example:

- name: "Make sure user exists"
  mysql_user:
    name: root
    host: 'localhost'
    password: "{{ mariadb_root_password }}"
    state: 'present'
    host_all: true

EXPECTED RESULTS

Module should not throw an error.

ACTUAL RESULTS

Module ends with a mysql syntax error:

    redirecting (type: connection) ansible.builtin.docker to community.general.docker
    Loading collection community.general from /home/hector/.local/lib/python3.8/site-packages/ansible_collections/community/general
    <centos6_systemd> ESTABLISH DOCKER CONNECTION FOR USER: root
    <centos6_systemd> EXEC ['/usr/bin/docker', b'exec', b'-i', 'centos6_systemd', '/bin/sh', '-c', "/bin/sh -c 'echo ~ && sleep 0'"]
    <centos6_systemd> EXEC ['/usr/bin/docker', b'exec', b'-i', 'centos6_systemd', '/bin/sh', '-c', '/bin/sh -c \'( umask 77 && mkdir -p "` echo /root/.ansible/tmp `"&& mkdir "` echo /root/.ansible/tmp/ansible-tmp-1601466149.6049237-476078-107517031136793 `" && echo ansible-tmp-1601466149.6049237-476078-107517031136793="` echo /root/.ansible/tmp/ansible-tmp-1601466149.6049237-476078-107517031136793 `" ) && sleep 0\'']
    redirecting (type: modules) ansible.builtin.mysql_user to community.mysql.mysql_user
    Including module_utils file ansible/__init__.py
    Including module_utils file ansible/module_utils/__init__.py
    Including module_utils file ansible/module_utils/_text.py
    Including module_utils file ansible/module_utils/basic.py
    Including module_utils file ansible/module_utils/common/_collections_compat.py
    Including module_utils file ansible/module_utils/common/__init__.py
    Including module_utils file ansible/module_utils/common/_json_compat.py
    Including module_utils file ansible/module_utils/common/_utils.py
    Including module_utils file ansible/module_utils/common/file.py
    Including module_utils file ansible/module_utils/common/parameters.py
    Including module_utils file ansible/module_utils/common/collections.py
    Including module_utils file ansible/module_utils/common/process.py
    Including module_utils file ansible/module_utils/common/sys_info.py
    Including module_utils file ansible/module_utils/common/text/converters.py
    Including module_utils file ansible/module_utils/common/text/__init__.py
    Including module_utils file ansible/module_utils/common/text/formatters.py
    Including module_utils file ansible/module_utils/common/validation.py
    Including module_utils file ansible/module_utils/common/warnings.py
    Including module_utils file ansible/module_utils/compat/selectors.py
    Including module_utils file ansible/module_utils/compat/__init__.py
    Including module_utils file ansible/module_utils/compat/_selectors2.py
    Including module_utils file ansible/module_utils/distro/__init__.py
    Including module_utils file ansible/module_utils/distro/_distro.py
    Including module_utils file ansible/module_utils/parsing/convert_bool.py
    Including module_utils file ansible/module_utils/parsing/__init__.py
    Including module_utils file ansible/module_utils/pycompat24.py
    Including module_utils file ansible/module_utils/six/__init__.py
    Including module_utils file ansible_collections/community/mysql/plugins/module_utils/database.py
    Including module_utils file ansible_collections/__init__.py
    Including module_utils file ansible_collections/community/__init__.py
    Including module_utils file ansible_collections/community/mysql/__init__.py
    Including module_utils file ansible_collections/community/mysql/plugins/__init__.py
    Including module_utils file ansible_collections/community/mysql/plugins/module_utils/__init__.py
    Including module_utils file ansible_collections/community/mysql/plugins/module_utils/mysql.py
    Using module file /home/hector/.local/lib/python3.8/site-packages/ansible_collections/community/mysql/plugins/modules/mysql_user.py
    <centos6_systemd> PUT /home/hector/.ansible/tmp/ansible-local-474153pkl19a0a/tmpaou35jm4 TO /root/.ansible/tmp/ansible-tmp-1601466149.6049237-476078-107517031136793/AnsiballZ_mysql_user.py
    <centos6_systemd> EXEC ['/usr/bin/docker', b'exec', b'-i', 'centos6_systemd', '/bin/sh', '-c', "/bin/sh -c 'chmod u+x /root/.ansible/tmp/ansible-tmp-1601466149.6049237-476078-107517031136793/ /root/.ansible/tmp/ansible-tmp-1601466149.6049237-476078-107517031136793/AnsiballZ_mysql_user.py && sleep 0'"]
    <centos6_systemd> EXEC ['/usr/bin/docker', b'exec', b'-i', 'centos6_systemd', '/bin/sh', '-c', "/bin/sh -c '/usr/bin/python /root/.ansible/tmp/ansible-tmp-1601466149.6049237-476078-107517031136793/AnsiballZ_mysql_user.py && sleep 0'"]
    <centos6_systemd> EXEC ['/usr/bin/docker', b'exec', b'-i', 'centos6_systemd', '/bin/sh', '-c', "/bin/sh -c 'rm -f -r /root/.ansible/tmp/ansible-tmp-1601466149.6049237-476078-107517031136793/ > /dev/null 2>&1 && sleep 0'"]
The full traceback is:
WARNING: The below traceback may *not* be related to the actual failure.
  File "/tmp/ansible_mysql_user_payload_itod9S/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 1091, in main
  File "/tmp/ansible_mysql_user_payload_itod9S/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 520, in user_mod
  File "/tmp/ansible_mysql_user_payload_itod9S/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 704, in user_get_hostnames
  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
fatal: [centos6_systemd]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "ca_cert": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "config_file": "/********/.my.cnf",
            "connect_timeout": 30,
            "encrypted": false,
            "host": "localhost",
            "host_all": true,
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": null,
            "name": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "plugin": null,
            "plugin_auth_string": null,
            "plugin_hash_string": null,
            "priv": "*.*:ALL,GRANT",
            "resource_limits": null,
            "sql_log_bin": true,
            "state": "present",
            "tls_requires": null,
            "update_password": "always",
            "user": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER"
        }
    },
    "msg": "(1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1\")"
}

The text was updated successfully, but these errors were encountered:

Andersson007 · 2020-09-30T13:15:49Z

@hluaces hey, thanks for reporting the issue!
Have you tried to remove a user using host_all=yes?

Andersson007 · 2020-09-30T13:21:21Z

From the doc (for 2.9)

  38   host_all:
  39     description:
  40       - Override the host option, making ansible apply changes to all hostnames for a given user.
  41       - This option cannot be used when creating users.

This feature can't be used when creating a user. Let's see what is this when we're modifying users..

Andersson007 · 2020-09-30T13:29:36Z

Looks related to https://github.com/ansible-collections/community.general/pull/490/files
I'll look at this later

hluaces · 2020-09-30T13:34:39Z

Hey @Andersson007 , thanks to you for your reply and your work.

Have you tried to remove a user using host_all=yes?

Deleting users works properly.

This feature can't be used when creating a user.

Yeah, that's on me, I apologize. I'm actually updating a user password on my task so I can confirm that it does happen on user updates. I'll edit the original issue to avoid further confusion.

Andersson007 · 2020-09-30T13:46:55Z

You're right! I modified the function fixing a bug but didn't modify its invocation. Your suggestion should work, testing now

Andersson007 · 2020-09-30T13:53:49Z

@hluaces could you please confirm there #40 that everything works as expected with the suggested change?

Andersson007 · 2020-09-30T13:59:19Z

@hluaces no needed, i added CI tests

Andersson007 · 2020-09-30T14:00:23Z

once they passed i'll merge the PR and later today or tomorrow will release community.mysql 1.0.2

hluaces · 2020-09-30T14:02:12Z

That's great, @Andersson007; and very quick. Thank you very much for your work.

Right now I'm using the pip ansible package. I'm not really familiar with the development cycle of Ansible, it's pip packages and the new community collections. Do you have any idea as to when these changes will reach pip?

Andersson007 · 2020-09-30T14:08:48Z

@hluaces you and community are welcome! Thank you for quick reporting!
When community.mysql 1.0.2 is released, you can install it separately https://docs.ansible.com/ansible/latest/user_guide/collections_using.html
I'll put a note here.

Andersson007 · 2020-10-01T05:38:27Z

@hluaces the fix release tarball 1.0.2 is now available on https://galaxy.ansible.com/community/mysql , would be grateful for any feedback

hluaces · 2020-10-01T07:57:33Z

My CI passed correctly when installing the collection via ansible-galaxy.

Now I'll try to find out when it will be ported to the ansible pip package.

Thank you!

Andersson007 · 2020-10-01T08:54:14Z

@hluaces cool, thanks for the quick feedback!

hluaces changed the title ~~Syntax error on user creation CentOS6 with "hosts_all" enabled~~ Syntax error on user updates CentOS6 with "hosts_all" enabled Sep 30, 2020

Andersson007 mentioned this issue Sep 30, 2020

mysql_user: fix error when modifying user with host_all #40

Merged

Andersson007 closed this as completed in #40 Sep 30, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Syntax error on user updates CentOS6 with "hosts_all" enabled #39

Syntax error on user updates CentOS6 with "hosts_all" enabled #39

hluaces commented Sep 30, 2020 •

edited

Loading

Andersson007 commented Sep 30, 2020

Andersson007 commented Sep 30, 2020 •

edited

Loading

Andersson007 commented Sep 30, 2020

hluaces commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

hluaces commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

Andersson007 commented Oct 1, 2020 •

edited

Loading

hluaces commented Oct 1, 2020

Andersson007 commented Oct 1, 2020

Syntax error on user updates CentOS6 with "hosts_all" enabled #39

Syntax error on user updates CentOS6 with "hosts_all" enabled #39

Comments

hluaces commented Sep 30, 2020 • edited Loading

SUMMARY

ISSUE TYPE

COMPONENT NAME

ANSIBLE VERSION

CONFIGURATION

OS / ENVIRONMENT

STEPS TO REPRODUCE

EXPECTED RESULTS

ACTUAL RESULTS

Andersson007 commented Sep 30, 2020

Andersson007 commented Sep 30, 2020 • edited Loading

Andersson007 commented Sep 30, 2020

hluaces commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

hluaces commented Sep 30, 2020

Andersson007 commented Sep 30, 2020

Andersson007 commented Oct 1, 2020 • edited Loading

hluaces commented Oct 1, 2020

Andersson007 commented Oct 1, 2020

hluaces commented Sep 30, 2020 •

edited

Loading

Andersson007 commented Sep 30, 2020 •

edited

Loading

Andersson007 commented Oct 1, 2020 •

edited

Loading