mysql_db: ERROR 1698 (28000): Access denied for user 'root'@'localhost' with mariadb-10.6

[asarubbo]

Summary

mysql_db fails to import a dump with mariadb-10.6 on CentOS 7

Issue Type

Bug Report

Component Name

mysql_db

Ansible Version

$ ansible --version
ansible [core 2.12.4]
  config file = /home/ago/.ansible.cfg
  configured module search path = ['/home/ago/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.8/site-packages/ansible
  ansible collection location = /home/ago/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/lib/python-exec/python3.8/ansible
  python version = 3.8.13 (default, Apr  7 2022, 10:43:15) [GCC 10.3.0]
  jinja version = 3.1.1
  libyaml = True

Configuration

$ ansible-config dump --only-changed
DEFAULT_FORKS(/home/ago/.ansible.cfg) = 30
DEFAULT_HOST_LIST(/home/ago/.ansible.cfg) = ['/home/ago/git/ansible/common/hosts']
DEFAULT_REMOTE_USER(/home/ago/.ansible.cfg) = root
DEPRECATION_WARNINGS(/home/ago/.ansible.cfg) = False
HOST_KEY_CHECKING(/home/ago/.ansible.cfg) = False
INTERPRETER_PYTHON(/home/ago/.ansible.cfg) = auto_legacy_silent

OS / Environment

target: CentOS 7

Steps to Reproduce

    - name: Add MariaDB YUM repository
      yum_repository:
        name: mariadb
        description: MariaDB
        baseurl: http://yum.mariadb.org/10.6/centos7-amd64
        gpgcheck: yes
        gpgkey: https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
        async: no

    - name: Load a dump
      mysql_db:
        name: database
        state: import
        target: dump.sql

It only fails with the new version of mariadb. With mariadb provided in the default repo, it succeeds.

Both output are: 'rpm -qa | grep -i mariadb' So, working versions:

mariadb-libs-5.5.68-1.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64

Broken versions:

MariaDB-server-10.6.7-1.el7.centos.x86_64
MariaDB-compat-10.6.7-1.el7.centos.x86_64
MariaDB-client-10.6.7-1.el7.centos.x86_64
MariaDB-common-10.6.7-1.el7.centos.x86_64

Expected Results

Loaded a dump successfully

Actual Results

fatal: []: FAILED! => {"changed": false, "msg": "WARNING: Forcing protocol to  TCP  due to option specification. Please explicitly state intended protocol.\nERROR 1698 (28000): Access denied for user 'root'@'localhost'\n"}

[Andersson007]

@asarubbo hello and welcome to the project! Thank you for reporting the issue, i hope someone will make time to take a closer look at this ASAP. Two things:

1. Have you tried this with an older version of MariaDB? I'm trying to understand if it's something version dependent. We test against 10.5 and it works there.
2. Have you tried to run other modules?
3. Can you connect via MySQL CLI directly providing no credentials (as i see in your example)? If you can't, please read the MariaDB doc about passing conn options. After you manage to connect via the mysql utility, you can use those parameters with the modules.
4. Could you please run the playbook with -vvv and provide (only) the traceback?

[asarubbo]

1. Have you tried this with an older version of MariaDB? I'm trying to understand if it's something version dependent. We test against 10.5 and it works there.

Yes, I tried 5.5 that is in the default CentOS repo. I didn't try other versions from mariadb repo

2. Have you tried to run other modules?

For now, I'm loading the dump with the shell module with: mysql $DB < $FILE

3. Can you connect via MySQL CLI directly providing no credentials (as i see in your example)? If you can't, please read the

MariaDB doc about passing conn options. After you manage to connect via the mysql utility, you can use those parameters with the modules. Yes I can, the default installation comes without root password

4. Could you please run the playbook with -vvv and provide (only) the traceback?

Of course, I will..

[Andersson007]

Yes, I tried 5.5 that is in the default CentOS repo. I didn't try other versions from mariadb repo

Did it work well?

For now, I'm loading the dump with the shell module with: mysql $DB < $FILE

Could you please try, say, the mysql_info module and tell us the result?

Of course, I will..

Thanks!

[asarubbo]

Did it work well?

yes it works.

4. Could you please run the playbook with -vvv and provide (only) the traceback?

I'm not getting a stacktrace for this issue.

[Andersson007]

Did it work well?

yes it works.

4. Could you please run the playbook with -vvv and provide (only) the traceback?

I'm not getting a stacktrace for this issue.

OK, thank you for the information! So i'll put the help_wanted label, hope someone can take a deeper look.

I guess we should start with replacing 10.5 with 10.6 in our test matrix.

@asarubbo would you like to contribute? I'd be happy to help if there are any questions. In this simple case you can just edit the file on its page but we also have the Quick-start guide that shows how to do it manually.

[asarubbo]

I can confirm that it works as expected with 10.5, in the same way I tried with 10.6

[Andersson007]

I can confirm that it works as expected with 10.5, in the same way I tried with 10.6

OK, thanks for the info!

[Andersson007]

@asarubbo as 10.6.* is not available in the MariaDB tar archive we use in our CI, i used 10.8.3 and everything is green (see the PR). If you try 10.8.3. with configuration that works with 10.5 and fails with 10.6 and give feedback, it would be faster then me trying to figure out how to change our CI.. If it's not time/effort consuming for you, it would be much appreciated.

UPDATE: i'm trying to understand if it's a consequence of some nuances in the local environment or an issue of release 10.6)

