# Execute an .sql script on mysql with secure installation

**URL:** <https://forum.ansible.com/t/execute-an-sql-script-on-mysql-with-secure-installation/36136>\
**Category:** Ansible Project\
**Created:** [December 20, 2021, 5:08pm UTC](https://forum.ansible.com/t/execute-an-sql-script-on-mysql-with-secure-installation/36136 "2021-12-20T17:08:23Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mythicale](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@Mythicale](https://forum.ansible.com/u/Mythicale)\
**Post date:** [December 20, 2021, 5:08pm UTC](https://forum.ansible.com/t/execute-an-sql-script-on-mysql-with-secure-installation/36136/1 "2021-12-20T17:08:23Z")

</div>

Hi,  
I am trying to use an sql script to create a database in mariadb on a openbsd server with ansible.  
My playbook first create the script with the name of the database to create and then uses expect to give the command “mysql -u root -p \< script.sql” and then responses the password with “” because theres no password.  
I try to use the script created by ansible and it works.  
I try to use the same command that ansible is using and it works.  
I took the “\< script.sql” part to see if the problem was the password, and ansible enter in mysql without returning any error.

When i use the playbook with “\< script.sql” in command, ansible returns the error of non-zero return code returning rc=1.

Anyone know how to solve it??

I’ve tried also the community.mysql module, but it didnt worked also to create the first database.

Best Regards,

Tiago Oliveira

---

<div class="post-metadata">

**Author:** ![racke](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/racke/32/4767_2.png) [@racke](https://forum.ansible.com/u/racke)\
**Post date:** [December 20, 2021, 5:14pm UTC](https://forum.ansible.com/t/execute-an-sql-script-on-mysql-with-secure-installation/36136/2 "2021-12-20T17:14:15Z")

</div>

> Hi,  
> I am trying to use an sql script to create a database in mariadb on a openbsd server with ansible.  
> My playbook first create the script with the name of the database to create and then uses expect to give the command "mysql -u root -p \< script.sql" and then responses the password with "" because theres no password.  
> I try to use the script created by ansible and it works.  
> I try to use the same command that ansible is using and it works.  
> I took the "\< script.sql" part to see if the problem was the password, and ansible enter in mysql without returning any error.
> 
> When i use the playbook with "\< script.sql" in command, ansible returns the error of non-zero return code returning rc=1.
> 
> Anyone know how to solve it??

Maybe you start with sharing your playbook and the contents of the script!?

The modules mysql\_user and mysql\_db should be able to create your database with the associated users.

Regards  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Racke

---

<div class="post-metadata">

**Author:** ![Mythicale](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@Mythicale](https://forum.ansible.com/u/Mythicale)\
**Post date:** [December 20, 2021, 5:28pm UTC](https://forum.ansible.com/t/execute-an-sql-script-on-mysql-with-secure-installation/36136/3 "2021-12-20T17:28:49Z")

</div>

Yeah, sorry.

- name: Create a new .sql file for the table ‘{{table\_name}}’  
template:  
src: templates/new\_table\_template  
dest: /home/secnet/{{table\_name}}.sql  
expect:  
command: mysql -u root -p \< {{table\_name}}.sql  
responses:  
‘Enter password:’: ‘’  
register: wp  
failed\_when: wp.rc not in [0, 1]

The mysql\_db module cant enter in mysql, returns the error “msg”: “unable to connect to database, check login\_user and login\_password are correct or /root/.my.cnf has  
the credentials. Exception message: (1698, "Access denied for user ‘root’@‘localhost’")”  
}

since i need to enter with root user.

Regards,

Tiago

A segunda-feira, 20 de dezembro de 2021 à(s) 17:14:36 UTC, [ra...@linuxia.de](mailto:ra...@linuxia.de) escreveu:

---

<div class="post-metadata">

**Author:** ![Mythicale](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@Mythicale](https://forum.ansible.com/u/Mythicale)\
**Post date:** [December 20, 2021, 5:42pm UTC](https://forum.ansible.com/t/execute-an-sql-script-on-mysql-with-secure-installation/36136/4 "2021-12-20T17:42:18Z")

</div>

oops, wrong code.

- name: Create a new .sql file for the table ‘{{table\_name}}’  
template:  
src: templates/new\_table\_template  
dest: /home/secnet/{{table\_name}}.sql

- name: Create the new database with name ‘{{table\_name}}’  
expect:  
command: mysql -u root -p \< {{table\_name}}.sql  
responses:  
‘Enter password:’: ‘’  
register: wp  
failed\_when: wp.rc not in [0, 1]

This one is the right one

A segunda-feira, 20 de dezembro de 2021 à(s) 17:28:50 UTC, Tiago Oliveira escreveu:

---

<div class="post-metadata">

**Author:** ![racke](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/racke/32/4767_2.png) [@racke](https://forum.ansible.com/u/racke)\
**Post date:** [December 20, 2021, 6:34pm UTC](https://forum.ansible.com/t/execute-an-sql-script-on-mysql-with-secure-installation/36136/5 "2021-12-20T18:34:38Z")

</div>

> oops, wrong code.
> 
> - name: Create a new .sql file for the table '{{table\_name}}'  
> template:  
> src: templates/new\_table\_template  
> dest: /home/secnet/{{table\_name}}.sql
> 
> - name: Create the new database with name '{{table\_name}}'  
> expect:  
> command: mysql -u root -p \< {{table\_name}}.sql  
> responses:  
> 'Enter password:': ''  
> register: wp  
> failed\_when: wp.rc not in [0, 1]
> 
> This one is the right one

From expect module documentation:

-- snip --  
If you want to run a command through the shell (say you are using \<, \>, |, and so on), you must specify a shell in the command such as /bin/bash -c "/path/to/something | grep else".  
-- snap --

Don't understand why you try to feed an empty password to MySQL. Do you use become: yes in your playbook? This would execute the task as root user.

Regards  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Racke

---

<div class="post-metadata">

**Author:** ![Antony\_Stone](https://avatars.discourse-cdn.com/v4/letter/a/4491bb/32.png) [@Antony\_Stone](https://forum.ansible.com/u/Antony_Stone)\
**Post date:** [December 20, 2021, 7:17pm UTC](https://forum.ansible.com/t/execute-an-sql-script-on-mysql-with-secure-installation/36136/6 "2021-12-20T19:17:29Z")

</div>

So, why don't you simply omit the "-p" to avoid being prompted for the non-  
existent password?

Antony.