mysql_query returns changed: true when nothing is executed.

[itcultus]

SUMMARY

I used the mysql_query with some "CREATE TABLE IF NOT EXISTS" statements.
The second time I run the query, it reports "changed" although nothing is executed since the tables are already created. The mysql_query module has the "changed: true" hardcoded in the return values simply by looking if some keywords are present. However, as you can see it's a bit more complex than that.

Let's see this SQL query:

CREATE TABLE IF NOT EXISTS `instructions` (
  `id` int(11) NOT NULL,
  `instructions` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If we execute this statement in the mysql/mariadb CLI client, we will see this:

CREATE TABLE IF NOT EXISTS `instructions` (`id` int(11) NOT NULL, `instructions` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.000 sec)

The warning of course is that the table already exists.

Note (Code 1050): Table 'instructions' already exists

When I execute the statement for a table that doesn't exist, we see this:

CREATE TABLE IF NOT EXISTS `instructions3` (`id` int(11) NOT NULL, `instructions` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.012 sec)

We have no warnings, the query IS OK, so, we can safely assume that the table is created.
I don't know if you have discussed it in the past, but in my opinion the code should be a bit more selective on when to return "Changed" and when not. I classify this as a bug, but obviously if you feel that it's a feature request, I am more than fine with it.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_query

ANSIBLE VERSION

ansible 2.9.27

COLLECTION VERSION

ansible-collection-community-mysql-2.1.0-1.fc34.noarch

OS / ENVIRONMENT

STEPS TO REPRODUCE

- name: "Create the tables of {{ db_name }}"
  mysql_query:
    ca_cert: "{{ db_ca_cert | default(omit) }}"
    client_cert: "{{ db_client_cert | default(omit) }}"
    client_key: "{{ db_client_key | default(omit) }}"
    login_host: "{{ db_host }}"
    login_port: "{{ db_host_port if db_host != 'localhost' else omit }}"
    login_unix_socket: "{{ db_login_socket | default(omit) }}"
    login_user: "{{ db_admin_user }}"
    login_password: "{{ db_admin_pass }}"
    login_db: "{{ db_name }}"
    #query: "{{ lookup('template', 'schema.sql.j2') | replace('\n', '') | trim }}"
    query: "CREATE TABLE IF NOT EXISTS `instructions2` (`id` int(11) NOT NULL, `instructions` text, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
  register: sql_result

EXPECTED RESULTS

Module should return OK when no action is taken from the DB server when we execute DDL statements.

ACTUAL RESULTS

TASK [demo : Create the tables of demo] ***********************************************************************************************************************************
changed: [server.example.com]

TASK [demo : debug] **********************************************************************************************************************************************************
ok: [server.example.com] => 
  sql_result:
    changed: true
    executed_queries:
    - CREATE TABLE IF NOT EXISTS `instructions2` (  `id` int(11) NOT NULL,  `instructions` text,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    failed: false
    query_result:
    - []
    rowcount:
    - 0

[Andersson007]

@itcultus hi, thanks for reporting this! Sorry for the late reply - missed the issue somehow. Would you like to pick it up? If we can catch the warning and parse, we could add a corresponding action, ie. if already exists in warning: changed = False or something

[Andersson007]

@itcultus the PR has just been merged, with PyMySQL it should work correctly, thanks!