mysql_user: fix compatibility issues with various MySQL/MariaDB versions

[bmalynovytch]

SUMMARY

To handle properly user management, version check needed refactoring, as well as the query used to get existing password hash.

Fixes #44441
Fixes #43361
Fixes #43255
Fixes #42564
Possibly fixes #40091
Possibly fixes #29625
Fixes #29615

ISSUE TYPE

Bugfix Pull Request

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.6.3
  config file = /Users/benjamin/Dev/xxxxxx/xxx-platform/ansible.cfg
  configured module search path = [u'/Users/benjamin/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/var/pyenv/versions/2.7.11/envs/ansible-xxxxxxx/lib/python2.7/site-packages/ansible
  executable location = /usr/local/var/pyenv/versions/ansible-xxxxxx/bin/ansible
  python version = 2.7.11 (default, Mar 31 2016, 13:46:08) [GCC 4.2.1 Compatible Apple LLVM 7.3.0 (clang-703.0.29)]

ADDITIONAL INFORMATION

Module wasn't able to handle properly MariaDB 10.3 as well as edge cases (mixed situations with user table including users with new style and users with old style).
It's now able to:

• better identify "new" vs "old" versions of MySQL, as well as MariaDB
• better handle any column of password hashes storage

The module now handles properly idempotence and was tested successfully against Debian 9.5 with MariaDB 10.3.9.

[ansibot]

cc @Jmainguy
click here for bot help

[bmalynovytch]

... still working on a fix

[bmalynovytch]

@ansibot WIP

[ansibot]

The test ansible-test sanity --test changelog [explain] failed with 1 error:

changelogs/fragments/45355-mysql_user-fix-versions-compatibilities:0:0: extension must be one of: .yml, .yaml

click here for bot help

[felixfontein]

LGTM

I assume you have tested this with newer and older versions of both MariaDB and MySQL :)

[felixfontein]

Why not select both columns in one query (without the LIMIT) and fill colA and colB in module code? This will save one query.

[bmalynovytch]

It's a trick to get an ordered list of valid columns to use in the next queries.
It's a way of getting the right list without using tons of if conditions which may be error prone.

[felixfontein]

This should also work:

            cursor.execute("""
                SELECT COLUMN_NAME FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'user' AND COLUMN_NAME IN ('Password', 'authentication_string')
            """)
            cols = sorted([row[0] for row in cursor.fetchall()])
            colA, colB = cols[0], cols[-1]

(no idea if fetchall() exists and works like this, but I guess there will be some method which fetches more than one row?)

[bmalynovytch]

Yes, probably

[felixfontein]

You can still change that later (once this is merged) if you want.

[bmalynovytch]

LGTM

I assume you have tested this with newer and older versions of both MariaDB and MySQL :)

Yes, definitely, as well as other contributors in various environments.

[gundalow]

Great work everybody on this.
Merged into devel for release in Ansible 2.8

[bmalynovytch]

Finally we get it merged !
Thx everybody !