Can't use "%" in community.mysql.mysql_query

[MagentoPro]

I have a query that uses the percentage character in a like statment...

community.mysql.mysql_query: login_db: "{{ db_name }}" login_host: "{{ host }}" login_port: "{{ port }}" login_user: "{{ username }}" login_password: "{{ password }}" query: UPDATE user SET user_name = CONCAT(%(user_name)s,%(ticket)s) WHERE user_name = %(user_name)s AND user_name NOT LIKE '%{{ ticket }}%' named_args: user_name: "{{ item.user_name }}" ticket: "{{ ticket }}"

I get the following error

failed: [127.0.0.1] (item={u'user_name': u'john doe'}) => {"ansible_loop_var": "item", "changed": false, "item": {"user_name": "john doe"}, "msg": "Cannot execute SQL 'UPDATE user SET user_name = CONCAT(%(user_name)s,%(ticket)s) WHERE user_name = %(user_name)s AND user_name NOT LIKE '%SMIG-1111'' args [{'ticket': 'SMIG-1111', 'user_name': 'cemery2174'}]: not enough arguments for format string"}

The query works fine if I run it directly in my database. It seems to be a problem with the % character. It is expecting another parameter because I have % in the LIKE statment.

[Andersson007]

@MagentoPro hi, thanks for reporting this!

@koleo would you like to pick it up?

[koleo]

Hi,

Yes, I will look to correct this in the next few days.

[koleo]

@MagentoPro I have reproduced and identified the issue you reported.

IMHO it's not a bug. As soon as you use named_args or positional_args option, a Python string substitution is applied. So any % character will be a candidate for replacement. If you expect the character % to stay as is, you should double it %%.

So your example should look like:

community.mysql.mysql_query:
    query: >-
        UPDATE user 
        SET user_name = CONCAT(%(user_name)s,%(ticket)s)
        WHERE user_name = %(user_name)s
        AND user_name NOT LIKE '%%{{ ticket }}%%'
    named_args:
        user_name: "{{ item.user_name }}"
        ticket: "{{ ticket }}"

Note that you should Not double the percent sign if you do not use named_args/positional_args:

community.mysql.mysql_query:
    query: >-
        UPDATE user 
        SET user_name = 'test1'
        WHERE user_name = 'test2'
        AND user_name NOT LIKE '%{{ ticket }}%'

[MagentoPro]

Thank you. I will give that a try. I appreciate it. Maybe the docs could be updated to state that. No one would ever know to do that otherwise.

[Andersson007]

I won't close the issue because updating the doc sounds sensible. So if anyone wants to do this, welcome. I'll add the doc label.