Search code examples
mysqljsonansibleescapingdouble-quotes

Ansible double quotes and variables

I'm trying implement a solution I've found on stackoverflow for truncating all tables in a database.

It works when I run it on terminal command line but unfortunately it does not work on Ansible.

Problematic part is this -e \"truncate table $table\" How should I handle a variable in double quote ? I couldn't found any solution so far.

- set_fact: db_truncate_command="mysql -v -h {{ wp_db_host }} -u {{ wp_db_user }} -p{{ wp_db_password }} -Nse 'show tables' {{ wp_db_name }} | while read table; do mysql -v -h {{ wp_db_host }} -u {{ wp_db_user }} -p{{ wp_db_password }}  -e \"truncate table $table\" {{ wp_db_name }}; done"
  when: stat_db_file.stat.exists

- debug:
    msg: "command {{ db_truncate_command }} "

- name: Truncate existing tables in db
  command: >
    {{ db_truncate_command }}
  when: stat_db_file.stat.exists

The output I get:

TASK [mysql : set_fact] ***********************************************************************************************************************************************************
ok: [ansible.mydomain.com] => {"ansible_facts": {"db_truncate_command": "mysql -v -h 192.155.190.255 -u wp_user -pMyPassword -Nse 'show tables' ansible | while read table; do mysql -v -h 192.155.190.255 -u wp_user -pMyPassword  -e \"truncate table $table\" ansible; done"}, "changed": false}

TASK [mysql : debug] **************************************************************************************************************************************************************
ok: [ansible.mydomain.com] => {
    "msg": "command mysql -v -h 192.155.190.255 -u wp_user -pMyPassword -Nse 'show tables' ansible | while read table; do mysql -v -h 192.155.190.255 -u wp_user -pMyPassword  -e \"truncate table $table\" ansible; done "
}

TASK [mysql : Truncate existing tables in db] *************************************************************************************************************************************
fatal: [ansible.mydomain.com]: FAILED! => {"changed": true, "cmd": ["mysql", "-v", "-h", "192.155.190.255", "-u", "wp_user", "-pMyPassword", "-Nse", "show tables", "ansible", "|", "while", "read", "table;", "do", "mysql", "-v", "-h", "192.155.190.255", "-u", "wp_user", "-pMyPassword", "-e", "truncate table $table", "ansible;", "done"], "delta": "0:00:00.005262", "end": "2018-02-24 10:33:22.808490", "msg": "non-zero return code", "rc": 1, "start": "2018-02-24 10:33:22.803228", "stderr": "mysql: [Warning] Using a password on the command line interface can be insecure.", "stderr_lines": ["mysql: [Warning] Using a password on the command line interface can be insecure."], "stdout": "mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper\n

Added output of fail message

ansible output of fail message

Solution

  • Using shell instead of command works.

    - name: Truncate existing tables in db
  shell: >
    {{ db_truncate_command }}
  when: stat_db_file.stat.exists