Handling ProxySQL (>= 1.4.8) with Ansible will result in an error

_Ansible_ProxySQL_Causes_Error.md

(2019/11/22) Another Error on ProxySQL 2.0.8 and Ansible 2.9.1

in check_user_privs\r\nTypeError: tuple indices must be integers or slices, not str\r\n",

See here: https://gist.github.com/shichi-at-nttr/ee8305dee9a791b5827526fbca53f9b9

Note: NOT Resolved

This problrem is resolved at Ansible 2.7.0. But recurrenced at Ansible 2.7.2 in another reason. ansible/ansible#47809

This will resolve at Ansible 2.8

Problems

Use proxysql_mysql_users module in ansible-playbook as such;

- name: create ProxySQL user
  proxysql_mysql_users:
    login_user: "admin"
    login_password: "adminpassword"
    username: "usernamestring"
    password: "passwordstring"
    state: present
    load_to_runtime: true
    save_to_disk: true

Causes error below.

unable to modify user.. (1045, 'unrecognized token: "\'\n                 AND backend = 1\n                 AND frontend = 1"')

Detail Conditions

• Ansible 2.6.0
• ProxySQL >= 1.4.8
  • no problem on 1.4.7
• Python 3 on target server

Causes

In MySQLdb module, query parameters has broken.
/usr/lib/python3/dist-packages/MySQLdb/cursors.py Line: 178 or so,
in definition of def execute(self, query, args=None):

        if args is not None:
            if isinstance(args, dict):
                args = dict((key, db.literal(item)) for key, item in args.items())
            else:
                args = tuple(map(db.literal, args))   <-------------- BROKEN at HERE
            if not PY2 and isinstance(query, bytes):
                query = query.decode(db.unicode_literal.charset)
            query = query % args

args are
BEFORE:

 ['usernamestring', True, True, 'passwordstring']

AFTER:

 ("'", '1', '1', "'")

Further chasing

/usr/lib/python3/dist-packages/MySQLdb/connections.py Line: 300 or so,
In definition of def literal(self, o):

    def literal(self, o):
         :
        s = self.escape(o, self.encoders)   <-------------- BROKEN at HERE
         :
        if not PY2 and isinstance(s, bytes):
            return s.decode('ascii', 'surrogateescape')
        return s

Fixture

        s = self.escape(o)

Ansible Playbook

- name: change MySQLdb/connections.py (1)
  replace:
    dest: /usr/lib/python3/dist-packages/MySQLdb/connections.py
    regexp: '^(\s*s = self.escape)\(o, self.encoders\)$'
    replace:  '\1(o)'

- name: create ProxySQL user
  proxysql_mysql_users:
    login_user: "admin"
    login_password: "adminpassword"
    username: "usernamestring"
    password: "passwordstring"
    state: present
    load_to_runtime: true
    save_to_disk: true

- name: change MySQLdb/connections.py (2)
  replace:
    dest: /usr/lib/python3/dist-packages/MySQLdb/connections.py
    regexp: '^(\s*s = self.escape)\(o\)$'
    replace:  '\1(o, self.encoders)'

@dyipon
I have no problem on Ansible 2.9.6 (homebrew 2.9.6_3) with ProxySQL 2.0.10.
As well on Ansible 2.9.7 with ProxySQL 2.0.10.