[asarubbo]

Latest working release is 10.5 for me

10.6 - 10.7 - 10.8 fails in the same way

[Andersson007]

@asarubbo OK, thanks for the info! If you run another module, for exapme, mysql_info, does it also fail? I asked this earlier but we missed it somehow.

[asarubbo]

I'm attaching a bit of POC for commodity:

---
- hosts: mariadb

  tasks:
    - name: Copy schema.sql
      copy:
        src: ./schema.sql
        dest: /root/schema.sql

    - name: Add MariaDB YUM repository
      yum_repository:
        name: mariadb
        description: MariaDB
        baseurl: http://yum.mariadb.org/10.7/centos7-amd64
        gpgcheck: yes
        gpgkey: https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
        async: no

    - name: Install needed packages
      yum:
        state: present
        name: [MariaDB-server, MariaDB-client, MySQL-python]

    - name: Start mariadb
      service:
        name: mariadb
        enabled: yes
        state: started

    - name: Create mysql database (db)
      mysql_db:
        name: db
        state: present

    - name: Create mysql user (user)
      mysql_user:
        name: user
        password: user
        state: present

    - name: Import the schema
      mysql_db:
        name: db
        state: import
        target: "/root/schema.sql"

schema.sql can be something like:

SELECT 1

You can test multiple mariadb versions by changing the version in the yum_repository task.

[asarubbo]

@asarubbo OK, thanks for the info! If you run another module, for exapme, mysql_info, does it also fail? I asked this earlier but we missed it somehow.

I tried something like this and works for me (10.6 - 10.7 - 10.8):

    - name: Collect all possible information using passwordless root access
      mysql_info:
        login_user: root
      register: mysqlinfo

    - name: Print mysqlinfo
      debug:
        msg: "{{ mysqlinfo }}"

[Andersson007]

@asarubbo OK, thanks for the info! This is important. Thanks for the collaboration! Could you please try to add check_implicit_admin: yes to you db import task and run again?

[asarubbo]

@asarubbo OK, thanks for the info! This is important. Thanks for the collaboration! Could you please try to add check_implicit_admin: yes to you db import task and run again?

fatal: [X.X.X.X]: FAILED! => {"changed": false, "msg": ""}

[Andersson007]

@asarubbo OK, thanks for the info! This is important. Thanks for the collaboration! Could you please try to add check_implicit_admin: yes to you db import task and run again?

fatal: [X.X.X.X]: FAILED! => {"changed": false, "msg": ""}

thanks, i'll try to make time to take a deeper look later. Feels like something incompatible was introduced in MariaDB 10.6+ to the mysql utility (which is used under the hood for dump import/export feature; other modules use one of python connectors, so that's why they work well)

[hubiongithub]

Hello I'm not sure if I stumbled over this exact problem some time ago, but I made an extra tasks in my playbook for mariadb I added login_unix_socket with the configured socket of the database (may be different depending on OS ...) (I use Ubuntu)

mysql_db:
        state: import
        name: db1
        login_unix_socket: "/run/mysqld/mysqld.sock"
        target: "/path/to/file.sql"

for mysql and mariadb older versions I did not need that option. This seams to match the error message " Please explicitly state intended protocol." as this option forces to use the socket.

I did not dig deeper into why this is the case as I had a workaround.

[Andersson007]

