New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

mysql_user: MySQL 8.0/CentOS 7 - unable to connect to database #41116

Open
dungdm93 opened this Issue Jun 5, 2018 · 5 comments

Comments

Projects
None yet
5 participants
@dungdm93

dungdm93 commented Jun 5, 2018

SUMMARY

I'm install MySQL 8.0 on CentOS, but I can't change root password.

ISSUE TYPE
  • Bug Report
ANSIBLE VERSION
ansible 2.5.4
  config file = /etc/ansible/ansible.cfg
  configured module search path = [u'/home/dungdm93/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python2.7/dist-packages/ansible
  executable location = /usr/bin/ansible
  python version = 2.7.12 (default, Dec  4 2017, 14:50:18) [GCC 5.4.0 20160609]
COMPONENT NAME

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

OS / ENVIRONMENT
16.04.4 LTS (Xenial Xerus)
STEPS TO REPRODUCE

MySQL config:

# {{ ansible_managed }}

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
log-error   = /var/log/mysql/error.log

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

and ansible tasks:

- name: Catch temporary password
  when: ansible_os_family == 'RedHat'  and
        mysql_new_installation is changed and
        mysql_version is version('5.7', '>=')
  block:
  - command: >
      awk -F : '/temporary password/ {gsub(/ /, "", $NF); print $NF}' /var/log/mysql/error.log
    register: temporary_password

  - set_fact:
      mysql_old_password: "{{ temporary_password.stdout }}"

- name: Change MySQL root password
  mysql_user:
    name: root
    password: "{{ mysql_root_password}}"
    check_implicit_admin: true
    login_user:     "root"
    login_password: "{{ mysql_old_password }}"

As you can see, I config socket dir from /var/lib/mysql/mysql.sock (default on CentOS) to /var/run/mysqld/mysqld.sock. But mysql_user don't recognize new location:

TASK [mysql-server : command] ******************************************************************************************************************************************************************************
changed: [srv-centos]

TASK [mysql-server : set_fact] *****************************************************************************************************************************************************************************
ok: [srv-centos]

TASK [mysql-server : Change MySQL root password] ***********************************************************************************************************************************************************
fatal: [srv-centos]: FAILED! => {
  "changed": false, 
  "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. 
        Exception message: (2002, \"Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)\")"
}

Second time, when i specify login_unix_socket: /var/run/mysqld/mysqld.sock on mysql_user, I got following error:

TASK [mysql-server : Change MySQL root password] ***********************************************************************************************************************************************************
fatal: [srv-centos]: FAILED! => {
    "changed": false,
    "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials.
        Exception message: (2059, \"Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory\")"
}
@ansibot

This comment has been minimized.

Contributor

ansibot commented Jun 5, 2018

@dungdm93 Greetings! Thanks for taking the time to open this issue. In order for the community to handle your issue effectively, we need a bit more information.

Here are the items we could not find in your description:

  • component name

Please set the description of this issue with this template:
https://raw.githubusercontent.com/ansible/ansible/devel/.github/ISSUE_TEMPLATE.md

click here for bot help

@ansibot

This comment has been minimized.

Contributor

ansibot commented Jun 5, 2018

@jlozadad

This comment has been minimized.

Contributor

jlozadad commented Jun 7, 2018

can you run with -vvv? also is the process up ?

@dungdm93

This comment has been minimized.

dungdm93 commented Jun 15, 2018

@jlozadad

TASK [mysql-server : Change MySQL root password] ***********************************************************************************************************************************************************
task path: /me/Workspaces/research/machines/mysql/roles/mysql-server/tasks/users.yml:32
Using module file /usr/lib/python2.7/dist-packages/ansible/modules/database/mysql/mysql_user.py
<35.198.253.127> ESTABLISH SSH CONNECTION FOR USER: dungdm93
<35.198.253.127> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=dungdm93 -o ConnectTimeout=10 -o ControlPath=/home/dungdm93/.ansible/cp/155bf867d4 35.198.253.127 '/bin/sh -c '"'"'echo ~dungdm93 && sleep 0'"'"''
<35.198.253.127> (0, '/home/dungdm93\n', '')
<35.198.253.127> ESTABLISH SSH CONNECTION FOR USER: dungdm93
<35.198.253.127> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=dungdm93 -o ConnectTimeout=10 -o ControlPath=/home/dungdm93/.ansible/cp/155bf867d4 35.198.253.127 '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /home/dungdm93/.ansible/tmp/ansible-tmp-1529049278.75-139840470373270 `" && echo ansible-tmp-1529049278.75-139840470373270="` echo /home/dungdm93/.ansible/tmp/ansible-tmp-1529049278.75-139840470373270 `" ) && sleep 0'"'"''
<35.198.253.127> (0, 'ansible-tmp-1529049278.75-139840470373270=/home/dungdm93/.ansible/tmp/ansible-tmp-1529049278.75-139840470373270\n', '')
<35.198.253.127> PUT /home/dungdm93/.ansible/tmp/ansible-local-30399d6y3W0/tmpo_VOiE TO /home/dungdm93/.ansible/tmp/ansible-tmp-1529049278.75-139840470373270/mysql_user.py
<35.198.253.127> SSH: EXEC sftp -b - -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=dungdm93 -o ConnectTimeout=10 -o ControlPath=/home/dungdm93/.ansible/cp/155bf867d4 '[35.198.253.127]'
<35.198.253.127> (0, 'sftp> put /home/dungdm93/.ansible/tmp/ansible-local-30399d6y3W0/tmpo_VOiE /home/dungdm93/.ansible/tmp/ansible-tmp-1529049278.75-139840470373270/mysql_user.py\n', '')
<35.198.253.127> ESTABLISH SSH CONNECTION FOR USER: dungdm93
<35.198.253.127> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=dungdm93 -o ConnectTimeout=10 -o ControlPath=/home/dungdm93/.ansible/cp/155bf867d4 35.198.253.127 '/bin/sh -c '"'"'chmod u+x /home/dungdm93/.ansible/tmp/ansible-tmp-1529049278.75-139840470373270/ /home/dungdm93/.ansible/tmp/ansible-tmp-1529049278.75-139840470373270/mysql_user.py && sleep 0'"'"''
<35.198.253.127> (0, '', '')
<35.198.253.127> ESTABLISH SSH CONNECTION FOR USER: dungdm93
<35.198.253.127> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=dungdm93 -o ConnectTimeout=10 -o ControlPath=/home/dungdm93/.ansible/cp/155bf867d4 -tt 35.198.253.127 '/bin/sh -c '"'"'sudo -H -S -n -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-ponemevplzyduydqfyfonljsbqahxdhy; /usr/bin/python /home/dungdm93/.ansible/tmp/ansible-tmp-1529049278.75-139840470373270/mysql_user.py'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded
<35.198.253.127> (1, '\r\n{"msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (2002, \\"Can\'t connect to local MySQL server through socket \'/var/lib/mysql/mysql.sock\' (2)\\")", "failed": true, "exception": "  File \\"/tmp/ansible_uD17nk/ansible_module_mysql_user.py\\", line 607, in main\\n    connect_timeout=connect_timeout)\\n  File \\"/tmp/ansible_uD17nk/ansible_modlib.zip/ansible/module_utils/mysql.py\\", line 72, in mysql_connect\\n    db_connection = MySQLdb.connect(**config)\\n  File \\"/usr/lib64/python2.7/site-packages/MySQLdb/__init__.py\\", line 81, in Connect\\n    return Connection(*args, **kwargs)\\n  File \\"/usr/lib64/python2.7/site-packages/MySQLdb/connections.py\\", line 193, in __init__\\n    super(Connection, self).__init__(*args, **kwargs2)\\n", "invocation": {"module_args": {"ssl_key": null, "update_password": "always", "login_user": "root", "host_all": false, "priv": null, "state": "present", "sql_log_bin": true, "encrypted": false, "connect_timeout": 30, "config_file": "/root/.my.cnf", "login_host": "localhost", "append_privs": false, "login_unix_socket": null, "host": "localhost", "user": "root", "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "check_implicit_admin": true, "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "login_port": 3306, "ssl_cert": null, "name": "root", "ssl_ca": null}}}\r\n', 'Shared connection to 35.198.253.127 closed.\r\n')
<35.198.253.127> ESTABLISH SSH CONNECTION FOR USER: dungdm93
<35.198.253.127> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=dungdm93 -o ConnectTimeout=10 -o ControlPath=/home/dungdm93/.ansible/cp/155bf867d4 35.198.253.127 '/bin/sh -c '"'"'rm -f -r /home/dungdm93/.ansible/tmp/ansible-tmp-1529049278.75-139840470373270/ > /dev/null 2>&1 && sleep 0'"'"''
<35.198.253.127> (0, '', '')
The full traceback is:
  File "/tmp/ansible_uD17nk/ansible_module_mysql_user.py", line 607, in main
    connect_timeout=connect_timeout)
  File "/tmp/ansible_uD17nk/ansible_modlib.zip/ansible/module_utils/mysql.py", line 72, in mysql_connect
    db_connection = MySQLdb.connect(**config)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)

fatal: [srv-centos]: FAILED! => {
    "changed": false, 
    "invocation": {
        "module_args": {
            "append_privs": false, 
            "check_implicit_admin": true, 
            "config_file": "/root/.my.cnf", 
            "connect_timeout": 30, 
            "encrypted": false, 
            "host": "localhost", 
            "host_all": false, 
            "login_host": "localhost", 
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", 
            "login_port": 3306, 
            "login_unix_socket": null, 
            "login_user": "root", 
            "name": "root", 
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", 
            "priv": null, 
            "sql_log_bin": true, 
            "ssl_ca": null, 
            "ssl_cert": null, 
            "ssl_key": null, 
            "state": "present", 
            "update_password": "always", 
            "user": "root"
        }
    }, 
    "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (2002, \"Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)\")"
}
@seanharmer

This comment has been minimized.

seanharmer commented Jun 30, 2018

Same issue here. MySQL 8 no longer ships with /usr/lib64/mysql/plugin/caching_sha2_password.so.

Temporary work around is to set mysql to use the legacy authentication by putting:

default_authentication_plugin=mysql_native_password

into your my.cnf file. If you have already created the root user and password without this set, you can update the root user to use the legacy password scheme with:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my_root_password_goes_here'

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment