mysql_query returns "changed" for queries using "IF EXISTS/IF NOT EXISTS" for all connectors but PyMySQL < 0.10.1

[laurent-indermuehle]

SUMMARY

In the mysql_query plugin, we catch the warnings returned by MySQL/MariaDB when we try to recreate or re-delete a resource. This code worked only for PyMySQL. Mysqlclient never supported that feature. In PyMySQL 0.10.1, so for us between the 0.9.3 and 1.0.2 we supports, this stopped working.

Then now, the only way to get green when the query changes nothing is by using an old version of PyMySQL.

The integration tests are filled with tests for connectors version. This only attest that we acknowledge there is many behavior possible, but the end user of the collection will still need to be careful about what connector and what version is in use.

I'm wondering if we can't replace the code responsible to catch the warning from the connector by a simple query SHOW WARNINGS. If it works, we can have both simpler tests and users happy with consistent returned values.

The part of the code responsible for this is here: https://github.com/ansible-collections/community.mysql/blob/main/plugins/modules/mysql_query.py#L219-L226

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_query

COLLECTION VERSION

3.6.0

[Andersson007]

@laurent-indermuehle thanks for investigating and filing the issue! it sounds like it's worth trying

[laurent-indermuehle]

Thanks to @methane (maintainer of PyMySQL and mysqlclient) for this additional informations:

I don't think there is a robust and simple way to detect changes from arbitrary queries.

PyMySQL (and mysqlclient) stopped emitting Python warning from MySQL warning.
So executing "SHOW WARNINGS" is the only way to get the MySQL warning.

To detect there is a warning, you can:

• connection.warning_count (mysqlclient)
• cursor._result.warning_count (PyMySQL)
• cursor.warning_count (PyMySQL v1.1.0+)

So you can check it before executing SHOW WARNINGS.
That's what old PyMySQL and mysqlclient did.

And to execute SHOW WARNING, you can just:

• connection.show_warnigns() (PyMySQL and mysqlclient)

https://github.com/PyMySQL/mysqlclient/blob/91c04282eac46ce2e5380be58e262d817faab566/src/MySQLdb/connections.py#L313
https://github.com/PyMySQL/PyMySQL/blob/c1d8063759a4a3968b0f7907e098554d9a8ad552/pymysql/connections.py#L490