mysql_user module issue with utf8 char like "£"

[emper0r]

Summary

Using mysql_user module to add new user into mysql database on centos 7 I can send user, password with '£' symbol in the string and can login after, same code using centos 8 can't login with the same password.

Issue Type

Bug Report

Component Name

mysql_user

Ansible Version

$ ansible --version

ansible 2.10.7
  config file = /home/emper0r/iac/ansible.cfg
  configured module search path = ['/home/emper0r/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/bin/python_virtualenvs/ansible-2.10.7-devops_env/lib/python3.6/site-packages/ansible
  executable location = /usr/local/bin/python_virtualenvs/ansible-2.10.7-devops_env/bin/ansible
  python version = 3.6.8 (default, Aug 24 2020, 17:57:11) [GCC 8.3.1 20191121 (Red Hat 8.3.1-5)]

Configuration

$ ansible-config dump --only-changed

ANSIBLE_PIPELINING(/home/emper0r/iac/ansible.cfg) = False
DEFAULT_CALLBACK_WHITELIST(/home/emper0r/iac/ansible.cfg) = ['profile_tasks']
DEFAULT_HOST_LIST(/home/emper0r/iac/ansible.cfg) = ['/home/emper0r/iac/inventories/devel/hosts']
DEFAULT_LOAD_CALLBACK_PLUGINS(/home/emper0r/iac/ansible.cfg) = True
DEFAULT_STDOUT_CALLBACK(env: ANSIBLE_STDOUT_CALLBACK) = yaml
HOST_KEY_CHECKING(/home/emper0r/iac/ansible.cfg) = False

OS / Environment

CentOS 7: OK
CentOS 8: Not working

Steps to Reproduce

  hosts: mysql_node

  tasks:
    - name: Create db user and Grant MySQl privileges
      mysql_user:
        login_host: '127.0.0.1'
        login_port: 3306
        login_user: root
        login_password: "{{ root_password }}"
        login_unix_socket: "/mysql/mysql.sock"
        name: "usertest1"
        host: 'localhost'
        password: "123abc£abc123"
        update_password: always
        priv: '*.*:SUPER,SHOW DATABASES,SELECT,CREATE,INSERT,CREATE TABLESPACE,PROCESS,RELOAD,LOCK TABLES,REPLICATION CLIENT,EVENT'
        state: present

Expected Results

do login on MySQL successful using CentOS 8

Actual Results

Can't login into MySQL on CentOS 8
when I explode AnsibleZ.py I can see the string like '123abc\u00a3abc123' 
that \\u00a3 means '£' character

that same encoding exist on CentOS 7 but there works fine.

when launch test playbook the last result hash authetication_strings on mysql are differents.

Maybe exist some issue connection client on mysql_user.py module when try to connect into MySQL server

both servers are Locate: en_US.UTF-8, and mysql settings utf8mb4

On both servers CentOS7 and CentOS8 are the same locales settings systems and MySQL server

mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8    |
+--------------------------+---------+
7 rows in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'collation_connection%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT @@character_set_client;
+------------------------+
| @@character_set_client |
+------------------------+
| utf8mb4                |
+------------------------+
1 row in set (0.00 sec)

[root@dbserver debug_dir]# localectl status
   System Locale: LANG=en_US.UTF-8
       VC Keymap: it2
      X11 Layout: it,us
     X11 Variant: ,

[emper0r]

Enabling mysql query log to see what receive it

 SELECT CONCAT('*', UCASE(SHA1(UNHEX(SHA1('123abc| abc123'))))) 

[Andersson007]

@emper0r hi, thanks for reporting this! We'll try to take a look ASAP!

[Andersson007]

ah, I see now that you created an issue also in pymysql.

[emper0r]

Yes... and them close the issue saying set charset utf8mb4, and I had already setup that utf8mb4 ... would be possible add on module that option for test it?.. I can't see on the mysql_user module ansible doc

[Andersson007]

@emper0r Ok, will do, thanks Would be good if you create a new issue for that, thanks!

[itcultus]

I don't want to open a new ticket for this, but I experience the same problem when I use the PyMySQL (RHEL 8.x) and the mysql_query module. Should I raise a new issue for it? I suppose the issue is exactly the same.

[Andersson007]

@itcultus thanks for the feedback! I think no additional issue is needed

[itcultus]

@Andersson007 Oups! I created one, so, please feel free to close it. BTW, if you have something ready and you would like to test it, please ping me I will have some free time this weekend.

[Andersson007]

@itcultus ah, no problem, let's keep both the issues open:) thank you