Failed to set up a read-only mode using community.mysql.mysql_variables

[Akasurde]

Summary

I have tried to add a task that will set the read only mode for replica server, and remove it from source server:

`- name: MySQL replication user
community.mysql.mysql_user:
name: replication
password: "{{ mysql_replication_password }}"
login_unix_socket: /var/run/mysqld/mysqld.sock
host: "%"
priv: '.:REPLICATION SLAVE'

• name: Set up read only mode
  community.mysql.mysql_variables:
  login_user: replication
  login_password: "{{ mysql_replication_password }}"
  login_unix_socket: /var/run/mysqld/mysqld.sock
  variable: read_only
  value: "{{ 'OFF' if inventory_hostname == mysql_host else 'ON' }}"
  mode: persist `

  But when I am trying to run a playbook, I have got the following error:

  TASK [mysql : Set up read only mode] *************************************************************************** fatal: [bobul-1]: FAILED! => {"changed": false, "msg": "unable to find /root/.my.cnf. Exception message: (1044, \"Access denied for user 'replication'@'%' to database 'mysql'\")"} fatal: [bobul-2]: FAILED! => {"changed": false, "msg": "unable to find /root/.my.cnf. Exception message: (1044, \"Access denied for user 'replication'@'%' to database 'mysql'\")"}

What's incorrect in my code? Feel so stucked...

Issue Type

Documentation Report

Component Name

mysql/tasks/main.yaml

Ansible Version

ansible [core 2.13.11]

Configuration

DEFAULT_BECOME(/home/pns/ica0002/ansible.cfg) = True
DEFAULT_HOST_LIST(/home/pns/ica0002/ansible.cfg) = ['/home/pns/ica0002/hosts']
DEFAULT_VAULT_PASSWORD_FILE(/home/pns/ica0002/ansible.cfg) = /home/pns/.ansible/vault_password
HOST_KEY_CHECKING(/home/pns/ica0002/ansible.cfg) = False

CONNECTION:
==========

paramiko_ssh:
____________
host_key_checking(/home/pns/ica0002/ansible.cfg) = False

ssh:
___
host_key_checking(/home/pns/ica0002/ansible.cfg) = False

OS / Environment

Ubuntu

Additional Information

I think it can help other users

Code of Conduct

• I agree to follow the Ansible Code of Conduct

[Akasurde]

cc @bobul

[laurent-indermuehle]

@Akasurde I'm not sure I would use the replication user for that. Only root should have the right to change the read_only status.
The error message says "Access denied ... to database 'mysql'" which seems to mean that the "replication slave" privilege on *.* (Git Hub swallowed your stars I think) is not enough.

I'm pretty sure it's only a permission issue and not a problem with community.mysql so I'll close your issue but feel free to ping me to reopen it if that's not the case.