MariaDB – Can’t Set Root Password After Fresh Installation Using Ansible

debianmariadbpassword

Problem:
After a fresh installation of mariadb 10.4 the root password could not be set using ansible.
When installing mariadb from the debian packages by mariadb (not the official debian packages), the password of root is empty and a passwordless login is possible from localhost.

Still, ansible mysql_user threw the following error:
failed: [XXX] (item=localhost) => {"ansible_loop_var": "item", "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'\")"}

Best Answer

Solution:

We need to add login_unix_socket: /var/run/mysqld/mysqld.sock to the task:

- name: update mysql root password for root account
  mysql_user:
    name: root
    login_unix_socket: /var/run/mysqld/mysqld.sock
    host: 'localhost'
    password: '{{ mysql_root_password }}'
    priv: "*.*:ALL,GRANT"
    check_implicit_admin: true

Explanation:
The passwordless login is not possible when connecting through the network (this is what ansible does), only when connecting using the unix socket (the mysql CLI does that when connecting to localhost). So we need to force ansible to connect to the socket.

I hope this post saves somebody the time figuring that out.

Related Question