I really need some help, i’m trying this for almost 3 days, searching on google, chatgpt, forums and anything.

I’m trying to create and environment with Ansible to WordPress with MariaDB, but i’m getting almost the same errors while trying to create the database and to create a new user with privileges on Oracle Linux (9.4) or Fedora (40).

The inventory is:

[fedora]
192.168.1.200 ansible_ssh_user=leo hostname=fedorasrv01 

[oracle]
192.168.1.202 ansible_ssh_user=leo hostname=oraclesrv01

This is the ansible playbook:

- name: Install Mariadb Server - RedHat
  yum:
    name:
      - mariadb-server
      - python3-pip
    state: present
  when: (ansible_facts['os_family'] == "RedHat")

- name: Install PyMySQL on RedHat
  ansible.builtin.pip:
    name: PyMySQL
    state: present
  when: (ansible_facts['os_family'] == "RedHat")

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

- name: Creating a database to be used by WordPress - RedHat
  mysql_db:
    name: "{{ db_wp_name }}"
    state: present
  when: (ansible_facts['os_family'] == "RedHat")

- name: Create user with password, all database privileges - RedHat
  mysql_user:
    name: "{{ db_wp_username }}"
    password: "{{ db_wp_password }}"
    priv: "{{ db_wp_name }}.*:ALL,GRANT"
    state: present
  when: (ansible_facts['os_family'] == "RedHat")

These are the errors:

TASK [mysql : Creating a database to be used by WordPress - RedHat] ***************************************************************************************************************************************************************************
fatal: [192.168.1.202]: FAILED! => {"changed": false, "msg": "unable to find /root/.my.cnf. Exception message: (1698, "Access denied for user 'root'@'localhost'")"}
fatal: [192.168.1.200]: FAILED! => {"changed": false, "msg": "unable to find /root/.my.cnf. Exception message: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")"}

During the seek, I tried to use another yml to help, calling it before the step that broke the pipeline.

- name: Stop MySQL
  service: name=mysqld state=stopped
- name: set environment variables
  shell: systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
- name: Start MySQL
  service: name=mysqld state=started
- name: sql query
  command:  mysql -u root --execute="UPDATE mysql.user SET authentication_string = PASSWORD('{{ mysql_root_password }}') WHERE User = 'root' AND Host = 'localhost';"
- name: sql query flush
  command:  mysql -u root --execute="FLUSH PRIVILEGES"
- name: Stop MySQL
  service: name=mysqld state=stopped
- name: unset environment variables
  shell: systemctl unset-environment MYSQLD_OPTS
- name: Start MySQL
  service: name=mysqld state=started

But after this, the errors now are:

TASK [mysql : sql query] **********************************************************************************************************************************************************************************************************************
fatal: [192.168.1.200]: FAILED! => {"changed": true, "cmd": ["mysql", "-u", "root", "--execute=UPDATE mysql.user SET authentication_string = PASSWORD('Password123') WHERE User = 'root' AND Host = 'localhost';"], "delta": "0:00:00.087770", "end": "2024-07-02 07:06:47.621171", "msg": "non-zero return code", "rc": 1, "start": "2024-07-02 07:06:47.533401", "stderr": "ERROR 1348 (HY000) at line 1: Column 'authentication_string' is not updatable", "stderr_lines": ["ERROR 1348 (HY000) at line 1: Column 'authentication_string' is not updatable"], "stdout": "", "stdout_lines": []}
fatal: [192.168.1.202]: FAILED! => {"changed": true, "cmd": ["mysql", "-u", "root", "--execute=UPDATE mysql.user SET authentication_string = PASSWORD('Password123') WHERE User = 'root' AND Host = 'localhost';"], "delta": "0:00:00.088495", "end": "2024-07-02 07:06:47.591159", "msg": "non-zero return code", "rc": 1, "start": "2024-07-02 07:06:47.502664", "stderr": "ERROR 1348 (HY000) at line 1: Column 'authentication_string' is not updatable", "stderr_lines": ["ERROR 1348 (HY000) at line 1: Column 'authentication_string' is not updatable"], "stdout": "", "stdout_lines": []}