Fix "unable to connect to database" using community.mysql.mysql_db in Ansible when connecting to MariaDB.

Intro

When using the module community.mysql.mysql_db in Ansible and setting up a MariaDB database on Ubuntu 22.04 LTS, I got an error:

1

fatal: [DBHOST]: 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: (1698, \"Access denied for user 'root'@'localhost'\")"}."

This occurs because by default in Ubuntu, the root user has it’s authentication_string set to invalid, this can be visualized by issuing the SQL command: SELECT user, authentication_string FROM mysql.user;

If you login interactively to the server, you can just run mysql as root and you will be able to access the database. This is because unix_socket authentication is used for access instead of a password.

Fix

To fix this, we will have to specify that Ansible should use unix socket authentication instead of the default of username and password.

1
2
3
4
5

- name: Example database creation
  community.mysql.mysql_db:
    login_unix_socket: /var/run/mysqld/mysqld.sock
    name: example
    state: present

The important bit here is login_unix_socket, the path might vary depending on distribution, /var/run/mysqld/mysqld.sock is the default in Ubuntu 22.04.

Relevant parameter documentation: https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_db_module.html#parameter-login_unix_socket