View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

[a118n]

Hello, Getting an error when running this module. BUT, when running the playbook a second time - there's no error and everything works. Honestly have no idea what's going on. Traceback from ansible below. Rocky Linux 8.4 MariaDB 10.6.4 Ansible 2.11.2

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: TypeError: Value of unknown type: <class 'pymysql.err.OperationalError'>, (1356, "View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them")
fatal: [xxx]: FAILED! => {"changed": false, "module_stderr": "Shared connection to xxx closed.\r\n", "module_stdout": "Traceback (most recent call last):\r\n  File \"/home/ansible/.ansible/tmp/ansible-tmp-1636388973.1593754-189346-237513651887281/AnsiballZ_mysql_secure_installation.py\", line 100, in <module>\r\n    _ansiballz_main()\r\n  File \"/home/ansible/.ansible/tmp/ansible-tmp-1636388973.1593754-189346-237513651887281/AnsiballZ_mysql_secure_installation.py\", line 92, in _ansiballz_main\r\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\r\n  File \"/home/ansible/.ansible/tmp/ansible-tmp-1636388973.1593754-189346-237513651887281/AnsiballZ_mysql_secure_installation.py\", line 41, in invoke_module\r\n    run_name='__main__', alter_sys=True)\r\n  File \"/usr/lib64/python3.6/runpy.py\", line 205, in run_module\r\n    return _run_module_code(code, init_globals, run_name, mod_spec)\r\n  File \"/usr/lib64/python3.6/runpy.py\", line 96, in _run_module_code\r\n    mod_name, mod_spec, pkg_name, script_name)\r\n  File \"/usr/lib64/python3.6/runpy.py\", line 85, in _run_code\r\n    exec(code, run_globals)\r\n  File \"/tmp/ansible_mysql_secure_installation_payload_nmptl5lc/ansible_mysql_secure_installation_payload.zip/ansible/modules/mysql_secure_installation.py\", line 478, in <module>\r\n  File \"/tmp/ansible_mysql_secure_installation_payload_nmptl5lc/ansible_mysql_secure_installation_payload.zip/ansible/modules/mysql_secure_installation.py\", line 474, in main\r\n  File \"/tmp/ansible_mysql_secure_installation_payload_nmptl5lc/ansible_mysql_secure_installation_payload.zip/ansible/module_utils/basic.py\", line 1497, in exit_json\r\n  File \"/tmp/ansible_mysql_secure_installation_payload_nmptl5lc/ansible_mysql_secure_installation_payload.zip/ansible/module_utils/basic.py\", line 1490, in _return_formatted\r\n  File \"/tmp/ansible_mysql_secure_installation_payload_nmptl5lc/ansible_mysql_secure_installation_payload.zip/ansible/module_utils/common/parameters.py\", line 889, in remove_values\r\n  File \"/tmp/ansible_mysql_secure_installation_payload_nmptl5lc/ansible_mysql_secure_installation_payload.zip/ansible/module_utils/common/parameters.py\", line 461, in _remove_values_conditions\r\nTypeError: Value of unknown type: <class 'pymysql.err.OperationalError'>, (1356, \"View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them\")\r\n", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 1}

[eslam-gomaa]

Thanks Daniel,

Will test with the same disto from my side

[eslam-gomaa]

It worked with me normally from the first time 🤔

Rocky Linux release 8.4 (Green Obsidian)
MariaDB 10.3.28
ansible 2.9.27

[a118n]

Could you please check it with the latest MariaDB version from their repository? I used the script to add the repo: https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage/

[eslam-gomaa]

I'm able to reproduce the issue after installing Mariadb 10.6, will check it

[eslam-gomaa]

Daniel,

The issue is fixed. please clone again & it should work normally.

---

The issue was caused by that line

Actually it's my bad, forgot to remove it 😅

The reason of the issue: As of Mariadb v10.4+ we can not use update mysql.user to modify a user

Why it works on the next try ? the function that contains the error line is not triggered on the next tries (because the password is already changed on the first time) & since the password is already modified the function will no be triggered.

[a118n]

Eslam, Thank you for a quick response and a fix. Everything is working now. Closing the issue.