mysql_db can't authenticate when mysql 8 using mysql_native_password

[kotso]

SUMMARY

mysql_db and mysql_user modules fail to auth with mysql8 when mysql_native_password is set as default authentication plugin.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_db
mysql_user

ANSIBLE VERSION

ansible 2.9.2
  config file = /Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg
  configured module search path = ['/Users/konstantine/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/Cellar/ansible/2.9.2/libexec/lib/python3.7/site-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.7.6 (default, Dec 30 2019, 19:38:26) [Clang 11.0.0 (clang-1100.0.33.16)]

CONFIGURATION

ANSIBLE_SSH_ARGS(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = -F ssh.cfg
COMMAND_WARNINGS(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = True
DEFAULT_MANAGED_STR(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = Ansible Managed - %Y-%m-%d %
DEFAULT_REMOTE_USER(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = ansible
DEFAULT_ROLES_PATH(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = ['/Users/konstantine/Library/
DEFAULT_TIMEOUT(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = 10
DEPRECATION_WARNINGS(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = True
HOST_KEY_CHECKING(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = False
RETRY_FILES_ENABLED(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = False
SYSTEM_WARNINGS(/Users/konstantine/Library/Mobile Documents/com~apple~CloudDocs/Development/Cloud9/IaC/NextGen/infrastucture/ansible.cfg) = True

OS / ENVIRONMENT

MacOS 10.15.4 (19E287)

STEPS TO REPRODUCE

mysql installation with default-authentication-plugin=mysql_native_password
credentials set in /root/.my.cnf

- name: create db
  mysql_db:
    name: test_db

EXPECTED RESULTS

mysql db to be created

ACTUAL RESULTS

<XXX> (0, b'', b"Warning: Permanently added 'XXX,1.2.3.4' (ECDSA) to the list of known hosts.\r\n")
The full traceback is:
WARNING: The below traceback may *not* be related to the actual failure.
  File "/tmp/ansible_mysql_db_payload_gC6WJ4/ansible_mysql_db_payload.zip/ansible/module_utils/mysql.py", line 82, in mysql_connect
    db_connection = mysql_driver.connect(**config)
  File "/usr/lib/python2.7/site-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 325, in __init__
    self.connect()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 599, in connect
    self._request_authentication()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 871, in _request_authentication
    auth_packet = self._process_auth(plugin_name, auth_packet)
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 900, in _process_auth
    return _auth.caching_sha2_password_auth(self, auth_packet)
  File "/usr/lib/python2.7/site-packages/pymysql/_auth.py", line 265, in caching_sha2_password_auth
    pkt = _roundtrip(conn, data)
  File "/usr/lib/python2.7/site-packages/pymysql/_auth.py", line 122, in _roundtrip
    pkt = conn._read_packet()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)

failed: [ams01-gw-web-01.braindept.com] (item=gainwest_prod) => {
    "ansible_loop_var": "item",
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "client_cert": null,
            "client_key": null,
            "collation": "",
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encoding": "",
            "ignore_tables": [],
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": null,
            "name": [
                "gainwest_prod"
            ],
            "quick": true,
            "single_transaction": false,
            "state": "present",
            "target": null
        }
    },
    "item": "gainwest_prod",
    "msg": "unable to connect to database: (1045, u\"Access denied for user 'root'@'localhost' (using password: YES)\")"
}

[ansibot]

Files identified in the description:

• lib/ansible/modules/database/mysql/mysql_db.py
• lib/ansible/modules/database/mysql/mysql_user.py

If these files are incorrect, please update the component name section of the description or use the !component bot command.

click here for bot help

[Akasurde]

Issue migrated to ansible-collections/community.general#266