Cannot execute SQL from mysql.mysql_query

Issue with executing query-file through mysql.mysql_query
I am new within ansible and it modules, so sorry if I’m just using the wrong module.

I need to run query (~7000 lines) to set up a database on my database server. I am using the mysql.mysql_query module because it seems to be the correct module for the task (but might me wrong and getting fatal: [db-server]: FAILED! => {"changed": false, "msg": "Cannot execute SQL '(...sql-file...)'.

The db-server is running MariaDB and running ansible through AAP, and it works manually doing source /var/tmp/redcap_install.sql, am I doing it correct or should I use another module?

- name: Move script to db-server
  copy:
    src: redcap_install.sql
    dest: /var/tmp

- name: Create REDCap database with config
  community.mysql.mysql_query:
    login_db: '{{ db_name }}'
    login_user: '{{ db_username }}'
    login_password: '{{ database_password }}'
    query: "/var/tmp/redcap_install.sql"
1 Like

You’re using a file name in the query text. In order to accomplish what you are trying to do, try this instead:

    - name: Execute SQL query from file
      community.mysql.mysql_db:
        login_user: your_db_user
        login_password: your_db_password
        login_host: localhost # Or the database server's IP if different
        name: your_database_name
        state: import
        target: /tmp/query.sql

Why? Becasue community.mysql.mysql_db has the parameter STATE which offers the value “import” value.

Source: Ansible Galaxy - community.mysql

2 Likes

Thanks @Narizz28 it works, I though mysql_query also could take a file :sweat_smile:

1 Like

Glad I could help @engstroem

1 Like