ansible mysql_query connects to mysql read-only, but the server is not in read-only mode

[life5ign]

SUMMARY

ansible mysql_query connects to mysql read-only, but the server is not in read-only mode

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.mysql.mysql_query

ANSIBLE VERSION

ansible [core 2.11.6] 
  config file = /home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg
  configured module search path = ['/home/bryant/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/bryant/.pyenv/versions/3.10.0/envs/ansible-deploy-vst/lib/python3.10/site-packages/ansible
  ansible collection location = /home/bryant/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/bryant/.pyenv/versions/ansible-deploy-vst/bin/ansible
  python version = 3.10.0 (default, Nov  2 2021, 13:53:11) [GCC 9.3.0]
  jinja version = 2.11.2
  libyaml = False

COLLECTION VERSION

# /home/me/.ansible/collections/ansible_collections
Collection      Version
--------------- -------
community.mysql 2.3.1  

# /home/me/.pyenv/versions/3.10.0/envs/ansible-deploy-vst/lib/python3.10/site-packages/ansible_collections
Collection      Version
--------------- -------
community.mysql 2.3.1  

CONFIGURATION

ANY_ERRORS_FATAL(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = True
CACHE_PLUGIN(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = jsonfile
CACHE_PLUGIN_CONNECTION(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = ~/.ansible/fact_cache
CACHE_PLUGIN_TIMEOUT(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = 86400
CALLBACKS_ENABLED(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = ['ansible.posix.timer']
DEFAULT_PRIVATE_KEY_FILE(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = /home/bryant/.ssh/ansible-control-node_rsa
DEFAULT_REMOTE_USER(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = ubuntu
DEFAULT_ROLES_PATH(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = ['/home/bryant/.ansible/roles', '/home/bryant/src/api_guys/ansible-deploy-vst/roles']
DEFAULT_STDOUT_CALLBACK(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = debug
DEFAULT_TIMEOUT(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = 120
DEFAULT_VAULT_IDENTITY(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = prod
DEFAULT_VAULT_PASSWORD_FILE(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = /home/bryant/src/api_guys/ansible-deploy-vst/.ansible_vault_passwords
HOST_KEY_CHECKING(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = False
TRANSFORM_INVALID_GROUP_CHARS(/home/bryant/src/api_guys/ansible-deploy-vst/config/remote_vst_aws_ansible.cfg) = always

OS / ENVIRONMENT

Ubuntu 20.04 (PopOS)

STEPS TO REPRODUCE

Run this query:

    - name: Insert users into stage db
      tags: insert_users
      community.mysql.mysql_query:
        login_host: "{{ mysql_login_host }}"
        login_user: "{{ mysql_login_user }}"
        login_password: "{{ mysql_login_password }}"
        login_db: db_name
        query: >
          INSERT INTO users(account,email,first,id,last,password,status,user_login)
          VALUES ({{ item.account }},'{{ item.email }}','{{ item.first }}',{{ item.id }},'{{ item.last }}','{{ item.password }}',{{ item.status }},'{{ item.user_login }}')
      loop: "{{ users_to_migrate }}"

EXPECTED RESULTS

My server is not in read-only mode:

mysql> SET GLOBAL read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

ACTUAL RESULTS

Playbook error:

The full traceback is:
  File "/tmp/ansible_community.mysql.mysql_query_payload_pqepnc0h/ansible_community.mysql.mysql_query_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_query.py", line 201, in main
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)

........snip.....

Cannot execute SQL 'INSERT INTO users(account,email,first,id,last,password,status,user_login) VALUES (...snip....)                                                                                                
' args [None]: (1290, 'The MySQL server is running with the --read-only option so it cannot execute this statement')  

[zeitounator]

Note: this is a add-on to the comments already given on the following StackOverflow question

---

What does the following task returns if you add it right before the one that fails ?

- name: check read only status
   community.mysql.mysql_query:
     login_host: "{{ mysql_login_host }}"
     login_user: "{{ mysql_login_user }}"
     login_password: "{{ mysql_login_password }}"
     query: >
       SHOW GLOBAL VARIABLES LIKE 'read_only'

I could be wrong and will remove this message as soon as you prove me I am, but I'll very confidently bet that you will get read_only = on

This basically has nothing to do with the community..mysql.mysql module nor the pymysql python module used underneath. The error you get is directly spitted out of your MySQL server which is telling you it is in read only mode. You can follow the different calls in your stack trace which will lead you to this function in pymysql where you will understand the library is giving you an info about the error that was fired right from the server.

You now have to understand why you think the server is not in read only mode while it is clearly telling you the exact opposite (and sorry, in such cases human rarely wins against the computer).

[life5ign]

Thanks for the input: here is the output:

TASK [check read only status] ***********************************************************************************************************************************************
ok: [server] => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python3"
    },
    "changed": false,
    "executed_queries": [
        "SHOW GLOBAL VARIABLES LIKE 'read_only'\n"
    ],
    "query_result": [
        [
            {
                "Value": "OFF",
                "Variable_name": "read_only"
            }
        ]
    ],
    "rowcount": [
        1
    ]
}

So it seems read_only is off, when queried by ansible as well.

[zeitounator]

And the next query still reports that read_only is on when fired in the same playbook ? If yes, what happens if you play the same query manually on the server ?

[life5ign]

Yes, it still reports read_only. I just tried a manual test, but I'm inexperienced with mysql:

mysql> INSERT INTO users(account,email,first,id,last,password,status,user_login) VALUES (1111,'test','test',1,'test','test',1,'test');
ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'

[zeitounator]

This error has nothing to do with the previous you reported: it refuses the query because id=1 which is your table primary key already exists. What is the exact error you are getting then: read-only is on or duplicate key ?

[life5ign]

OK, yes; successfully made an insert into the table while not attempting to duplicate the table's primary key, manually:

INSERT INTO users(account,email,first,id,last,password,status,user_login) VALUES (20452,'test','test',11111,'test','test',1,'test');

Then deleted it:

DELETE FROM vst_master.users WHERE id = 11111;

Then tried with ansible, and still got the read_only error:

    - name: Static insert users into stage db
      tags: static_insert_users
      community.mysql.mysql_query:
        login_host: "{{ mysql_login_host }}"
        login_user: "{{ mysql_login_user }}"
        login_password: "{{ mysql_login_password }}"
        login_db: vst_master
        query: >
          INSERT INTO users(account,email,first,id,last,password,status,user_login)
          VALUES (20452,'test','test',11111,'test','test',1,'test');

MSG:                                                                                                                                                                         
                                                                                                                                                                             
Cannot execute SQL 'INSERT INTO users(account,email,first,id,last,password,status,user_login) VALUES (20452,'test','test',11111,'test','test',1,'test');                     
' args [None]: (1290, 'The MySQL server is running with the --read-only option so it cannot execute this statement')        

[Andersson007]

@life5ign hi, thanks for the report. I don't think this related to mysql_query as it just passes queries to a mysql python driver and returns the result. We have multiple places in integration tests where the module inserts data and there's no problem with this. If there's any error occurs on the DB's side, the module returns the error, like in this case. So i would continue troubleshooting on the server's side.