Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

proxysql_mysql_query_rules becomes incompatible with 1.4.x since 1.1.0 #71

Closed
markuman opened this issue Sep 16, 2021 · 5 comments · Fixed by #77
Closed

proxysql_mysql_query_rules becomes incompatible with 1.4.x since 1.1.0 #71

markuman opened this issue Sep 16, 2021 · 5 comments · Fixed by #77

Comments

@markuman
Copy link
Member

SUMMARY

community.proxysql 1.1.0 introduced cache_empty_result parameter.
However, this column does not exist in proxysql < 2.0.0. Therefore the module will always fail atm.

1.4.15 schema

CREATE TABLE mysql_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    schemaname VARCHAR,
    flagIN INT NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT,
    digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagOUT INT,
    replace_pattern VARCHAR,
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED,
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED,
    next_query_flagIN INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    OK_msg VARCHAR,
    sticky_conn INT CHECK (sticky_conn IN (0,1)),
    multiplex INT CHECK (multiplex IN (0,1,2)),
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    comment VARCHAR)

2.2.0 schema

CREATE TABLE mysql_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    schemaname VARCHAR,
    flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535),
    digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagOUT INT CHECK (flagOUT >= 0),
    replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
    cache_timeout INT CHECK(cache_timeout >= 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED CHECK (timeout >= 0),
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED CHECK (delay >=0),
    next_query_flagIN INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    OK_msg VARCHAR,
    sticky_conn INT CHECK (sticky_conn IN (0,1)),
    multiplex INT CHECK (multiplex IN (0,1,2)),
    gtid_from_hostgroup INT UNSIGNED,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR)
ISSUE TYPE
  • Bug Report
COMPONENT NAME

proxysql_mysql_query_rules

@markuman
Copy link
Member Author

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | create test query rule] *************************************************************************************************
fatal: [proxysql]: FAILED! => {"changed": false, "msg": "unable to modify rule.. (1045, 'no such column: cache_empty_result')"}

@markuman
Copy link
Member Author

The fix is very simple: markuman@ca47c32
But the integration test is burning after that. See: markuman@e8f44fa

For 1.4.15 is it passing now.
But for 2.x.x it will fail with

TASK [test_proxysql_query_rules : debug] *****************************************************************************************************************************************************************************
ok: [proxysql] => {
    "memory_result.stdout": "VARIABLE IS NOT DEFINED!"
}

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | confirm create query rule in check mode didn't make a change in memory] *************************************************
fatal: [proxysql]: FAILED! => {"msg": "The conditional check 'memory_result.stdout|length == 0' failed. The error was: error while evaluating conditional (memory_result.stdout|length == 0): 'dict object' has no attribute 'stdout'"}

because the test for 1.x.x is skipped after 2.x.x: https://github.com/markuman/community.proxysql/blob/fix_query_rules/tests/integration/targets/test_proxysql_query_rules/tasks/base_test.yml#L70
If we exchange the test, 1.x.x will fail while 2.x.x will pass.

Any ideas @Andersson007 ?

@Andersson007
Copy link
Contributor

I have several questions:)

  1. Is this caused by our recent activity or this is something that happened naturally?
  2. Maybe it's worth submitting the PR from your fork?
  3. You test it locally with 1.4.15 and 2+ and the tests fail with 2+ and pass with 1.4.15, right?
  4. Did you use our tests from tests/integration/.. or you tested things manually, i.e. wrote a playbook, ran, go to ProxySQL and see the result there with eyes, not with assert?
  5. What will be the actual result for users after the fix is released: everything will work and it's just a problem in our testing approaches or something will not work for users?
  6. Would be also nice to run the tests with -vvv and see the output here (especially interesting what that check that registers memory_result returns).

I'm trying to fully understand the context

@markuman
Copy link
Member Author

Is this caused by our recent activity or this is something that happened naturally?

beb3841
It happens this year in april and is introduced with community.proxysql 1.1.0.

Maybe it's worth submitting the PR from your fork?

Yes, that's the plan. #73

You test it locally with 1.4.15 and 2+ and the tests fail with 2+ and pass with 1.4.15, right?

Yes. The verify variable is overwritten by skipping. that why it is failing.

Did you use our tests from tests/integration/.. or you tested things manually, i.e. wrote a playbook, ran, go to ProxySQL and see the result there with eyes, not with assert?

No, our integration test

