Can't create a mysql user with version 3.8.0

[sczelo]

SUMMARY

Can't create a mysql user in AWS RDS database with version 3.8.0
It works with version 3.7.2

ISSUE TYPE

• Bug Report

COMPONENT NAME

ANSIBLE VERSION

ansible [core 2.15.6]
  config file = None
  configured module search path = ['/Users/user/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /opt/homebrew/Cellar/ansible/8.6.1/libexec/lib/python3.12/site-packages/ansible
  ansible collection location = /Users/user/.ansible/collections:/usr/share/ansible/collections
  executable location = /opt/homebrew/bin/ansible
  python version = 3.12.0 (main, Oct  2 2023, 12:03:24) [Clang 15.0.0 (clang-1500.0.40.1)] (/opt/homebrew/Cellar/ansible/8.6.1/libexec/bin/python)
  jinja version = 3.1.2
  libyaml = True

COLLECTION VERSION

# /opt/homebrew/Cellar/ansible/8.6.1/libexec/lib/python3.12/site-packages/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.8.0

CONFIGURATION

CONFIG_FILE() = None
PAGER(env: PAGER) = less

OS / ENVIRONMENT

AWS RDS Global Database

STEPS TO REPRODUCE

- name: Create SQL users
  community.mysql.mysql_user:
    check_hostname: yes
    name: "sqlreader"
    priv: "sqlreader.*:ALL,GRANT"
    host: "%"
    password: "MjDZlLibMCnpAON2044fPPVCEdTK3KTo"
    login_host: "<AWS RDS URL>"
    login_user: "root"
    login_password: "rootpassword"
    update_password: on_new_username
  retries: 5
  delay: 10
  register: create_db_user
  until: create_db_user is not failed

EXPECTED RESULTS

User created.

ACTUAL RESULTS

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: IndexError: tuple index out of range
failed: [localhost] (item={'name': 'sqlreader', 'password': 'MjDZlLibMCnpAON2044fPPVCEdTK3KTo'}) => changed=false
  ansible_loop_var: item
  attempts: 5
  item:
    name: sqlreader
    password: MjDZlLibMCnpAON2044fPPVCEdTK3KTo
  module_stderr: |-
    Traceback (most recent call last):
      File "/Users/user/.ansible/tmp/ansible-tmp-1699887556.1884391-87712-96203046205881/AnsiballZ_mysql_user.py", line 107, in <module>
        _ansiballz_main()
      File "/Users/user/.ansible/tmp/ansible-tmp-1699887556.1884391-87712-96203046205881/AnsiballZ_mysql_user.py", line 99, in _ansiballz_main
        invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
      File "/Users/user/.ansible/tmp/ansible-tmp-1699887556.1884391-87712-96203046205881/AnsiballZ_mysql_user.py", line 47, in invoke_module
        runpy.run_module(mod_name='ansible_collections.community.mysql.plugins.modules.mysql_user', init_globals=dict(_module_fqn='ansible_collections.community.mysql.plugins.modules.mysql_user', _modlib_path=modlib_path),
      File "<frozen runpy>", line 226, in run_module
      File "<frozen runpy>", line 98, in _run_module_code
      File "<frozen runpy>", line 88, in _run_code
      File "/var/folders/9f/t_6w_hsd0m12_0bpjz1dqsw80000gn/T/ansible_community.mysql.mysql_user_payload_iwjnd0eq/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 553, in <module>
      File "/var/folders/9f/t_6w_hsd0m12_0bpjz1dqsw80000gn/T/ansible_community.mysql.mysql_user_payload_iwjnd0eq/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 528, in main
      File "/var/folders/9f/t_6w_hsd0m12_0bpjz1dqsw80000gn/T/ansible_community.mysql.mysql_user_payload_iwjnd0eq/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/user.py", line 169, in user_add
      File "/var/folders/9f/t_6w_hsd0m12_0bpjz1dqsw80000gn/T/ansible_community.mysql.mysql_user_payload_iwjnd0eq/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/user.py", line 144, in get_existing_authentication
    IndexError: tuple index out of range
  module_stdout: ''
  msg: |-
    MODULE FAILURE
    See stdout/stderr for the exact error
  rc: 1

[markuman]

I am not exactly sure what the issue is.

We're using AWS RDS mariadb and it works. What RDS provider and version are you exactly using?

Furthermore, you cannot set ALL privileges on RDS - at least on mariadb. I guess it's also valid for mysql and aurora.

[sczelo]

AWS RDS Aurora MySQL
Engine: Aurora MySQL
Engine version: 8.0.mysql_aurora.3.04.0
Instance type: Serverless v2

If I set update_password to on_create or always there is no error.
If I set update_password to on_new_username there is error, because the get_existing_authentication section called.

community.mysql/plugins/modules/mysql_user.py

Line 527 in 8dfab12

reuse_existing_password = update_password == 'on_new_username'

community.mysql/plugins/module_utils/user.py

Lines 168 to 169 in 8dfab12

	if reuse_existing_password:
	existing_auth = get_existing_authentication(cursor, user, host)

We looked the code changes between 3.7.2 and 3.8.0, and we think the error caused somehow by the change of get_existing_authentication section.
feat[mysql_info]: add 'users_info' filter

[laurent-indermuehle]

I'm sorry if I introduced a regression. But it could be because latest MySQL (8.1 or 8.2 I think) deprecates mysql_native_password. If you use password I think it is what will be used.
Have you tried with this instead?

plugin: caching_sha2_password
plugin_hash_string: 'yourpassword-and-good-luck-with-the-non-ascii-characters'

or maybe it's plugin_auth_string in that case, I'm not sure now.