Incomprehensible error message

Archives Ansible Project
1

Hi All,

I am attempting to use Ansible to create a MySQL backup user. It should be able to read and lock tables for everything. The step in the playbook is this.

- name: create user - GRANT SELECT, PROCESS, LOCK TABLES ON *.* TO `backup`@`localhost`
   community.mysql.mysql_user:
     user: backup
     host: localhost
     login_password: <redacted>
     priv: '*.*:SELECT, PROCESS, LOCK TABLES'

The error message is:
fatal: [ubuntu2004.hcs]: FAILED! => {"changed": false, "msg": "invalid privileges string: Invalid privileges specified: frozenset({' PROCESS, LOCK TABLES'})"}

I can see nothing wrong with the priv line. Can someone spot it?

Regards

Ian

2

Hi Ian,

There was a similar issue back in 2015/16, see https://github.com/ansible/ansible/issues/29611 and also https://stackoverflow.com/questions/28908155/ansible-mysql-user-priv-invalid-privileges-string-invalid-privileges-specifi amongst quite a few others. They all referenced spaces being in the privilege string.

Are you able to try that without the spaces between privileges?

Cheers
David

3

Hi Ian,

  community.mysql.mysql_user:
    user: backup
    host: localhost
    login_password: <redacted>
    priv: '*.*:SELECT, PROCESS, LOCK TABLES'

The error message is:
fatal: [ubuntu2004.hcs]: FAILED! => {"changed": false, "msg": "invalid
privileges string: Invalid privileges specified: frozenset({' PROCESS, LOCK
TABLES'})"}

I can see nothing wrong with the priv line. Can someone spot it?

I've found the format of the mysql privileges to be extremely picky.
Aside from having to get the syntax correct, also look out for it
seeing privileges in a different order, so applying them again at
every run.

I found that whole-table privileges required no space between, while
column-based privs need spaces between. Example:

priv:
  "somedb.sometable": "SELECT,INSERT,UPDATE,DELETE"
  "somedb.othertable": "SELECT (ID, ACOL, BCOL),\
                       INSERT"

If you run ansible with two or three -v you'll see the MySQL
commands it executes so you can check what it's doing.

Thanks,
Andy