mysql_user broken in 2.7.1 when using /root/.my.cnf

[esamattis]

SUMMARY

When upgrading from 2.7.0 to 2.7.1 we started seeing this error from mysql_user module

unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")

The task is following:

# Write password to root home as readable only by root so future mysql
# operations can function.
- name: Write root login credentials
  copy:
      dest: /root/.my.cnf
      owner: root
      group: root
      mode: 0600
      content: |
          # {{ ansible_note }}
          [client]
          user=root
          password={{ db_root_password }}

# By default the root user has no password. Set one.
- name: Set root user password
  mysql_user:
      host: "{{ item }}"
      name: root
      password: "{{ db_root_password }}"
      # Login without credentials
      check_implicit_admin: yes
      state: present
  with_items:
      - localhost
      - "{{ ansible_hostname }}"
      - 127.0.0.1
      - ::1

The latter one fails.

Ansible 2.7.0 works fine.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.7.1
  config file = None
  configured module search path = ['/Users/esamatti/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.7/site-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.7.0 (default, Oct  2 2018, 09:18:58) [Clang 10.0.0 (clang-1000.11.45.2)]

CONFIGURATION

ANSIBLE_PIPELINING(/Users/epeli/code/playbooks/ansible.cfg) = True
ANSIBLE_SSH_ARGS(/Users/epeli/code/playbooks/ansible.cfg) = -o ForwardAgent=yes
DEFAULT_FORKS(/Users/epeli/code/playbooks/ansible.cfg) = 100
DEFAULT_HOST_LIST(/Users/epeli/code/playbooks/ansible.cfg) = ['/Users/epeli/code/playbooks/tools/dynamic-inventory']
DEFAULT_REMOTE_USER(/Users/epeli/code/playbooks/ansible.cfg) = root
DEFAULT_SUDO_FLAGS(/Users/epeli/code/playbooks/ansible.cfg) = -HE
DEFAULT_VAULT_PASSWORD_FILE(env: ANSIBLE_VAULT_PASSWORD_FILE) = /Users/epeli/.ansible-vault-password

OS / ENVIRONMENT

macOS Mojave version 10.14 (18A391)

And Ubuntu 18.04 server

STEPS TO REPRODUCE

See SUMMARY

EXPECTED RESULTS

ACTUAL RESULTS

TASK [mariadb : Set root user password] ********************************************************************************************************************************************************************************
failed: [1.2.3.4] (item=localhost) => {"changed": false, "item": "localhost", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}
failed: [1.2.3.4] (item=customer) => {"changed": false, "item": "customer", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}
failed: [1.2.3.4] (item=127.0.0.1) => {"changed": false, "item": "127.0.0.1", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}
failed: [1.2.3.4] (item=::1) => {"changed": false, "item": "::1", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}

[ansibot]

Hi @epeli, thank you for submitting this issue!

click here for bot help

[ansibot]

Files identified in the description:

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

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

click here for bot help

[gundalow]

@epeli Hi, does #47809 fix this for you, could you please add comments on the PR

[timorunge]

@timorunge You see, adding different mysql users was possible without this patch...
Yes, my issue does not related to this PR, but it does to #47736, so I continue to talk there. And I can't provide Dockerfile, sorry.

Have you tried the same already in a different order? Meaning basically: First logging in without any password, setting the root password and afterwards generating the .my.cnf?

Which python DB connector are you using these days? MySQLdb or PyMySQL?

[ansibot]

cc @Jmainguy
click here for bot help

[bchanan03]

Hi All,
Is it possible am seeing same issue with Ansible 2.7.5? with mysql_db

ansible 2.7.5
  config file = None
  configured module search path = [u'/home/vagrant/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python2.7/dist-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 2.7.12 (default, Nov 12 2018, 14:36:49) [GCC 5.4.0 20160609]

Running:

  mysql_db:
    name: "{{ item.db }}"
    encoding: latin1
    state: present
  with_items: '{{ gerrit_db_info }}'
  when: item.type is defined and item.type == "mysql"

Error:

failed: [192.168.202.15] {
    "changed": false,
    "invocation": {
        "module_args": {
            "collation": "",
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encoding": "latin1",
            "ignore_tables": [],
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": null,
            "name": "reviewdb",
            "quick": true,
            "single_transaction": false,
            "ssl_ca": null,
            "ssl_cert": null,
            "ssl_key": null,
            "state": "present",
            "target": null
        }
    },
    "item": {
        "db": "reviewdb",
        "host": "localhost",
        "pass": "xxxxxx",
        "type": "mysql",
        "user": "gerrit"
    },
    "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, u\"Access denied for user 'root'@'localhost'\")"
}

[bchanan03]

Seems same issue with mysql_db - moving back to ansible v2.7.0 resolved the issue.

[felixfontein]

I don't know where that was, but some time (weeks? months?) ago I looked into a similar problem, and the reason was that MySQLdb (which is based on libmysql) was using Unix sockets to connect by default, while PyMySQL was connecting via TCP to localhost by default. Depending on how root access to your MySQL server is configured, this might help you work around this problem.

As I said, I don't really remember where I wrote this before, and I'm not really sure about the details anymore, but maybe this gives one of you having this problem a hint on how to solve it :)

[felixfontein]

Ok, I found it. It was a discussion on #ansible-devel on October 2nd, and the user who had a problem connecting (he first suspected that handling of an empty string password was the problem) finally found out that he needs to use login_unix_socket (because of the different behavior I mentioned above), with it it worked for him. Unfortunately, he didn't create an issue for that.

Anyway, @epeli @anniemelen @bchanan03, can you try out the login_unix_socket option and see if that fixes the problem for you?

[efflamlemaillet]

Hello,
i got the same issue with Centos7, ansible 2.7.6.
specifing the socket=/var/lib/mysql/mysql.sock in ~/.my.cnf
or login_unix_socket: /var/lib/mysql/mysql.sock option in the mysql_db task
is fixing the connection error.

[ansibot]

cc @Xyon @bmalynovytch @dagwieers @michaelcoburn @Oneiroi @tolland
click here for bot help

[ansibot]

cc @bmildren
click here for bot help

[nizarakbarm]

Sorry until now, i think this bug still not being resolved.

[felixfontein]

@nizarakbarm have you tried what I suggested and @efflamlemaillet tried and verified? It would be nice if you could try that, and confirm whether it works (or not!) here.

[pouyana]

Hi @felixfontein, on Ubuntu 18.04 the given work-around by @efflamlemaillet fixes the problem
I think, the following:
https://stackoverflow.com/questions/41846000/mariadb-password-and-unix-socket-authentication-for-root
is also related.

[efflamlemaillet]

Hi,
as said @pouyana, somehow the ansible mysql module using socket connection by default on local server before is now using tcp.

[nizarakbarm]

@felixfontein It work, but i don't like to use ~/.my.cnf

[felixfontein]

@nizarakbarm login_unix_socket: /var/lib/mysql/mysql.sock is something to specify in Ansible. Have you tried that?

[felixfontein]

@efflamlemaillet to be more precise, it is not the Ansible module itself, but the Python MySQL connection library it uses. The old library, MySQLdb (which is based on libmysql), prefers Unix sockets, while the new library, PyMySQL, prefers a TCP connection. If you're explicit in your playbook/role and specify how to connect, that doesn't matter; but if you don't, it depends on which library the module is using. The module tries PyMySQL first, and falls back to MySQLdb if PyMySQL is not available.

[nizarakbarm]

Oke, thanks for your response. @felixfontein From your answer, it can help me to understand the reason of the problem.

[efflamlemaillet]

The whole thing is to know if it should be considered as a bug ?
If so, perhaps the Ansible module could override PyMySQL default behaviour to make sure the module connect the same way (if there is no connection option specified) when the driver installed is MySQLdb or PyMySQL.

[ansibot]

cc @Alexander198961
click here for bot help

[ansibot]

cc @Andersson007
click here for bot help

[ansibot]

cc @kurtdavis
click here for bot help

[etursunbaev]

In my case this workaround also helped
login_unix_socket: /var/run/mysqld/mysqld.sock

ansible 2.8.4
  config file = /etc/ansible/ansible.cfg
  configured module search path = [u'/home/eldar/.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.15+ (default, Nov 27 2018, 23:36:35) [GCC 7.3.0]

- name: Create database
  mysql_db:
    name: dbname
    state: present
    login_unix_socket: /var/run/mysqld/mysqld.sock  

[ansibot]

cc @tomaszkiewicz
click here for bot help

[Callisto88]

Thanks @felixfontein for pointing this out !
Specifying the param login_unix_socket: /var/run/mysqld/mysqld.sock solved the problem !

Had to update every mysql_{user,db} module tasks in files tasks/databases.yml, tasks/secure-installation.yml and tasks/users.yml though

[Andersson007]

i explained this in the doc #66848
it's coming with 2.10

[Andersson007]

close_me