proxysql_query_rules_fast_routing module can not update existed rules

[squirrel532]

SUMMARY

proxysql_query_rules_fast_routing module can not update destination_hostgroup of a rule.

ISSUE TYPE

• Bug Report

COMPONENT NAME

proxysql_query_rules_fast_routing

ANSIBLE VERSION

ansible [core 2.12.5]
  config file = /home/user/Workspace/ansible/ansible.cfg
  configured module search path = ['/home/user/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/user/.pyenv/versions/3.10.1/envs/ansible/lib/python3.10/site-packages/ansible
  ansible collection location = /home/user/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/user/.pyenv/versions/ansible/bin/ansible
  python version = 3.10.1 (main, Jan  5 2022, 13:00:07) [GCC 9.3.0]
  jinja version = 3.0.3
  libyaml = True

COLLECTION VERSION

# /home/user/.pyenv/versions/3.10.1/envs/ansible/lib/python3.10/site-packages/ansible_collections
Collection         Version
------------------ -------
community.proxysql 1.3.2

# /home/user/.ansible/collections/ansible_collections
Collection         Version
------------------ -------
community.proxysql 1.4.0

CONFIGURATION

OS / ENVIRONMENT

Ubuntu 20.04

STEPS TO REPRODUCE

1. Config a proxysql instance with admin credential admin/admin.
2. Run ansible-playbook proxysql.yml

# playbook proxysql.yml
- hosts: all
  vars:
    proxysql_admin_user: admin
    proxysql_admin_password: admin
    proxysql_admin_interface: "127.0.0.1"
    proxysql_admin_port: 6032

  tasks:
    - name: Create fast routing rules
      community.proxysql.proxysql_query_rules_fast_routing:
        # rule args
        username: user
        schemaname: test_database"
        destination_hostgroup: 1
        # auth args
        login_user: "{{ proxysql_admin_user }}"
        login_password: "{{ proxysql_admin_password }}"
        login_host: "{{ proxysql_admin_interface }}"
        login_port: "{{ proxysql_admin_port }}"
        state: present

    - name: Update fast routing rules
      community.proxysql.proxysql_query_rules_fast_routing:
        # rule args
        username: user
        schemaname: test_database"
        destination_hostgroup: 2
        # auth args
        login_user: "{{ proxysql_admin_user }}"
        login_password: "{{ proxysql_admin_password }}"
        login_host: "{{ proxysql_admin_interface }}"
        login_port: "{{ proxysql_admin_port }}"
        state: present

EXPECTED RESULTS

PLAY [proxysql] ***************************************************************************************************

TASK [Gathering Facts] ********************************************************************************************
ok: [proxysql]

TASK [Create fast routing rules] **********************************************************************************
ok: [proxysql]

TASK [Update fast routing rules] **********************************************************************************
changed: [proxysql]

PLAY RECAP ********************************************************************************************************
proxysql                   : ok=2    changed=0    unreachable=0    failed=1    skipped=0    rescued=0    ignored=0 

ACTUAL RESULTS

PLAY [proxysql] ***************************************************************************************************

TASK [Gathering Facts] ********************************************************************************************
ok: [proxysql]

TASK [Create fast routing rules] **********************************************************************************
ok: [proxysql]

TASK [Update fast routing rules] **********************************************************************************
fatal: [proxysql]: FAILED! => {"changed": false, "msg": "unable to modify rule: (1045, 'ProxySQL Admin Error: UNIQUE constraint failed: mysql_query_rules_fast_routing.username, mysql_query_rules_fast_routing.schemaname, mysql_query_rules_fast_routing.flagIN')"}

PLAY RECAP ********************************************************************************************************
proxysql                   : ok=2    changed=0    unreachable=0    failed=1    skipped=0    rescued=0    ignored=0 

[squirrel532]

I found the root cause is at proxysql_query_rules_fast_routing.py#L381. The if statement evaluated to False since query_rule.config_data["flagIN"] is 0.

[markuman]

Nice catch.

Because flatIN has a default value in both (table schema and module parameter), I believe that the check for flagIN can be just left out.

CREATE TABLE mysql_query_rules_fast_routing (
    username VARCHAR NOT NULL,
    schemaname VARCHAR NOT NULL,
    flagIN INT NOT NULL DEFAULT 0,
    destination_hostgroup INT CHECK (destination_hostgroup >= 0) NOT NULL,
    comment VARCHAR NOT NULL,
    PRIMARY KEY (username,
schemaname,
flagIN) )

diff --git a/plugins/modules/proxysql_query_rules_fast_routing.py b/plugins/modules/proxysql_query_rules_fast_routing.py
index 601caf6..07e79b8 100644
--- a/plugins/modules/proxysql_query_rules_fast_routing.py
+++ b/plugins/modules/proxysql_query_rules_fast_routing.py
@@ -379,7 +379,7 @@ def main():
         try:
             if not query_rule.check_rule_cfg_exists(cursor):
                 if query_rule.config_data["username"] and query_rule.config_data["schemaname"] and \
-                   query_rule.config_data["flagIN"] and query_rule.check_rule_pk_exists(cursor):
+                   query_rule.check_rule_pk_exists(cursor):
                     query_rule.update_rule(module.check_mode, result, cursor)
                 else:
                     query_rule.create_rule(module.check_mode, result, cursor)

@squirrel532 do you have some time to provide a PR?

[squirrel532]

Sure, I'll file a PR later.