ANSI_QUOTES is get from global variable sql_mode instead of session variable. Therefore, it may use the quotes incorrectly if session sql_mode differs from global one

[SoledaD208]

SUMMARY

ANSI_QUOTES is get from global variable sql_mode instead of session variable. Therefore, it may parse the quotes incorrectly if session sql_mode differs from global one

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user module

ANSIBLE VERSION

any versions

COLLECTION VERSION

any versions

STEPS TO REPRODUCE

create a client config file and set up sql_mode differs from the global variable then run the module. There will be parse error.
sample client config file:
[client]
username=...
password=...
init-command="set sql_mode='ANSI_QUOTES'" # while global sql_mode does not include ANSI_QUOTES

EXPECTED RESULTS

ANSI_QUOTES in session variable should be detect correctly

[SoledaD208]

here's my suggested fix, could anyone please have a look? https://github.com/ansible-collections/community.mysql/compare/main...SoledaD208:community.mysql:check_ansi_quotes_in_session_variable?diff=unified&w=

[laurent-indermuehle]

Hi @SoledaD208, thank you for taking the time to describe the issue.

I'm not sure I understand the use case, though. The get_mode() method is used in mysql_user and mysql_role to manipulate user privileges. What are you trying to quote?

Regarding your suggested fix: To be "Pull Request worthy," it needs a changelog fragment and integration tests. If you open a PR, we (the maintainers) will gladly help you complete these tasks.

[SoledaD208]

hi @laurent-indermuehle sorry for replying late. Incorrect getting the sql_mode ANSI_QUOTES will lead to incorrect parsing here: https://github.com/ansible-collections/community.mysql/blob/7188bea0c827fab6e190984c4d6fd3acb3668e35/plugins/module_utils/user.py#L799C1-L801C71 . If GLOBAL variable have ANSI_QUOTES enabled, but the ansible's session variable does not, ansible --check always report privs changed, and even worse, when applying privs, it breaks with invalid syntax errors for using double quotes (") to quote the table names.
For the reason while sometimes I have to set session's variable sql_mode diffrent from global variable, it's because of such bug: https://bugs.mysql.com/bug.php?id=115953 (I reported to Mysql dev team and they confirmed). So if I want to get "consistent" DB objects' names quotation while not affecting the whole DB, I need to remove ANSI_QUOTES for the ansible's session only.

[SoledaD208]

I just created the PR. It would be great if you can help to review and help with the changelog and integration tests.
The whole point of my idea is the effective sql_mode is the session's variable, not the global one. So we should check the session one instead.
Thank you! Appreciate for the great module and your answers for my issue.

[laurent-indermuehle]

Fixed in #677