What will be the actual result for users after the fix is released: everything will work and it's just a problem in our testing approaches or something will not work for users?

Just our test is failing. We can reorder the tests, so that < 2 is run first and >= 2 afterwards.
Then our integration test will pass, but testing against 1.4.15 will fail. But that's also a weak solution.

Would be also nice to run the tests with -vvv and see the output here (especially interesting what that check that registers memory_result returns).

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | check if test query rule exists in memory] *****************************************************************************
task path: /home/m/git/ansible_collections/community/proxysql/tests/integration/targets/test_proxysql_query_rules/tasks/base_test.yml:57
redirecting (type: connection) ansible.builtin.docker to community.docker.docker
<proxysql> ESTABLISH DOCKER CONNECTION FOR USER: root
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c 'echo ~ && sleep 0'"]
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', '/bin/sh -c \'( umask 77 && mkdir -p "` echo /root/.ansible/tmp `"&& mkdir "` echo /root/.ansible/tmp/ansible-tmp-1632209224.9696357-124007-246539997313719 `" && echo ansible-tmp-1632209224.9696357-124007-246539997313719="` echo /root/.ansible/tmp/ansible-tmp-1632209224.9696357-124007-246539997313719 `" ) && sleep 0\'']
Using module file /home/m/.local/lib/python3.9/site-packages/ansible/modules/command.py
<proxysql> PUT /home/m/.ansible/tmp/ansible-local-43069lw2_gqtm/tmpe52psax_ TO /root/.ansible/tmp/ansible-tmp-1632209224.9696357-124007-246539997313719/AnsiballZ_command.py
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c 'chmod u+x /root/.ansible/tmp/ansible-tmp-1632209224.9696357-124007-246539997313719/ /root/.ansible/tmp/ansible-tmp-1632209224.9696357-124007-246539997313719/AnsiballZ_command.py && sleep 0'"]
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c '/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1632209224.9696357-124007-246539997313719/AnsiballZ_command.py && sleep 0'"]
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c 'rm -f -r /root/.ansible/tmp/ansible-tmp-1632209224.9696357-124007-246539997313719/ > /dev/null 2>&1 && sleep 0'"]
changed: [proxysql] => {
    "changed": true,
    "cmd": "mysql -uadmin -padmin -h127.0.0.1 -P6032 -BNe\"SELECT username || ',' || match_pattern || ',' || destination_hostgroup || ',' || active || ',' || retries || ',' || cache_ttl || ',' || cache_empty_result || ',' || multiplex FROM mysql_query_rules where username = 'guest_ro' and match_pattern = '^SELECT.*' and destination_hostgroup and '1' and active = '1' and retries = '3' and cache_ttl = '20000' and cache_empty_result = '0' and multiplex = '0'\"",
    "delta": "0:00:00.005045",
    "end": "2021-09-21 07:27:05.547091",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql -uadmin -padmin -h127.0.0.1 -P6032 -BNe\"SELECT username || ',' || match_pattern || ',' || destination_hostgroup || ',' || active || ',' || retries || ',' || cache_ttl || ',' || cache_empty_result || ',' || multiplex FROM mysql_query_rules where username = 'guest_ro' and match_pattern = '^SELECT.*' and destination_hostgroup and '1' and active = '1' and retries = '3' and cache_ttl = '20000' and cache_empty_result = '0' and multiplex = '0'\"",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true,
            "warn": false
        }
    },
    "rc": 0,
    "start": "2021-09-21 07:27:05.542046",
    "stderr": "",
    "stderr_lines": [],
    "stdout": "",
    "stdout_lines": []
}

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | check if test query rule exists on disk] *******************************************************************************
task path: /home/m/git/ansible_collections/community/proxysql/tests/integration/targets/test_proxysql_query_rules/tasks/base_test.yml:61
redirecting (type: connection) ansible.builtin.docker to community.docker.docker
<proxysql> ESTABLISH DOCKER CONNECTION FOR USER: root
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c 'echo ~ && sleep 0'"]
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', '/bin/sh -c \'( umask 77 && mkdir -p "` echo /root/.ansible/tmp `"&& mkdir "` echo /root/.ansible/tmp/ansible-tmp-1632209225.8488975-124245-148086203230372 `" && echo ansible-tmp-1632209225.8488975-124245-148086203230372="` echo /root/.ansible/tmp/ansible-tmp-1632209225.8488975-124245-148086203230372 `" ) && sleep 0\'']
Using module file /home/m/.local/lib/python3.9/site-packages/ansible/modules/command.py
<proxysql> PUT /home/m/.ansible/tmp/ansible-local-43069lw2_gqtm/tmp2shvfivh TO /root/.ansible/tmp/ansible-tmp-1632209225.8488975-124245-148086203230372/AnsiballZ_command.py
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c 'chmod u+x /root/.ansible/tmp/ansible-tmp-1632209225.8488975-124245-148086203230372/ /root/.ansible/tmp/ansible-tmp-1632209225.8488975-124245-148086203230372/AnsiballZ_command.py && sleep 0'"]
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c '/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1632209225.8488975-124245-148086203230372/AnsiballZ_command.py && sleep 0'"]
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c 'rm -f -r /root/.ansible/tmp/ansible-tmp-1632209225.8488975-124245-148086203230372/ > /dev/null 2>&1 && sleep 0'"]
changed: [proxysql] => {
    "changed": true,
    "cmd": "mysql -uadmin -padmin -h127.0.0.1 -P6032 -BNe\"SELECT username || ',' || match_pattern || ',' || destination_hostgroup || ',' || active || ',' || retries || ',' || cache_ttl || ',' || cache_empty_result || ',' || multiplex FROM disk.mysql_query_rules where username = 'guest_ro' and match_pattern = '^SELECT.*' and destination_hostgroup and '1' and active = '1' and retries = '3' and cache_ttl = '20000' and cache_empty_result = '0' and multiplex = '0'\"",
    "delta": "0:00:00.005009",
    "end": "2021-09-21 07:27:06.444526",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql -uadmin -padmin -h127.0.0.1 -P6032 -BNe\"SELECT username || ',' || match_pattern || ',' || destination_hostgroup || ',' || active || ',' || retries || ',' || cache_ttl || ',' || cache_empty_result || ',' || multiplex FROM disk.mysql_query_rules where username = 'guest_ro' and match_pattern = '^SELECT.*' and destination_hostgroup and '1' and active = '1' and retries = '3' and cache_ttl = '20000' and cache_empty_result = '0' and multiplex = '0'\"",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true,
            "warn": false
        }
    },
    "rc": 0,
    "start": "2021-09-21 07:27:06.439517",
    "stderr": "",
    "stderr_lines": [],
    "stdout": "",
    "stdout_lines": []
}

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | check if test query rule exists in runtime] ****************************************************************************
task path: /home/m/git/ansible_collections/community/proxysql/tests/integration/targets/test_proxysql_query_rules/tasks/base_test.yml:65
redirecting (type: connection) ansible.builtin.docker to community.docker.docker
<proxysql> ESTABLISH DOCKER CONNECTION FOR USER: root
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c 'echo ~ && sleep 0'"]
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', '/bin/sh -c \'( umask 77 && mkdir -p "` echo /root/.ansible/tmp `"&& mkdir "` echo /root/.ansible/tmp/ansible-tmp-1632209226.7705739-124484-111504593053916 `" && echo ansible-tmp-1632209226.7705739-124484-111504593053916="` echo /root/.ansible/tmp/ansible-tmp-1632209226.7705739-124484-111504593053916 `" ) && sleep 0\'']
Using module file /home/m/.local/lib/python3.9/site-packages/ansible/modules/command.py
<proxysql> PUT /home/m/.ansible/tmp/ansible-local-43069lw2_gqtm/tmp3gu7e0l6 TO /root/.ansible/tmp/ansible-tmp-1632209226.7705739-124484-111504593053916/AnsiballZ_command.py
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c 'chmod u+x /root/.ansible/tmp/ansible-tmp-1632209226.7705739-124484-111504593053916/ /root/.ansible/tmp/ansible-tmp-1632209226.7705739-124484-111504593053916/AnsiballZ_command.py && sleep 0'"]
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c '/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1632209226.7705739-124484-111504593053916/AnsiballZ_command.py && sleep 0'"]
<proxysql> EXEC ['/usr/bin/docker', b'exec', b'-i', 'proxysql', '/bin/sh', '-c', "/bin/sh -c 'rm -f -r /root/.ansible/tmp/ansible-tmp-1632209226.7705739-124484-111504593053916/ > /dev/null 2>&1 && sleep 0'"]
changed: [proxysql] => {
    "changed": true,
    "cmd": "mysql -uadmin -padmin -h127.0.0.1 -P6032 -BNe\"SELECT username || ',' || match_pattern || ',' || destination_hostgroup || ',' || active || ',' || retries || ',' || cache_ttl || ',' || cache_empty_result || ',' || multiplex FROM runtime_mysql_query_rules where username = 'guest_ro' and match_pattern = '^SELECT.*' and destination_hostgroup and '1' and active = '1' and retries = '3' and cache_ttl = '20000' and cache_empty_result = '0' and multiplex = '0'\"",
    "delta": "0:00:00.005396",
    "end": "2021-09-21 07:27:07.383287",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql -uadmin -padmin -h127.0.0.1 -P6032 -BNe\"SELECT username || ',' || match_pattern || ',' || destination_hostgroup || ',' || active || ',' || retries || ',' || cache_ttl || ',' || cache_empty_result || ',' || multiplex FROM runtime_mysql_query_rules where username = 'guest_ro' and match_pattern = '^SELECT.*' and destination_hostgroup and '1' and active = '1' and retries = '3' and cache_ttl = '20000' and cache_empty_result = '0' and multiplex = '0'\"",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true,
            "warn": false
        }
    },
    "rc": 0,
    "start": "2021-09-21 07:27:07.377891",
    "stderr": "",
    "stderr_lines": [],
    "stdout": "",
    "stdout_lines": []
}

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | check if test query rule exists in memory] *****************************************************************************
task path: /home/m/git/ansible_collections/community/proxysql/tests/integration/targets/test_proxysql_query_rules/tasks/base_test.yml:75
skipping: [proxysql] => {
    "changed": false,
    "skip_reason": "Conditional result was False"
}

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | check if test query rule exists on disk] *******************************************************************************
task path: /home/m/git/ansible_collections/community/proxysql/tests/integration/targets/test_proxysql_query_rules/tasks/base_test.yml:79
skipping: [proxysql] => {
    "changed": false,
    "skip_reason": "Conditional result was False"
}

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | check if test query rule exists in runtime] ****************************************************************************
task path: /home/m/git/ansible_collections/community/proxysql/tests/integration/targets/test_proxysql_query_rules/tasks/base_test.yml:83
skipping: [proxysql] => {
    "changed": false,
    "skip_reason": "Conditional result was False"
}

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | check if create query rule in check mode reported a change] ************************************************************
task path: /home/m/git/ansible_collections/community/proxysql/tests/integration/targets/test_proxysql_query_rules/tasks/test_create_using_check_mode.yml:10
redirecting (type: connection) ansible.builtin.docker to community.docker.docker
ok: [proxysql] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [test_proxysql_query_rules : debug] ****************************************************************************************************************************************************************************
task path: /home/m/git/ansible_collections/community/proxysql/tests/integration/targets/test_proxysql_query_rules/tasks/test_create_using_check_mode.yml:14
redirecting (type: connection) ansible.builtin.docker to community.docker.docker
ok: [proxysql] => {
    "memory_result.stdout": "VARIABLE IS NOT DEFINED!: 'dict object' has no attribute 'stdout'"
}