@hubiongithub thanks for the feedback! @asarubbo if you try and confirm the solution hubiongithub came up with works in your case, it would be much appreciated

[Andersson007]

@asarubbo ^

[Andersson007]

I'll close the issue. If there's feedback, we can reopen it. I've just added 10.8.3 to our CI. Thanks everyone!

[asarubbo]

@asarubbo if you try and confirm the solution hubiongithub came up with works in your case, it would be much appreciated

Sorry for the delay, it works for me ;)

Do you think is useful add it to the default example in the documentation?

[Andersson007]

@asarubbo no problem:) I'm happy with any doc improvements, would you like to submit a PR? If you're happy with it but don't know how to do it, let me know.

[eRadical]

As it is quite cumbersome to add login_unix_socket: "/run/mysqld/mysqld.sock" to every module call I added socket in .my.cnf:

# grep socket /root/.my.cnf 
socket=/var/lib/mysql/mysql.sock

This way you don't need to specify at every module call.

[Andersson007]

As it is quite cumbersome to add login_unix_socket: "/run/mysqld/mysqld.sock" to every module call I added socket in .my.cnf:

# grep socket /root/.my.cnf 
socket=/var/lib/mysql/mysql.sock

This way you don't need to specify at every module call.

@eRadical hello, thanks for the hint, would you like to add a short note to the login_unix_socket option's description in the doc file? Something like

- Alternatively, you can specify the socket path using the `socket` option in your MySQL config file, for example C(socket=/var/lib/mysql/mysql.sock).

What do you think? If you have no time, please let me know (you can use just the edit button in that file to create a PR)

[laurent-indermuehle]

I just spend half a day on this issue using c.mysql 3.5.1 I upgraded my first server from 10.4 to 10.6.11 and now I get this error when running a playbook to do a backup:

WARNING: Forcing protocol to  TCP  due to option specification. Please explicitly state intended protocol.
mysqldump: Got error: 1698: "Access denied for user 'mysql'@'localhost'" when trying to connect

The default file for the mysql user is there though:

[client]
socket=/var/lib/mysql/mysql.sock

I use this playbook on MySQL 5.7 and MariaDB 10.4, both works like a charm. But with MariaDB 10.6, the only way to use mysql_db plugin is by adding the option : login_unix_socket: /var/lib/mysql/mysql.sock.

I leave this comment here for later because now I'm too tired to wrap my head around all the facts:

• We tested this before
• We wrote a full PR to add comments but they didn't help me. So maybe we must revert that
• The changelog for the 10.6 release doesn't show anything that could explain this behavior
• Why 10.8 works again?
• Why 10.6, which is a LTS version is not available for our tests? Is it a dbdeployer issue? If yes, why don't we use container images for our tests, like official MySQL and MariaDB images from docker hub? It will be faster than installing every engine at every tests no?

[Andersson007]

@laurent-indermuehle any ways to speed up the tests would be much appreciated, thanks for investigating!

[eRadical]

What grants does 'mysql'@'localhost' have?

I get:

GRANT ALL PRIVILEGES 
ON *.* 
TO `mysql`@`localhost` 
IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket 
WITH GRANT OPTION

[laurent-indermuehle]

I've:

Grants for mysql@localhost
GRANT ALL PRIVILEGES ON *.* TO `mysql`@`localhost` IDENTIFIED VIA unix_socket WITH GRANT OPTION

[eRadical]

I just ran against a suite of MariaDB Galera Clusters (on first node) and it works as expected.

The difference is that I'm running under root not under mysql user.

[[email protected] ~]# grep -v password /root/.my.cnf
[client]
user=root
socket=/var/lib/mysql/mysql.sock

And the playbook does not have login_unix_socket.

It there a way to see relevant parts of the playbook? Asking this because the error is from mysqldump but you're mentioning mysql_db.

[eRadical]

Also by any chance do you have bind-address in the server config?

[laurent-indermuehle]

I tried both on mysql and root. Same result. Here is the relevant tasks from my playbook:

- name: Dump all databases
  become: true
  become_user: mysql
  community.mysql.mysql_db:
    name: all
    state: dump
    target: dump.sql

And no, I don't have bind-address in my server config. But I have a lot in there, you made me thing I should test with a clean install.

Plus, I recall that the issue happen also with other modules like mysql_info. I definitely need to do more tests and write things down. In the meantime, thank you @eRadical for your help!