ERROR 1064 (42000) SQL Syntax error while running ansible playbook

Question

I am trying to load files into MySQL RDS using ansible-playbook

 tasks:

    - name: loadBase
      block:
        - name: copy loadBase files to target
          copy: src=../../myApp/{{ item }}
                dest=/tmp/{{ item | basename }}
                owner=tomcat group=tomcat mode=0600
          with_items: "{{ data.loadBase }}" 
        - name: load loadBase files into target mysql
          shell: mysql -h {{ db_host }} -u root -p{{ db_root_password }} {{ db_name }} < /tmp/{{ item | basename }}
          with_items: "{{ data.loadBase }}"
          no_log: true
        - name: cleanup loadBase files on target
          file: path=/tmp/{{ item | basename }}
                state=absent
          with_items: "{{ data.loadBase }}"
      when: loadBase

I am using the following config file which is used by the ansible playbook Config file:

# data files, relative to the source code root, to be
#   conditionally run during a deployment
data:
  loadBase:
    - data/src/main/sql/structure.sql
    - data/src/main/sql/data.sql
    - data/src/main/sql/update.sql

I see that the .sql files are being added to the /tmp folder in the app server from the ansible server. But I see that the data is not loading to MySQL RDS and I see the following error

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-\nansible_ssh_common_args: '-o ProxyCommand=\"ssh -W %h:%p -q root@bastion-myapp' at line 1",

I am using MySQL RDS Engine version 8.0.11.

Its trying to connect to the bastion host again and its failing?

ansible_ssh_common_args: '-o ProxyCommand="ssh -W %h:%p -q bastion-app.org"'

Any help, what's wrong here?

Looks like you try to execute shell command on MySQL server as SQL query... — Akina, Dec 29 '21 at 19:27
@Akina any idea, why is it trying try to execute shell command on MySQL server? — merilstack, Dec 29 '21 at 21:41
This looks like a strange side effect of a typof in the file where you define `ansible_ssh_common_args`. Moreover, there is a dedicated module to run MySQL queries (see answer from Patrick) to prefer over shell. — Zeitounator, Dec 30 '21 at 10:02

score 1 · Accepted Answer · answered Dec 30 '21 at 08:20

Why not using the Ansible MySQL module?
See Insert data into mysql tables using ansible and https://docs.ansible.com/ansible/2.5/modules/mysql_db_module.html:

# Copy database dump file to remote host and restore it to database 'my_db'
- name: Copy database dump file
  copy:
    src: dump.sql.bz2
    dest: /tmp

- name: Restore database
  community.mysql.mysql_db:
    name: my_db
    state: import
    target: /tmp/dump.sql.bz2

ERROR 1064 (42000) SQL Syntax error while running ansible playbook

1 Answers1