mysql_query an unable to correctly format UPDATE query with multiple key values

[aworldofcode]

SUMMARY

Correct usage for updating columns of a mariadb row when using mysql_query

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.mysql.mysql_query

ANSIBLE VERSION

2.14

COLLECTION VERSION

CONFIGURATION

OS / ENVIRONMENT

STEPS TO REPRODUCE

community.mysql.mysql_query

...


{
    "diff_records": {
        "app_portfolio_manager": "New Manager"
    }
}


- name: case 1b insert app_dict to db table app_info
  community.mysql.mysql_query:
      - UPDATE app_info
        SET ( {{ diff_records.keys() | join(', ') }} ) VALUES ( {{ diff_records.values() | map('regex_replace', '^(.*)$', "'\1'") | join(', ') }} )
        WHERE app_name = '{{ app_dict.app_name }}' 

EXPECTED RESULTS

Expecting to be able to insert keys and values for the update query

ACTUAL RESULTS

fatal: [localhost]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "query": [
                "UPDATE app_info SET ( app_portfolio_manager ) VALUES ( 'New Manager' ) WHERE app_name = 'My new App'"
            ],
            "single_transaction": true
        }
    },
    "msg": "Cannot execute SQL 'UPDATE app_info SET ( app_portfolio_manager ) VALUES ( 'New Manager' ) WHERE app_name = 'My new App'' args [None]: (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( app_portfolio_manager ) VALUES ( 'New Manager' ) WHERE app_name = 'My new...' at line 1\")"
}

[aworldofcode]

I also tested with following code but does not work with multiple values

- name: case 1b insert app_dict to db table app_info community.mysql.mysql_query: - UPDATE app_info SET {{ diff_records.keys() | join(', ') }} = {{ diff_records.values() | map('regex_replace', '^(.*)$', "'\1'") | join(', ') }} WHERE app_name = '{{ app_dict.app_name }}'

[aworldofcode]

update .
Adding a simple dict loop seems to have done the trick.

- name: case 1b insert app_dict to db table app_info community.mysql.mysql_query: - UPDATE app_info SET {{ item.key }} '{{ item.value }}' WHERE app_name = '{{ app_dict.app_name }}' loop: "{{ diff_records | dict2items }}"

[Andersson007]

@aworldofcode hello, thanks for reporting the issue!
I think it can be an issue with templating, could you please run the the task with the -vvv key and post what it'll return?

[aworldofcode]

@Andersson007
The message above is with -vvv
fatal: [localhost]: FAILED! => { "changed": false, "invocation": { "module_args": { "query": [ "UPDATE app_info SET ( app_portfolio_manager ) VALUES ( 'New Manager' ) WHERE app_name = 'My new App'" ], "single_transaction": true } }, "msg": "Cannot execute SQL 'UPDATE app_info SET ( app_portfolio_manager ) VALUES ( 'New Manager' ) WHERE app_name = 'My new App'' args [None]: (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( app_portfolio_manager ) VALUES ( 'New Manager' ) WHERE app_name = 'My new...' at line 1\")" }

[aworldofcode]

@Andersson007 So far it seems that the correct way to move forward is looping with items not sure how else to code it.

- name: case 1b insert app_dict to db table app_info  
  community.mysql.mysql_query:  
    - UPDATE app_info SET  {{ item.key }} '{{ item.value }}'   
       WHERE app_name = '{{ app_dict.app_name }}' 
  loop: "{{ diff_records | dict2items }}"

[Andersson007]

@aworldofcode ah, ok, yes, it should be in the form of

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

according to the docs

If the issue has a solution, could it be closed?

[aworldofcode]

@Andersson007 That statement is correct but it works well only for static key, values. The code I posted is for passing into the UPDATE statement dynamic values.
The issue can be closed.
Maybe in the docs we want to add this as an example of adding dynamic key, values ?

[Andersson007]

@Andersson007 That statement is correct but it works well only for static key, values. The code I posted is for passing into the UPDATE statement dynamic values. The issue can be closed. Maybe in the docs we want to add this as an example of adding dynamic key, values ?

@aworldofcode sorry for the delayed reply, just back from Fosdem
thanks for the info! I'm not a user, so didn't know
yeah, improving the docs is always a good idea!
Would you like to submit a quick PR? You can edit the file directly in the web ui or, if you'd like to set up a full development environment for further contributions (which is much appreciated!), the quick start dev guide will help.
What do you think?