mysql_query: add the MySQL error code as separate field to the result object

[JaSafieddine]

SUMMARY

Add the MySQL error code as separate field to the result object.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

mysql_query

ADDITIONAL INFORMATION

In case certain errors are expected for a query and are recoverable, it would be nice to have easy access to the error code instead of needing to parse the error code from the error message.

Our particular use case would be handling MySQL components. Installing or uninstalling a MySQL component is not idempotent, so calling the query two times results in an error. E.g.

mysql> INSTALL COMPONENT 'file://component_log_sink_syseventlog';
Query OK, 0 rows affected (0.15 sec)

mysql> INSTALL COMPONENT 'file://component_log_sink_syseventlog';
ERROR 3529 (HY000): Cannot load component from specified URN: 'file://component_log_sink_syseventlog'.

In this case if the query encounters an error with error code 3529, the error can be ignored.

Another simple example:

---
- hosts: localhost
  tasks:
    - name: test mysql query
      community.mysql.mysql_query:
        login_user: root
        login_host: localhost
        login_password: <password>
        query: <invalid query>
      register: query_result
      ignore_errors: True
    - name: show query result
      ansible.builtin.debug:
        var: query_result

current output:

ok: [localhost] => {
    "query_result": {
        "changed": false,
        "failed": true,
        "msg": "Cannot execute SQL 'invalid_query' args [None]: (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<invalid_query>' at line 1\")"
    }
}

possible output:

ok: [localhost] => {
    "query_result": {
        "changed": false,
        "failed": true,
        "error_code": 1064,
        "msg": "Cannot execute SQL 'invalid_query' args [None]: (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<invalid_query>' at line 1\")"
    }
}

[laurent-indermuehle]

I like your idea. Do we know other Ansible modules that have a field to return error codes? Can we use RC for that?

[JaSafieddine]

The common return values specify that some modules can return an 'rc' for return codes: https://docs.ansible.com/ansible/latest/reference_appendices/common_return_values.html#rc

So calling it 'rc' sounds fine to me and in line with other modules.

[Andersson007]

@JaSafieddine thanks for the feature request! sgtm, would you like to submit a PR? Here's the quick start guide if needed.