TASK [test_proxysql_query_rules : test_proxysql_query_rules | test_create_using_check_mode | confirm create query rule in check mode didn't make a change in memory] ************************************************
task path: /home/m/git/ansible_collections/community/proxysql/tests/integration/targets/test_proxysql_query_rules/tasks/test_create_using_check_mode.yml:16
redirecting (type: connection) ansible.builtin.docker to community.docker.docker
fatal: [proxysql]: FAILED! => {
    "msg": "The conditional check 'memory_result.stdout|length == 0' failed. The error was: error while evaluating conditional (memory_result.stdout|length == 0): 'dict object' has no attribute 'stdout'"
}

PLAY RECAP **********************************************************************************************************************************************************************************************************
localhost                  : ok=3    changed=2    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   
proxysql                   : ok=566  changed=275  unreachable=0    failed=1    skipped=124  rescued=0    ignored=0  

As you see memory_result.stdout|length == 0 is set correctly, but it is overwritten by the skip block https://github.com/ansible-collections/community.proxysql/pull/73/files#diff-88a558252bfc136c78a97618fcd7fb6abb944f0e7f4b588300c0f22bd8749c36R70

Another possibility is to use different variables while testing against 2.x.x and 1.x.x

@Andersson007
Copy link
Contributor

@markuman that's cool that the problem is in the tests itself. If the different vars solve the issue, this solution sounds good to me

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants